Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: VBA Consolidation

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm trying to write a macro that will consolidate all the worksheets.

    The only way I have found is to list the actual worksheet names in the consolidation array line. The files I want to use this macro for are automatically created and the names may change so I can't hard code them in.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    what are you consolidating? does each sheet have identical width list with column headers in row 1? If so, try...



    Sub consolidate_lists()
    For sheetno = 2 To Sheets.Count
    Sheets(sheetno).Rows("2:5").Copy
    Sheets(1).Rows("2:2").Insert Shift:=xlDown
    Next
    End Sub


    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The left column has product names and the top row has a column heading. Not all the products are on every worksheet. I need a way to add up the ones that are on multiple sheets (but different rows) and include the ones that are only on one sheet.

    The consolidation function does this and I have it referencing the top and left cells for headings/columns.

    Here is the code I have that does not work, it doesn't like my string in the array. But, if I type the string in it is fine.

    (Works if I replace the last-
    Array(ConRange) with
    Array("Sheet1!R11C1:R27C4", "Sheet2!R11C1:R27C4", "Sheet3!R11C1:R27C4")

    *****

    GrpWorkSheets = Worksheets.Count
    CurWorksheet = 2

    Do While CurWorksheet <= GrpWorkSheets
    Worksheets(CurWorksheet).Activate
    sheetname = Worksheets(CurWorksheet).Name
    rangename = Chr(34) & sheetname & Chr(33) & "R11C1:R27C4" & Chr(34) & ", "
    ConRange = ConRange & rangename
    CurWorksheet = CurWorksheet + 1
    Loop

    numChar = Len(ConRange)
    numChar = numChar - 2
    ConRange = Left(ConRange, numChar)
    Worksheets(1).Activate
    Range("A11").Select
    Selection.Consolidate Sources:=Array(ConRange), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

    [ This Message was edited by: Bruce on 2002-04-19 14:42 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    haven't tested your code or really scrutinised but on first view looks like you're missing an exclamation mark in...

    rangename = Chr(34) & sheetname & Chr(33) & "R11C1:R27C4" & Chr(34) & ", "


    ...ie...

    rangename = Chr(34) & sheetname & Chr(33) & "!R11C1:R27C4" & Chr(34) & ", "


    ...if you are going to go about it this way.


    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It didn't like the actual exclamtion mark in quotes so that's the chr(33).

    For testing I had it place ConRange value in a cell and then copy/pasted it into the array line and everything worked fine. It doesn't like me referencing it with ConRange for some reason.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i have abbreviated you code down to a few lines...



    For worksheetno = 2 To Worksheets.Count
    conRange = conRange & Sheets(worksheetno).Name & "!R11C1:R27C4" & Chr(34) & ","
    Next

    conRange = Left(conRange, Len(conRange) - 1)

    Sheets(1).Range("A11").Consolidate Sources:=Array(conRange), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False



    _________________

    DALEY


    [ This Message was edited by: daleyman on 2002-04-19 14:48 ]

    [ This Message was edited by: daleyman on 2002-04-19 14:49 ]

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    okay, i see the chr(33) now, sorry. so then you should take out the chr(34) ????

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sorry, am just being stupid, ignore my last statement.

    PS: why are you doing numChar=numChar-2, surely you only need strip one character?


    _________________

    DALEY


    [ This Message was edited by: daleyman on 2002-04-19 14:55 ]

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your coding didn't mind the "!"

    So now your coding is more effiecent (THANKS!!), but same issue.

    I did the same test and added a temporary line to have the ConRange go into a cell and copy/pasted that into the coding and it worked perfect...still didn't work with ConRange being in there.

    ----
    For worksheetno = 2 To Worksheets.Count
    ConRange = ConRange & Chr(34) & Sheets(worksheetno).Name & "!R11C1:R27C4" & Chr(34) & ","
    Next

    ConRange = Left(ConRange, Len(ConRange) - 1)
    Sheets(1).Range("A11").Consolidate Sources:=Array(ConRange), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was stripping a space I after the comma, it wasn't neccesary...just a little easier to read when viewing the string. Your coding just had the comma which is fine.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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