CODEWITHSUNDEEP
vbaexcel
Waryaam Khan
Waryaam Khan

Reputation: 11

Loop values in one sheet and apply macros on other sheet with range next to the loop range

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.

Upvotes: 1

Views: 67

Answers (1)

Vityata
Vityata

Reputation: 43575

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

Upvotes: 2

Related Questions