Create report

Topic

New Reply

I need to create a report that extracts the data from cells A1 and C24 in a 50 sheet workbook

Reply | Quote

Replies

Phil,

I assume you mean cells A1 & C24 from each worksheet in the workbook?
How do you want the report to look? What columns / format.
A little guidance please. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Just 2 columns which will be “Name” and “Date” in a new worksheet

Reply | Quote

Assuming that worksheet 1 is the report sheet:

Code:

Public Sub GrabData()
Row = 2
For I = 2 To Worksheets.Count
    With Worksheets(I)
    Cells(Row, 1) = .[a1]
    Cells(Row, 2) = .[c24]
    Row = Row + 1
    End With
Next I
End Sub

Reply | Quote

Maud
Thanks again
Works a treat

Reply | Quote

Phil,

Building on Maud’s fine work you might want to add statements to skip the Report Sheet:

Code:

Public Sub GrabData()
Row = 2
For I = 2 To Worksheets.Count
    With Worksheets(I)
       If .Name  "Your Report Tab name here" Then '<----
         Cells(Row, 1) = .[a1]
         Cells(Row, 2) = .[c24]
         Row = Row + 1
       End If                                                                           '<----
    End With
Next I
End Sub

Note: changes are Air Code but should be correct when you add the Tab name where indicated.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Oh Retired one!
Thanks for that
I inserted a sheet named “Driver Training” but the macro dumped the retrieved data into the sheet I started from

Reply | Quote

Phil,

Code:

Public Sub GrabData()
    Sheets("Driver Training").Select
    Row = 2
    For I = 2 To Worksheets.Count
    With Worksheets(I)
       If .Name  "Driver Training" Then
         Cells(Row, 1) = .[a1]
         Cells(Row, 2) = .[c24]
         Row = Row + 1
       End If                                                                          
    End With
Next I
End Sub

This should fix that. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Yep
That did it.
Thanks again works great

Reply | Quote

RG,

Clever tweak. Using your amended code, you might want to change the line to:

For I = 1 To Worksheets.Count

in case the “Driver Training” sheet is not the first sheet and you want the first sheet to be looked at.

Maud

Reply | Quote

Maud,

GOOD POINT! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote