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

[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.

 

[Ron de Bruin]

Hi

See my tips page for example code
http://www.rondebruin.nl/tips.htm

Post back if you need more help

 

[Dave Peterson]

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?

 

[Suzie M]

Thanks, that helped me out!