+ Reply to Thread
Results 1 to 11 of 11

Consolidating worksheets

  1. #1
    Registered User
    Join Date
    08-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Office 2010
    Posts
    71

    Consolidating worksheets

    Hi

    I want to consolidate 3 worksheets into a single summary sheet, I've found the following code but it stops at the highlighted bit saying that it isn't defined. I got the code from Microsoft's website so I'm a bit puzzled

    Please Login or Register  to view this content.
    Can anybody help me please? I got the code from
    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Consolidating worksheets

    You have not copied the Lastrow function from that site.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    08-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Office 2010
    Posts
    71

    Re: Consolidating worksheets

    facepalm-500x4001.jpg

    Thanks
    Last edited by davidman1; 11-06-2012 at 08:00 AM.

  4. #4
    Registered User
    Join Date
    08-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Office 2010
    Posts
    71

    Re: Consolidating worksheets

    OK....So now I've defined the function I can either have the header row listed twice or I can have the data with no header row.....is there a way of getting one header row and all the data?

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Consolidating worksheets

    The macro has the startrow as 2, which is the row below the header in your case, right?

  6. #6
    Registered User
    Join Date
    08-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Office 2010
    Posts
    71

    Re: Consolidating worksheets

    Yeah, but if I change the start row to 1 then it copies the headers in from every spreadsheet (they're the same in each one).

  7. #7
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Consolidating worksheets

    Yeah, why do you want to change it to 1? Leave it as it is.

  8. #8
    Registered User
    Join Date
    08-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Office 2010
    Posts
    71

    Re: Consolidating worksheets

    I need the headers in there too.

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Consolidating worksheets

    One way you can do it is this - After "startrow = 2", enter this line
    Please Login or Register  to view this content.
    This works fine as long as there are not too many headers, you will be spending time in typing in each one. When you specify the range A1:J1, ensure that you put in 10 headers in the split brackets. I just put in 5 for now.

  10. #10
    Registered User
    Join Date
    08-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Office 2010
    Posts
    71

    Re: Consolidating worksheets

    Thanks - that's great!

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Consolidating worksheets

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1