VBA help

[CY078]

Hi All

Background
- I'm having very inconsistent results with my Macro.
- Fundamentally it is meant to grab all the data from all tabs and then combine it into one.
- It sometimes works and sometimes does not.

Macro

Sub Merge_Sheets()

Dim startRow, startCol, lastRow, lastCol As Long
Dim headers As Range

'Set Master sheet for consolidation
Set mtr = Worksheets("Master")

Set wb = ThisWorkbook
'Get Headers
Set headers = Application.InputBox("Select the Headers", Type:=8)

'Copy Headers into master
headers.Copy mtr.Range("A1")
startRow = headers.Row + 1
startCol = headers.Column

Debug.Print startRow, startCol
'loop through all sheets
For Each ws In wb.Worksheets
'except the master sheet from looping
If ws.Name <> "Master" Then
ws.Activate
lastRow = Cells(Rows.Count, startCol).End(xlUp).Row
lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
'get data from each worksheet and copy it into Master sheet
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If
Next ws

Worksheets("Master").Activate

End Sub

Process
- Run Macro
- It will ask me to "Select the Headers"

- I proceed to select the headers. They are all in the same place on all the tabs.
Note1: If I put just row 37 ... it does not work at all (Thus I have to put the block of data)
Note2: Putting the tab name in front of the cell selection does not make a difference
Note3: There are some tabs that I don't require the data from but I assume it will just grab the data at that point and I sort out what I require ... (thus the assumption there is no impact on the end result)

Outcome
- All data is extracted and put into a tab named "master"
- When it works, I just sort by the "yearly total" column, and delete the other data I do not require
- Sometimes it just gets the data from one of the tabs
- Sometimes its a mixture of data from many tabs

Question
- Have I written the macro incorrectly ?
- Is it something to do with the headers ?
- Is it something to do with something else ?

Notes
- I'm using MS365 64bit
- I can't download a "XL2BB" cause this is a company lappy. I require admin access to put it on.

Can anyone shed some light on my situation please.

Thank you in advance

 

[CY078]

1) You're a fellow Aussie too
2) Wasn't intentionally stalking .. but I was trying to send a blank template of the worksheet but I can't attached an excel file. Is there a way to do so without this XL2BB ?
3) To answer your questions

• Do all the data sheets start in column B like the one you have showed us. Yes all the same columns .. maybe some are different rows
• Instead of using column Z to work out the last row can we use Current Region.
  (ie can we rely on all the sheets not having a completely blank row) I actually used the heading "key" in the blank column A .. and then used a unique numbering sequence going down each row .. but this did not work either.
• Whatever words we use in the heading to Find the heading row, do all the data sheets in all the workbooks contains those words Yes all the headings are the same on each tab
• Where are you storing the macro and how do you run it on the different workbooks Its saved in the spreadsheet itself. So whenever I open up different workbooks I just click on the Macro
• Do they all only have one sheet to exclude being the Master or do some have other sheets to exclude I combined the other sheets that I didn't require because I did not know how to exclude them. I just sorted the data (manually) in the master sheet and got what I required.

 

[Alex Blakenburg]

If you can, share the file(s) using one of the sharing platforms eg Dropbox, Onedrive, googledrive etc. Make the file accessible to anyone with the link and post the link here.

 

[CY078]

Test.xlsm

Ok .. hopefully that works. Basically had to send it to another computer as my work won't let me on google drive either.

Hopefully you will be able to see the template.

Fundamentally how it works is
- Users fill out the green tab
- Its normally protected so all users can't "accidentally" delete / input wrong data.
- Col B / C they can fill out or leave blank
- Col E / G / H they MUST fill out (in this example Col G is prefilled for the user based on the tab ... but other templates there is a drop down box for them to choose)
- Col I is a nice to have
- Col J:N requested from the users
- Col O:Z are monthly phasing of $$
- Col AA is the total (previously I referred this to Col Z but this particular team required one more col than others)
- The rest is self explanatory
- The summary tabs is just that .. sum of all other tabs (the formulas in there are very simple ... this worksheet was a hand-me-down and I haven't changed them yet .. but hey they work)

So fundamentally the Macro is just meant to pull all the data from each individual (green) tab from $B$30:$AA$155 (or wherever the last row of the total is) into one tab so all the data is together. This step is repeated for all teams where the end result is to get a compilation of all data on one sheet for the entire division.

- Some worksheets work flawlessly (eg: once compiled I check that the total monthly data = the total yearly data .. then total yearly data = total in summary ... 2 second check to make sure it reconciles)
- However some worksheets the data gets moved around left/right or its missing some rows etc etc .. ie: does not reconcile with the total figure in the summary tab.

Hope the above makes sense.

 

[Alex Blakenburg]

I will have a look tomorrow. Goodnight from Sydney.

 

[Alex Blakenburg]

I am not surprised that you are always having to reconcile stuff.
It might even be worth consider using Power Query.

Why are they never starting on the first row after the heading ?

I don't think based on that file you can use column Z to determine the last row.
Based on you description I take it Internal Order is mandatory and that might be a better choice to determine the last row.
Do you agree ?

Do the data sheets to be consolidated always end in a 6 digit number (in all workbooks) ?

 

[Michael M]

@CY078
I agree with @Alex Blakenburg that you might be better off finding the TRUE last row rather than focusing on 1 column. I haven't read all of the post but it does seem like a fairly straightforward task made more complex by the header selection.

dim lr as long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row

AND

 

[Alex Blakenburg]

@CY078
I agree with @Alex Blakenburg that you might be better off finding the TRUE last row rather than focusing on 1 column. I haven't read all of the post but it does seem like a fairly straightforward task made more complex by the header selection.

dim lr as long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row

AND

Sadly I have had a look at the Test file and some fields are auto-populated well past the input data.

 

[CY078]

Why are they never starting on the first row after the heading ?

Some of the other worksheets they do ... but the basic answer is some users just put the data wherever. We're talking about very administratively challenged users here so I am thankful there is some kind of data in there to start with.

I don't think based on that file you can use column Z to determine the last row.
Based on you description I take it Internal Order is mandatory and that might be a better choice to determine the last row.
Do you agree ?

Thanks why I was thinking of just using column A as a unique key. Literally have "Key" in the heading and then numbered all the way down the page to where the table stops. This way at least I get all the info, even if the user has forgotten to put in the mandatory fields. Then I just sort by the total column to get all the figures. This way I can quickly reconcile against the summary and if there are extra $$ (due to user not inputting in mandatory fields where the summary would not add it up) then I can ask them if it was "accidental" or did they wish to include the $$ in the forecast.

Do the data sheets to be consolidated always end in a 6 digit number (in all workbooks) ?

Do you mean the 6 digit cost center (col F) ?? If so then yes.

Sadly I have had a look at the Test file and some fields are auto-populated well past the input data.

Wouldn't this be like my thinking of using Col A as a unique key ?? Shouldn't it just return all the data in the row but the total is $0 ?

 

[Alex Blakenburg]

OK try the code below.

• It will select any worksheet ending in a 5 character numeric.
• It sounded like all the key column A was doing it to select all the data rows regardless of content, so I have opted to just use @Michael M's suggestion to use the find method of getting the last row. With totally empty rows being a possibility I have used that method for getting the last row on the Master too.
• I have changed the Copy Paste to copying Values and Formats.

Sub Merge_SheetsNew()
    Dim hdgRow As Long, startRow  As Long, lastRow   As Long, lastCol As Long
    Dim headers As Range
    Dim mtr As Worksheet
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim HdrKeyCell As Range
    Dim KeyCol As Long
    Dim mtrlastRow As Long
    Dim FindInHdr As String
    Dim headerRow As Long
   
    Application.ScreenUpdating = False
   
    'Set Master sheet for consolidation
    Set mtr = Worksheets("Master")
    Set ws = ActiveSheet
   
    ' This is currently the case but may change since it is being installed on multiple workbooks
    Set wb = ThisWorkbook
    FindInHdr = "Internal Order"
   
    ' Find first Data Sheet
    For Each ws In wb.Worksheets
        If IsNumeric(Right(ws.Name, 5)) Then
            Set HdrKeyCell = ws.Cells.Find(What:=FindInHdr, After:=ws.Range("A1"), LookIn:=xlFormulas, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                True)

            If HdrKeyCell Is Nothing Then
                MsgBox "Header row not found in sheet " & ws.Name & " using " & FindInHdr & vbLf _
                & "Exiting Procedure"
                GoTo ExitSub
            End If
            Exit For
        End If
   
    Next ws
   
    hdgRow = HdrKeyCell.Row
    KeyCol = HdrKeyCell.Column
    lastCol = ws.Cells(hdgRow, Columns.Count).End(xlToLeft).Column
   
    'Copy Headers into master
    Set headers = ws.Range(ws.Cells(hdgRow, "B"), ws.Cells(hdgRow, lastCol))
    headers.Copy mtr.Range("A1")
   
    For Each ws In wb.Worksheets
        'Was only excluding master now pick up data sheets based on trailing numeric value
        If IsNumeric(Right(ws.Name, 5)) Then
            With ws
                startRow = hdgRow + 1
                lastRow = ws.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
                mtrlastRow = mtr.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
               
                If lastRow > startRow Then
                    'get data from each worksheet and copy it into Master sheet
                    'check internal order no in
                    .Range(.Cells(startRow, "B"), .Cells(lastRow, lastCol)).Copy
                    mtr.Range("A" & mtrlastRow).PasteSpecial xlPasteValues
                    mtr.Range("A" & mtrlastRow).PasteSpecial xlPasteFormats
                End If
            End With

        End If
    Next ws
   
    mtr.Range(Cells(1, "A"), mtr.Cells(1, Columns.Count).End(xlToLeft)).EntireColumn.AutoFit
    mtr.Activate
    mtr.Range("A2").Select
         
ExitSub:
    Application.ScreenUpdating = True

End Sub

 

[CY078]

It worked for a couple of the worksheets but for some of them they missed some tabs ?

The structure is definitely stable now as all the results follow the headings (ie: does not move left / right)

I'll have a more in depth look as to why it may be missing some tabs and i'll get back to you.

Thanks for the update too btw.