Gathering data from multiple worksheets

[superkopite]

Hi guys and gals,

It has been years since my last post!

I need to gather data from multiple worksheets to display on one page. For example for Every worksheet I want to grab the contents of A1, A2, I8 and G8.

I can do this manually, of course. But I wanted to see if there was a way to do this automatically, maybe with a macro, so that even if I add new worksheets, that data is captured.

Any ideas as to a starting point.

Many thanks, as always

 

[mrshl9898]

Depends what you want to do with the data.

Something like this could be a start:

Dim ws As Worksheet
Dim mystr as String


For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Sheet1" Then
        mystr = mystr & ", " & ws.Range("A1").value & ", " & ws.Range("A2").value & ", " & ws.Range("I8").value & ", " & ws.Range("G8").value
   End If
Next rs

 

[superkopite]

Depends what you want to do with the data.

Something like this could be a start:

Dim ws As Worksheet
Dim mystr as String


For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Sheet1" Then
        mystr = mystr & ", " & ws.Range("A1").value & ", " & ws.Range("A2").value & ", " & ws.Range("I8").value & ", " & ws.Range("G8").value
   End If
Next rs

Thanks mrshl, I think I can workout what this is going to do, however I fear I may have explained myself poorly originally.
I don't actually what to form a string from the various bits of data. I want to put them into individual cells. For example, column A on Invoices worksheet, would display the contents of A1 from all of the other worksheets, Column B would display the contents of A2 etc

 

[mrshl9898]

So in invoices A2 would be from the first sheet, A3 from the second etc?

How's this looking?

Dim ws As Worksheet
Dim mystr As String
Dim rownum As Long


rownum = 2


For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Invoices" Then
        Sheets("Invoices").Cells(rownum, 1).Value = ws.Range("A1").Value
        Sheets("Invoices").Cells(rownum, 2).Value = ws.Range("A2").Value
        Sheets("Invoices").Cells(rownum, 3).Value = ws.Range("I8").Value
        Sheets("Invoices").Cells(rownum, 4).Value = ws.Range("G8").Value
        rownum = rownum + 1
   End If
Next rs

 

[superkopite]

So in invoices A2 would be from the first sheet, A3 from the second etc?

How's this looking?

Dim ws As Worksheet
Dim mystr As String
Dim rownum As Long


rownum = 2


For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Invoices" Then
        Sheets("Invoices").Cells(rownum, 1).Value = ws.Range("A1").Value
        Sheets("Invoices").Cells(rownum, 2).Value = ws.Range("A2").Value
        Sheets("Invoices").Cells(rownum, 3).Value = ws.Range("I8").Value
        Sheets("Invoices").Cells(rownum, 4).Value = ws.Range("G8").Value
        rownum = rownum + 1
   End If
Next rs

Thanks that looks pretty spot on, I think that will do exactly what I need it to, I can tinker with it from there, but I am getting a Complie Error: Invlaid Next control variable ref on "Next rs"

 

[superkopite]

Think I worked it out

Next rs should have been Next ws

Looks like it is working now, so much thanks to you!!!!!

I love this fourm so much

 

[mrshl9898]

Good catch, sorry for that one.