need help compiling data via a macro

[forgottenitall]

Hi All,
This may seem ambititious but I have done it before (about 13 years ago) so I'm sure the functions are still there. I have multiple sheets in a workbook with the same name and an ascending number (e.g., "data 1" , "data 2", etc..) and I want to compile the information from specifics rows into one continuous list of information on another sheet. I vaguely recall writing a code that would use a counter to change a variable and then input htat variable in the sheet name so it could be activated. And then data could be chosen by cells and appended into a separate sheet.

Any guidance on any part of this would be greatly appreciated!

Thanks!

 

[craig.penny]

See if this helps

Sub s()
Dim sName As String: sName = "Data"
Dim shName As String
Dim i As Integer
On Error Resume Next
For i = 1 To ActiveWorkbook.Sheets.Count
      shName = sName & CStr(i)
      If Not Sheets(shName) Is Nothing Then
            Sheets(shName).Select
      End If
Next i
On Error GoTo 0
End Sub

 

[forgottenitall]

Thanks for your help, craig.penny
I'm getting an error when it compiles at this line:
Dim sName As String: sName = "Data"
saying that's its a "Invalid Outside Procedure"

Also, what is the syntax for picking a row and column based on a counter number, too?

thanks again for helping me!

 

[craig.penny]

I don't know if it'll make a difference but perhaps running it this way will work

Sub s()
Dim sName As String
sName = "Data"
Dim shName As String
Dim i As Integer
On Error Resume Next
For i = 1 To ActiveWorkbook.Sheets.Count
      shName = sName & CStr(i)
      If Not Sheets(shName) Is Nothing Then
            Sheets(shName).Select
      End If
Next i
On Error GoTo 0
End Sub

As for rows and columns it's just like matrix algebra where the cell reference is Cells(Row, Column) which is opposite of how it's often done using Range("B2") - ColumnRow. Anyways try this code

Sub CellSelection()
Dim RowCnt As Long
Dim ColCnt As Long
For RowCnt = 1 To 10
      For ColCnt = 1 To 10
            Cells(RowCnt, ColCnt).Select
      Next ColCnt
Next RowCnt
 End Sub

It will make a lot more sense if you have the code window open and press F8 repeatedly so that you can see which line of code is producing the on-screen results.

By the way, I like your user name. I'm getting pretty old and forgetting stuff faster than I'm learning new stuff!

 

[forgottenitall]

Thank you so much, craig.penny!
This little bit of code jogged my memory enough for me to work out the kinks in my entire macro! Looks like I haven't "forgottenitall" afterall!

 

[craig.penny]

I'm happy to help!

If you get stuck post here again