How can I recode this so it can loop through specific worksheets in a workbook

Question

I have a macro that I need to loop through specific worksheets, but I built the code through many examples I found online. So I am not quite sure where or how to set the loop and I'm also certain I would have to change the way the whole code is set up. I really have no coding knowledge at all. Meep.

Sub datatransfer()


Application.ScreenUpdating = False
Dim copySheet As Worksheet 

Dim pasteSheet As Worksheet

Set copySheet = Worksheets(ActiveSheet.Name)

Set pasteSheet = Worksheets("CMICIMPORT")

copySheet.Range("A100:AA124").Copy

pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Application.ScreenUpdating = True

MsgBox "Data was copied over to the CMICIMPORT tab successfully", vbInformation, ActiveSheet.Name

Range("M4").Select

    With Selection.Font

        .Color = -11489280

        .TintAndShade = 0

    End With

End Sub

I have to run the code above on each sheet that I am currently on as opposed to being able to run the Macro and it runs on all of my payroll tabs. Also my tabs are named payroll (1), payroll (2) and so forth through payroll (200) if this makes it easier to help me.

Assuming the copy range is consistent, you should be able to change the local sheet `copySheet` into a parameter of the sub. Then you can create another sub which cycles through the sheets and passes each one into `datatransfer` (Although in one of the subs, you'll have to check that the sheet you're copying from isn't "CMICIMPORT") — Mistella, Apr 05 '19 at 19:42
"CMICIMPORT" is where everything is being copied over to. That tab is basically copying everything from the "activesheet" and pasting it over into the next available blank row. So I'm hoping to automate the copying from every payroll tab payroll (1), payroll (2), payroll (3)... through payroll (200) and paste it all on the "CMICIMPORT" tab. — Sergio Maldonado, Apr 05 '19 at 19:51

score 0 · Answer 1 · answered Apr 05 '19 at 19:49

This is a quick and dirty solution, but still would work.

Start with declaring the sheets which should be looped in an Array() called specificWorksheets. If they are indeed 200, then it is a better idea to create some kind of a loop or to read them from a settings worksheets. Anyway, this is the working part:

Sub TestMe()

    Dim specificWorksheets As Variant
    specificWorksheets = Array("payroll (3)", "payroll (1)", "payroll (2)")

    Dim wks As Worksheet

    For Each wks In ThisWorkbook.Worksheets
        If valueInArray(wks.Name, specificWorksheets) Then
            wks.Activate
            'Do your stuff, writing  before...
        End If
    Next

End Sub


Public Function valueInArray(myValue As Variant, myArray As Variant) As Boolean

    Dim cnt As Long

    For cnt = LBound(myArray) To UBound(myArray)
        If LCase(CStr(myValue)) = LCase(CStr(myArray(cnt))) Then
            valueInArray = True
            Exit Function
        End If
    Next cnt

End Function

At the place of the comment 'Do your stuff , copy and paste your code.

Why is this Quick and dirty? Mainly because of the using Activate and thus referring to the active worksheet. Once you realize how the for-each loop works, it is a better idea to read this - How to avoid using Select in Excel VBA - and to rewrite your code. There is a reason, why this is the second most popular topic in [vba] in StackOverflow.

score 0 · Answer 2 · answered Apr 06 '19 at 00:23

Sub DataTransfer()
    Dim sht As Worksheet

    Application.ScreenUpdating = False
    For each sht in ThisWorkbook.Worksheets
        If Left(sht.Name, 7) = "payroll" Then DoIt sht
    Next
    Application.ScreenUpdating = True
End Sub


Sub DoIt(copySheet As Worksheet)
    copySheet.Range("A100:AA124").Copy

    Worksheets("CMICIMPORT").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    Application.CutCopyMode = False

    MsgBox "Data was copied over to the CMICIMPORT tab successfully", vbInformation, copySheet.Name

    With copySheet.Range("M4").Font
        .Color = -11489280
        .TintAndShade = 0
    End With

End Sub

How can I recode this so it can loop through specific worksheets in a workbook

2 Answers2