Excel Worksheet Report Using Cells from Other Worksheets

[JimmyC]

I have tried searching the forum---but no luck though I wasn't sure of the correct search terms..Anyway, I need to create a worksheet that merely reports data elements copied from other worksheets in the workbook. My workbook has 62 worksheets. The worksheet names are customer last name.

I need to create a report on a new worksheet with the column names as follows {i.e., the cell reference under the column name is the cell reference that has the requred information in each customer worksheet}:
Name Date County Acres Parcel numbers Amount Cost1 Cost2 Cost3 Net
B3 B4 B5 B6 B7 E12 E13 E14 E15 E16

Most of the cell information is numeric, except for name, date, county and parcel number columns---this data is text. How can I "copy" the cell values from the various customer worksheets when the worksheets are no longer the default names Sheet1, Sheet2, etc.? Can this even be done? Thank you....JimC

[HansV]

Are you going to enter the names manually? If so, you could use formulas to look up the rest of the information in the sheet of that name.
Or do you want the entire report sheet to be generated automatically? That would require VBA code.

[JimmyC]

Hans,
Thanks so much the quick reply...I have inherited the workbook with the 62 worksheets and have been asked to create the "report"...I started to print all 62 worksheets to re-enter the requested data manually and thought that there had to be a better way. So I guess I need an automatic report since the worksheets have already been created. I hope I have answered your question. JimC

[HansV]

The following code assumes that the workbook doesn't contain other worksheets than those for the customers, except perhaps a sheet named Report that will be used by the macro (if there is no sheet named Report, the code will create it).

Code: Select all

Sub CreateReport()
  Dim wshData As Worksheet
  Dim wshReport As Worksheet
  Dim n As Long

  ' Make sure we have a blank report sheet
  On Error Resume Next
  Set wshReport = ActiveWorkbook.Worksheets("Report")
  On Error GoTo 0
  If wshReport Is Nothing Then
    Set wshReport = ActiveWorkbook.Worksheets.Add
    wshReport.Name = "Report"
  Else
    wshReport.Cells.ClearContents
  End If
  ' Populate first row
  wshReport.Range("A1") = "Name"
  wshReport.Range("B1") = "Date"
  wshReport.Range("C1") = "County"
  wshReport.Range("D1") = "Acres"
  wshReport.Range("E1") = "Parcel numbers"
  wshReport.Range("F1") = "Amount"
  wshReport.Range("G1") = "Cost1"
  wshReport.Range("H1") = "Cost2"
  wshReport.Range("I1") = "Cost3"
  wshReport.Range("J1") = "Net"

  n = 1
  ' Loop through all sheets but skip Report
  For Each wshData In ActiveWorkbook.Worksheets
    If wshData.Name <> "Report" Then
      n = n + 1
      wshReport.Range("A" & n) = wshData.Range("B3")
      wshReport.Range("B" & n) = wshData.Range("B4")
      wshReport.Range("C" & n) = wshData.Range("B5")
      wshReport.Range("D" & n) = wshData.Range("B6")
      wshReport.Range("E" & n) = wshData.Range("B7")
      wshReport.Range("F" & n) = wshData.Range("E12")
      wshReport.Range("G" & n) = wshData.Range("E13")
      wshReport.Range("H" & n) = wshData.Range("E14")
      wshReport.Range("I" & n) = wshData.Range("E15")
      wshReport.Range("J" & n) = wshData.Range("E16")
    End If
  Next wshData
End Sub

[JimmyC]

Hans,
I think you are as close to genius status as anyone on the planet...Thank you so much for the code--it worked flawlessly--no surprise. I need to study it some next week as I really need to learn this series of code as I can now see so many opportunities to use it elsewhere in my job. This was a last minute request this morning from my boss and Saturday is not even a scheduled work day---sometimes I could just throw my Blackberry out the window. Thanks again for the quick response as your code created the report quickly enough it will allow me to spend more time with my youngest son as today is his 10th birthday and we have family members coming to the house within hours. Again, I can't thank you enough for your help. God Bless.
Jimc

[HansV]

Glad to have been able to help. Congratulations on your son's birthday!

[JimmyC]

Hans,
I'm am so happy to have completed the report {aka important to keep the boss happy in this down economy} and more happy to be able to enjoy his birthday party guilt free....You have given him a great birthday present....Its too bad with all the gadgets, etc in the world...that I can not "zip" you a piece of cake and ice cream...you clearly should get to celebrate too. Everything is chocolate which is typical of boys in the USA...so hopefully you would like chocolate cake and ice cream. Thanks so much.

I really have appreciated your help today and everytime previously you have helped me. If you ever secure a paypal account, I would like to donate something to say "thank you" too....Jim

[HansV]

Hi Jim, I *love* chocolate cake and ice cream, so thank you!

There's no need to pay me - kind words repay me more than money could ever do.