Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to copy all sheets into a new sheet

    I would have a workbook containing several sheets. All the sheets contain the same headings which are in Columns A to L and rows 1 to 4

    I would like a macro to copy all the sheets into a new sheet called “Consolidate” . None of the headings are to be copied into the sheet "consolidate", except sheet1 as all the headings on the other sheets are the same as sheet 1

    Your assistance in this regard is most appreciated



  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does the code in http://windowssecrets.com/forums/sho...l=1#post460565 work?

    If you are using XL2007 or later you should change the "65536" to "1048576" since the number of rows has increased.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for pointing me in the right direction-The code works perfectly. I made a small change to the code to increase the range of the columns that needed to be selected

    Regards

    Howard

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve & Howard,

    How about?
    Code:
    For Each wshCur In wbkCur.Worksheets
    		lngRow = wshNew.Range("A"+format(rows.Count)").End(xlUp).Row + 1
    		wshCur.Range(wshCur.Range("A2"), wshCur.Range("K"+format(rows.Count)).End(xlUp)).Copy _
    			Destination:=wshNew.Range("A" & lngRow)
    	Next wshCur
    Which doesn't care which version of Excel.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    HowardC (2012-06-23)

  6. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for the code. When running the code it comes up with a syntax error and the following code is highlighted

    lngRow = wshNew.Range("A"+format(rows.Count)").End(xlUp).Ro w + 1

    Regards

    Howard

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Remove the dbl-quote after Count and remove the space within the word Row

    lngRow = wshNew.Range("A"+format(rows.Count)).End(xlUp).Row + 1

    Steve

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    HowardC (2012-06-23)

  9. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the advise

    Regards

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •