1

I have two main sheets in excel with other multiple sheets.. -> "Control sheet" and "Target sheet" for performing Loop

Control Sheet is with a Range (in column) on which to apply loop and next to the range are cell refernces to be used for "Target sheet".

The Target sheet has a Table named "Hyp_table" and shall be pasted in same "Target sheet" in a number of places, the refernce of which shall be read from the column next to the Range on which loop is to be perfomred on control sheet.

The objective is as follows For each value in Range in control sheet, if the value in range is 1, then move to the target sheet, copy the "Hyp_table", and paste it on the cell number that is existent in column next to the Range on which loop is perfomred.

I have used the following code but it is not working:

Sub Testing()
    Dim rng As Range, cell As Range    
    Set rng = Sheets("Control").Range("C5:C10")

    For Each cell In rng    
        If cell = "1" Then
            'Moving to my target sheet
            Sheets(Sheets.Count).Select
            ActiveSheet.Previous.Select
            'Copying my table
            Application.Goto "Hyp_Table"
            Selection.Copy

            'Selecting my cell on "Target sheet" based on value in "Control sheet"
            ActiveSheet.Range(Sheets("Control").cell.Offset(, 1)).Select

            'Pasting the table where the cell is selected as above
            ActiveSheet.Paste
            Application.CutCopyMode = False
        Else
        End If
    Next cell
End Sub

Can anyone help me with the code.

Vityata
  • 42,633
  • 8
  • 55
  • 100
Waryaam Khan
  • 11
  • 1
  • but its not working – Waryaam Khan Nov 17 '17 at 10:30
  • Please provide more detail than "it's not working". What isn't working? Is there an error, and if so, what line(s) in the code get highlighted and what is the error message? What result do you get? What result did you expect? – Vegard Nov 17 '17 at 10:31
  • Sorry for being too limited. This line gets highlighted "ActiveSheet.Range(Sheets("Control").cell.Offset(, 1)).Select" – Waryaam Khan Nov 17 '17 at 10:45
  • @WaryaamKhan - it gets highlighted, because you have to refer to a cell. If you know to which cell you need to refer, try like this -> `ActiveSheet.Range(Sheets("Control").cells(1,1).Offset(, 1)).Select` for `A1`. – Vityata Nov 17 '17 at 10:48
  • It still gets highlighted. My objective is... copy the table on a cell the refrence of which is next to the range on which i apply loop. Loop range ("C5:C10") Cell refernce to copy table on target sheet ("D5:D10") – Waryaam Khan Nov 17 '17 at 10:54
  • Now its working fine but if somehow I can get cells(1,1) dynamic to check next cell on each loop. – Waryaam Khan Nov 17 '17 at 11:04

1 Answers1

2

First thing first - avoid using ActiveSheet, Application.GoTo and Selection. In every case. How to avoid using Select in Excel VBA

Second thing second - avoid declaring variables with names like Cell and Range, because these names are special and they are used by the VBEditor.

Third thing third - try the code below - it will probably not work, because I did not understand what exactly is the offset, but it declares a sheetName and it uses it later. It is a good practice:

Option Explicit

Sub Testing()

    Dim rng         As Range
    Dim myCell      As Range
    Dim sheetName   As String

    Set rng = Sheets("Control").Range("C5:C10")

    For Each myCell In rng

        If myCell = 1 Then

            Worksheets(Worksheets.Count - 1).Range("Hyp_Table").Copy
            sheetName = Worksheets("Control").Cells(1, 1)
            Worksheets(sheetName).Paste
            Application.CutCopyMode = False

        End If

    Next myCell

End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • offset is used for the selection of any cell around my selected cell. i.e. offset(1,1) means select cell 1 step to the right side and 1 step down to the selected cell. I studied the code and apllied it but it would not work since it is not selecting my target sheet. – Waryaam Khan Nov 17 '17 at 10:42
  • @WaryaamKhan - I have a clue what is `offset`, but I do not know what exactly do you think it is - e.g. `ActiveSheet.Range(Sheets("Control").cell.Offset(, 1)).Select` is a bit strange, as far as `Offset` should refer to a cell and you are not refering to any. – Vityata Nov 17 '17 at 10:47
  • Your comment worked but if only I could make it dynamic "ActiveSheet.Range(Sheets("Control").Cells(5, 3).Offset(, 1)).Select" I need to make 5 in Cell("5,3") dynamic, something like "i+1" .... – Waryaam Khan Nov 17 '17 at 11:06
  • @WaryaamKhan - then make it `i+1`. Instead of `5,3` write `Cells(i+1,3)`. – Vityata Nov 17 '17 at 11:13