consolidate info from multiple worksheets

  • Please bear with me - I am a real novice! I have a retail business which uses a dos based epos system. I have a program to generate sales reports in windows, however, when I export these reports to Excel each page converts to a separate worksheet. Therefore I have a workbook with 300 - 900 worksheets, each with identical column headings, what I need (in order to sort/filter/extract information) is one sheet with the column headings and sales information below. I discovered macros! and I have recorded some so I can get to the stage where SHEET 1 contains the column headings and then 9 rows of sales info and every other sheet contains the 9 rows of sales info. I recorded another macro to cut and paste the sales info to SHEET 1, but I have to click on each individual worksheet, in turn, and run the macro. Is there any way to do this in one go? I am entirely self-taught and have only got this far by trial and (lots of) error (even if I do manage to get something to work I don't always know how I did it!) so I would appreciate any help - but please assume I'm a bit dim!!! :thanx:

  • Re: consolidate info from multiple worksheets


    Rachel


    Can we see the macro you have?


    And could you post a small example workbook with a few sheets with representative date?

    Boo!:yikes:

  • Re: consolidate info from multiple worksheets


    To norie -


    I hope I have sent this correctly - macro 1 and 2 sorts the rows and columns
    to the correct layout then I delete the headings from all sheets and reinsert to sheet one only. Macro 4 is what I use to transfer all the information from each sheet to sheet one (and it is this operation that takes the time).

    In the attached workbook ORIGINAL is how the report looks when first exported to Excel. INFORMATION is what sheets 2 onwards look like when I have run macro 1 and macro 2 and deleted the headings. SHEET ONE is how the report looks when I have run macro 4 on sheets 2 and 3.



    Sub Macro1()
    '
    ' Macro1 Macro
    ' cut + paste rows - all sheets
    '
    ' Keyboard Shortcut: Ctrl+z
    '
    Range("A9:L9").Select
    Selection.Cut
    Range("K8").Select
    ActiveSheet.Paste
    Range("A12:L12").Select
    Selection.Cut
    Range("K11").Select
    ActiveSheet.Paste
    Range("A15:L15").Select
    Selection.Cut
    Range("K14").Select
    ActiveSheet.Paste
    Range("A18:L18").Select
    Selection.Cut
    Range("K17").Select
    ActiveSheet.Paste
    Range("A21:L21").Select
    Selection.Cut
    Range("K20").Select
    ActiveSheet.Paste
    Range("A24:L24").Select
    Selection.Cut
    Range("K23").Select
    ActiveSheet.Paste
    Range("A27:L27").Select
    Selection.Cut
    Range("K26").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollRow = 3
    Range("A30:L30").Select
    Selection.Cut
    Range("K29").Select
    ActiveSheet.Paste
    Range("A33:L33").Select
    Selection.Cut
    Range("K32").Select
    ActiveSheet.Paste
    Range( _
    "7:7,9:9,10:10,12:12,13:13,15:15,16:16,18:18,19:19,21:21,22:22,24:24,25:25,27:27,28:28,30:30,31:31" _
    ).Select
    Range("A31").Activate
    Selection.Delete Shift:=xlUp
    Range("A16").Select
    End Sub


    Sub Macro2()
    '
    ' Macro2 Macro
    ' correct headings - all sheets
    '
    ' Keyboard Shortcut: Ctrl+s
    '
    Range("A5:L6").Select
    Application.CutCopyMode = False
    Selection.Cut
    Range("K5").Select
    ActiveSheet.Paste
    Range("B:B,D:D,G:G").Select
    Range("G3").Activate
    Selection.Delete Shift:=xlToLeft
    Selection.ColumnWidth = 25.71
    Selection.ColumnWidth = 7
    Range("A16").Select
    Columns("D:D").ColumnWidth = 19.43
    Rows("4:4").RowHeight = 20.25
    Columns("G:G").ColumnWidth = 12.57
    Range("F4:H4").Select
    Selection.Cut
    Range("E5").Select
    ActiveSheet.Paste
    Range("G19").Select
    End Sub


    Sub Macro4()
    '
    ' Macro4 Macro
    ' Macro recorded 05/11/2003 by Rachel Kerley
    '
    ' Keyboard Shortcut: Ctrl+x
    '
    Rows("1:9").Select
    Selection.Cut
    Sheets("Sheet1").Select
    ActiveSheet.Paste
    ActiveWindow.LargeScroll Down:=1
    ActiveCell.Offset(9, 0).Range("A1").Select


    End Sub



    many thanks.

    Files

    • question.xls

      (27.65 kB, downloaded 77 times, last: )