Gathering data from all worksheets for a report

[jonco]

I'm want to generate a report by gathering info from certain cells on all
but 4 spreadsheets in a workbook.
I want the selected data from each worksheet to go into a row on the
"Reports" worksheet starting at row 5 (under the headings that remain
constant)
This is what I have so far:
Sub ReportBasic()

Sheets("Reports").Select ' This is the sheet that has the report format
Range("A5").Select ' This is where the first item form the first
worksheet will go

For Each Worksheet In ThisWorkbook.Worksheets

Select Case Worksheet.Name
Case "Index", "Trans", "Customers", "Reports" ' Sheets that I won't pull
data from
'don't do anything
Case Else

' Gather data from valid worksheet

Set CustName = ActiveSheet.Range("A1")
Set CustNumber = ActiveSheet.Range("G1")
Set Limit = ActiveSheet.Range("I2")
Set Freq = ActiveSheet.Range("J2")
Set DueDate = ActiveSheet.Range("L2")
Set Status = ActiveSheet.Range("M2")
Set Total = ActiveSheet.Range("R1")
Set LastPaid = ActiveSheet.Range("N2")
Set LastPaidAmt = ActiveSheet.Range("O2")

' Insert Customer Info on report
Sheets("Reports").Select
ActiveCell.Value = CustNumber
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Total
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = DueDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Freq
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Limit
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Status
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = LastPaid
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = LastPaidAmt
ActiveCell.FormulaR1C1 = CustName
'
End Select
Next

End Sub


Any help is greatly appreciated.

Jonco

 

[Dave Peterson]

Maybe something like:

Option Explicit
Sub ReportBasic()
Dim RptWks As Worksheet
Dim DestCell As Range
Dim wks As Worksheet

Dim CustName As Range
Dim CustNumber As Range
Dim Limit As Range
Dim Freq As Range
Dim DueDate As Range
Dim Status As Range
Dim Total As Range
Dim LastPaid As Range
Dim LastPaidAmt As Range

Set RptWks = Worksheets("Reports")
Set DestCell = RptWks.Range("a5")

For Each wks In ThisWorkbook.Worksheets
Select Case LCase(wks.Name)
' Sheets that I won't pull data from
Case "index", "trans", "customers", "reports"
'do nothing
Case Else
' Gather data from valid worksheet
Set CustName = wks.Range("A1")
Set CustNumber = wks.Range("G1")
Set Limit = wks.Range("I2")
Set Freq = wks.Range("J2")
Set DueDate = wks.Range("L2")
Set Status = wks.Range("M2")
Set Total = wks.Range("R1")
Set LastPaid = wks.Range("N2")
Set LastPaidAmt = wks.Range("O2")

' Insert Customer Info on report
With DestCell
.Offset(0, 0).Value = CustNumber.Value
.Offset(0, 1).Value = CustName.Value
.Offset(0, 2).Value = Total.Value
.Offset(0, 3).Value = DueDate.Value
.Offset(0, 4).Value = Freq.Value
.Offset(0, 5).Value = Limit.Value
.Offset(0, 6).Value = Status.Value
.Offset(0, 7).Value = LastPaid.Value
.Offset(0, 8).Value = LastPaidAmt.Value
End With
'get ready for next set of data
Set DestCell = DestCell.Offset(1, 0)
End Select
Next wks
End Sub

I'd stay away from using a variable named Worksheet.

And I try to declare all the variables that I use. And instead of selecting, I
just plopped values into each cell.

You were always taking stuff from the activesheet. And when you looped through
the worksheets, you never changed that activesheet.

 

[jonco]

That worked pretty well. I'll fine tune it, but it's pretty much what I
needed.
Thank you VERY MUCH!

Jonco