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

 

[Alex Blakenburg]

Also your screen shot is indicating it is failing on the Find line, if that is the case then make sure that "By Project / Category" exists in your spreadsheets heading line. You only posted a picture so if I have retyped it with the wrong number of spaces it will fail.
It just needs any text that will always be in the heading row and will not appear anywhere else in the sheet.
eg so not internal order because that appears in column I at the top as well as in the heading.

If you prefer we can just hard code it as being row 37.

 

[CY078]

The aim still remains to consolidate columns B to AD

Try this:
It won't ask you for a heading. It will go looking for "By Project / Category" in the current sheet or the 1st sheet after master
(anything that is unique to the header row will do, I am only looking for the row number)
It is still relying on Column B / Column 2 to work out the last row. If this is not the most reliable column to use tell me and we should address that.

I assumed when you asked if it is the most reliable column to use ... that I could use any column within that range to "work out the last row". So the most reliable column with all the data I require is column "Z" (which is the column of total of all $$)

However it works just as fine if I use the range "A:AD" and just putting a character in col A to "work out the last row"

Either way it seems to be working now and seems to be stable (for the moment :P).

For moderators .. i'll work on it throughout the day to see if any other issues pop up before closing this post off as "solved"

Thank you very much for your help.

 

[Alex Blakenburg]

Just testing my change

 

[Alex Blakenburg]

If apart from trying to change the column Z, you haven't changed anything then just use the below.

Changed:
Removed the debug.print I forgot to remove last time.
Changed the last row (xlup) dependency to column Z (which in Master is column Z minus 1)

            ' XXX Changed xlUp dependency to Column Z (source and master latter being Z minus 1)
            lastRow = Cells(Rows.Count, "Z").End(xlUp).Row
            '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, Columns("Z").Column - 1).End(xlUp).Row + 1)

Full Code incorporating the above

Sub Merge_Sheets()

    Dim startRow, startCol, lastRow, lastCol As Long
    Dim headers As Range
    Dim mtr As Worksheet
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim FindString As String
    Dim headerRow As Long
    
    'Set Master sheet for consolidation
    Set mtr = Worksheets("Master")
    Set ws = ActiveSheet
    
    Set wb = ThisWorkbook
    'Get Headers
    'Set headers = Application.InputBox("Select the Header", Type:=8)
        
    If ws.Name = mtr.Name Then
        Set ws = ws.Next
        ws.Activate
    End If
    
    FindString = "By Project / Category"
    headerRow = Cells.Find(What:=FindString, After:=Range("A1"), LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Row
   
    ' XXX Change Row - to get first row AFTER heading
    'startRow = headers.Row + 1
    'startRow = headers.Row + headers.Rows.Count
    startRow = headerRow + 1
    startCol = 2
    lastCol = Cells(headerRow, Columns.Count).End(xlToLeft).Column
    Set headers = Range(Cells(headerRow, "B"), Cells(headerRow, lastCol))
    'Copy Headers into master
    headers.Copy mtr.Range("A1")
          
    'loop through all sheets
    For Each ws In wb.Worksheets
        'except the master sheet from looping
        If ws.Name <> "Master" Then
            ws.Activate
            ' XXX Changed xlUp dependency to Column Z (source and master latter being Z minus 1)
            lastRow = Cells(Rows.Count, "Z").End(xlUp).Row
            '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, Columns("Z").Column - 1).End(xlUp).Row + 1)

        End If
    Next ws
    
    Worksheets("Master").Activate

End Sub

 

[CY078]

Its still showing that the below is an error

 

[Alex Blakenburg]

Does By Project / Category exist in your row 37 ?

 

[CY078]

Yes in Col C .. but it has "(if applicable)" written after that

 

[Alex Blakenburg]

If you use Find in the spreadsheet itself and paste the words in does it find it ? There may be a line feed character in there before the word Category.
Alternatively, replace the phrase in the code with something like: Job Description or Estimate Signed

 

[CY078]

Yes it did find it in the spreadsheet with the find function.

I replaced the code with "Internal Order" which got most of the data. One of the tabs it missed a section of data even though everything was correct filled out in the tab ??

I run this macro on about 10 separate workbooks (different people) and it works for some and not for others even though everything is the same. I spend more time trying to reconcile than the work itself .. hahaha

 

[Alex Blakenburg]

I wouldn't use Internal Order since your first picture shows that those words appear at the top of Column I and you need to look for something that is unique to the heading. If you want to use Internal Order at least try changing the find from xlPart to xlWhole, that way it should not pick up the By Internal Order at the top of column I

I run this macro on about 10 separate workbooks (different people) and it works for some and not for others even though everything is the same. I spend more time trying to reconcile than the work itself

Running it on separate workbooks is probably why the macro initially got you to select where the heading row is.
Spending so much time reconciling it defeats the purpose of having a macro.
We can probably help you but we need much better visibility of the variations between workbooks.
eg.

• Do all the data sheets start in column B like the one you have showed us.
• 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)
• Whatever words we use in the heading to Find the heading row, do all the data sheets in all the workbooks contains those words
• Where are you storing the macro and how do you run it on the different workbooks
• Do they all only have one sheet to exclude being the Master or do some have other sheets to exclude