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

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.

Hi

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

Post back if you need more help

--
Regards Ron de Bruin
http://www.rondebruin.nl


<[email protected]> wrote in message news:[email protected]...
>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?

[email protected] wrote:
>
> 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.

--

Dave Peterson

Thanks, that helped me out!