Repeat action for different worksheets

Question

Newbie here! I have an action which I'd like to repeat, for specific workbooks or specific worksheets.

Is there a way to do it without copy and pasting the whole code for the 2nd, 3rd etc worksheets? Only the workbook and the worksheet names change. other actions (e.g. copy paste) remains the same.

Although there's a "For Each loop", but I don't know how to do it in a way that allows me to specify which worksheets exactly.

For example, I'm

Step 1: copying data from workbook "Red" sheet "Apple". paste into output workbook.
Repeat action. Step 2: copying data from workbook "Yellow" sheet "Banana". paste into same output workbook.

Here's my code if anyone could kindly advise. VBA newbie here thank you!

Sub CopyPastefromOtherWB()

Range("B13").Select

    'Activate WB1
            Workbooks.Open Filename:= "C:\Users\Desktop\My macro projects\Red"

            Worksheets("Apple").Activate

            Range("A1").Select

            Do While Selection.Value <> "Mar"
            ActiveCell.Offset(0, 1).Select

            Loop

            ActiveCell.Offset(1, 0).Select
            Range(ActiveCell, ActiveCell.End(xlDown)).Select
            Selection.Copy

    'Activate output notebook
            Workbooks.Open Filename:= "C:\Users\Desktop\My macro projects\OutputWB"
            Worksheets("Sheet1").Activate
            Range("B13").PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'HERE IS WHERE THE REPEAT HAPPENS. Activate WB2
            Workbooks.Open Filename:= "C:\Users\Desktop\My macro projects\Yellow"

            Worksheets("Banana").Activate

            Range("A1").Select

            Do While Selection.Value <> "Mar"
            ActiveCell.Offset(0, 1).Select

            Loop

            ActiveCell.Offset(1, 0).Select
            Range(ActiveCell, ActiveCell.End(xlDown)).Select
            Selection.Copy

    'Activate output notebook
            Workbooks.Open Filename:= "C:\Users\Desktop\My macro projects\OutputWB"
            Worksheets("Sheet1").Activate
            Range("C13").PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    ActiveCell.Offset(0, 1).Select
            
End Sub

score 1 · Accepted Answer · answered Nov 07 '21 at 07:31

Please see How to avoid using Select in Excel VBA.

Sub CopyPastefromOtherWB(ByVal FromPath As String, ByVal FromSheetName As String, ByVal TargetCell As Range)
    With Workbooks.Open(FromPath)
        With .Worksheets(FromSheetName)
            Dim c As Range
            Set c = .Rows(1).Find("Mar", LookAt:=xlWhole).Offset(1, 0)
          
            TargetCell.Resize(c.Rows.Count, 1).Value = .Range(c, c.End(xlDown)).Value
        End With
    
        .Close False
    End With
End Sub

With Workbooks.Open("C:\Users\Desktop\My macro projects\OutputWB").Worksheets("Sheet1")
    CopyPastefromOtherWB "C:\Users\Desktop\My macro projects\Red", "Apple", .Range("B13")
    CopyPastefromOtherWB "C:\Users\Desktop\My macro projects\Yellow", "Banana", .Range("C13")
End With

This is really awesome, thank you for this!! From your response, I'm able to learn so many new things that I didn't before. Thanks for linking me to that article too, it's something new and amazing to me!! — rainbowtox, Nov 07 '21 at 15:28

score 1 · Answer 2 · edited Dec 22 '21 at 09:40

After months of learning, I developed a solution, feel free to use the code below and tweak it to your needs. This solution is for a set area of cells.

Sub copypaste_adhoc()

    Dim inputfile As Workbook
    
    Set inputfile = Workbooks.Open("c:\path\workbook")

    Dim arrSht, i
    arrSht = Array("worksheet1", "worksheet2")
                
                        
        For i = LBound(arrSht) To UBound(arrSht)
        
            With Worksheets(arrSht(i))
                                
            .Range("A31:Z31").Copy
            
            ThisWorkbook.Sheets("Sheet1").Cells(Sheet5.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
            
                                    
            End With
            Next i

                           
            Application.CutCopyMode = False
        
    
    Sheet5.Range("a1").CurrentRegion.EntireColumn.AutoFit
    
End Sub

`Dim arrSht, i` will declare `i` as `Variant` too which is not suitable since it's meant to be used a counter in your `For` loop. Suggest to split them and declare `Dim i as Long` by a new line itself. Your worksheet reference also can be confusing (is `Sheet5` the same as `ThisWorkbook.Sheets("Sheet1")`?), suggest to standardize it, either use its codename or set a `Worksheet` variable to it. — Raymond Wu, Dec 22 '21 at 09:43

Repeat action for different worksheets

2 Answers2