Moving info from multiple sheets (ove 130) into a single sheet

  • Thread starter Thread starter suziemorrel
  • Start date Start date
S

suziemorrel

I am attempting to analyze financial data that is only available in a
poorly organized text file. Currently we have developed a macro that
imports the file to excel, then breaks out the information by
individual componants on separate sheets. I have two actions to perform
to make this a readable report: 1) The reference data on each sheet is
in cells A1:B3 (Col A has the lable, Col B has the data). I'd like to
populate this next to every line item on the sheet. 2)I'd like to
consolidate all this info onto a summary sheet for reporting purposes
(or to make a pivot table, etc).

These are daily reports so any tips would be helpful... I can forward
our current macro, as well as examples of output and what I would LIKE
it to look like. The code is several pages long so I'll avoid posting
it here.

Thanks.
 
Maybe this might work:

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim curWkbk As Workbook
Dim wks As Worksheet
Dim DestCell As Range

Set curWkbk = ActiveWorkbook
Set newWks = Workbooks.Add(1).Worksheets(1)

Set DestCell = newWks.Range("a1")

For Each wks In curWkbk.Worksheets
wks.Range("a1:B3").Copy _
Destination:=DestCell
With newWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Next wks

End Sub


Have you thought of just importing the text file once more into one giant
worksheet?
 
Back
Top