How can I loop my code to more than one spreadsheet in VBA macro?

Question

I've made a code in Macros that only works for one particular spreadsheet. I'm not sure how to loop it or set it to where is does the same thing to the other 20 or so sheets(all in the same workbook) that I have. Also if it matters the code is only for a particular column as well, e.g. columns O and P(it's the same for all 20 sheets).

TestCase #1
Range("S2").Value = "1. " & Range("J2").Value & vbNewLine & vbNewLine & "2. " & Range("J3").Value & vbNewLine & vbNewLine & "3. " & Range("J4").Value & vbNewLine & vbNewLine & "4. " & Range("J5")
Range("T2").Value = "1. " & Range("K2").Value & vbNewLine & vbNewLine & "2. " & Range("K3").Value & vbNewLine & vbNewLine & "3. " & Range("K4").Value & vbNewLine & vbNewLine & "4. " & Range("K5")

So for the code above it varies between different sheets(this is the code that i need to loop for all 20 sheets) TestCase1 or Sheet1 has 5 rows, but the others varies to maybe up the the hundreds. I would like to know how can i integrate a count and loop for it.

' TestCase #1
Sheets(2).Select
Range("S2").Copy
Sheets(1).Select
Range("O2").Select
ActiveSheet.Paste
Sheets(2).Select
Range("T2").Copy
Sheets(1).Select
Range("P2").Select
ActiveSheet.Paste

For the second code I would like to loop it so it just copies the previous information that has been concatenated on to my other worksheet. Thanks in advance anyone :)

If you'd like more specific help, please share and post the code you're working with. In order to wrap your code in a loop that works through all the worksheets in a workbook, look [here](http://stackoverflow.com/a/25953980/4717755) and [here](http://www.vbaexpress.com/kb/getarticle.php?kb_id=390) — PeterT, Jun 14 '16 at 14:49
Please be more specific about exactly what you're using - you're not working in google spreadsheet with VBA... — Tim Williams, Jun 14 '16 at 16:40
updated.. thanks for the feed back. It's my first time using this. not sure how i was supposed to structure the description and etc. — ozone1229, Jun 14 '16 at 17:32

score 0 · Answer 1 · answered Jun 15 '16 at 21:50

Try it this way.

    Sub WorksheetLoop()

                 Dim WS_Count As Integer
                 Dim I As Integer

                 ' Set WS_Count equal to the number of worksheets in the active
                 ' workbook.
                 WS_Count = ActiveWorkbook.Worksheets.Count

                 ' Begin the loop.
                 For I = 1 To WS_Count

                    ' Insert your code here.
                    ' The following line shows how to reference a sheet within
                    ' the loop by displaying the worksheet name in a dialog box.
                    MsgBox ActiveWorkbook.Worksheets(I).Name

                 Next I

    End Sub

How can I loop my code to more than one spreadsheet in VBA macro?

1 Answers1