Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering data from all worksheets for a report
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering data from all worksheets for a report
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering data from all worksheets for a report
That worked pretty well. I'll fine tune it, but it's pretty much what I
needed. Thank you VERY MUCH! Jonco "jonco" wrote in message . com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing data from daily worksheets into my annual report | Excel Discussion (Misc queries) | |||
Gathering data from multiple worksheets | Excel Discussion (Misc queries) | |||
Gathering totals from various worksheets | Excel Worksheet Functions | |||
gathering information from many worksheets | Excel Discussion (Misc queries) | |||
Gathering data | Excel Programming |