Repeat action for different worksheets

user_17347580 0
Viewed 78

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
repeat vba excel loops
2 Answers

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
user_116830

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
user_173475800
Related