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]

sometimes works and sometimes does not.

As far as I can tell the only thing that can make it "variable", is your input box.
Your Header selection doesn't make sense, you can't possibly have headers spanning almost 400 rows.

I think the only change that you need to make is this:-

    ' XXX Change Row - to get first row AFTER heading
    'startRow = headers.Row + 1
    startRow = headers.Row + headers.Rows.Count

I have also added these Dim statements

    'XXX Additional Dim statements
    Dim mtr As Worksheet
    Dim wb As Workbook
    Dim ws As Worksheet

But when you select headers ONLY SELECT THE HEADERS
The macro will then:

• Copy the headers from the sheet the headers were selected to the master at position A1
• Then for each sheet copy from the first data row & column to the last data row and column to the next available row in Master starting in Column A

 

[CY078]

Yeh the headers part never made sense to me either ... but when I actually selected headers .. nothing happened. I had to select the whole data set.

Either way I have applied the suggested changes but I get this error

 

[Alex Blakenburg]

Take a screen shot of the input box after you make the selection and also of the the sheet showing me what your selection looks like in terms of the actual sheet (the screen shot needs to include column and row references)

 

[CY078]

So this is if I was to select only the headers I require. Obviously it goes out to col AD

 

[Alex Blakenburg]

OK I know what it is but we should make some other changes but I need more information first.
when you replaced the lastRow line you also lost the line
startCol = headers.Column

Do you know why there is requirement to manually select the header ?

eg​

does the number of heading rows change or is it always just 1 ?​

does the row number of the heading change ?​

We are using the 1st column of the header to determine where the data finishes but it seems to have blanks in some rows (currently Agency column).
Do you trust that the last row with data in that column (Agency) is the really the last row of data ?

 

[CY078]

No I do not know why there is a requirement to manually select the headers. They are all on the same row / column on each tab.

Ah yes ... I realised that having no data in col B did affect the outcome. I will put something in col A (some character or what not) to ensure it captures all the data set.

Also there are active formulas on the tabs .. if it grabs the data .. should i 1) hardcode the data before exporting it, 2) can hardcode it after its compiled as it doesn't make a difference ?

 

[Alex Blakenburg]

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.

Sub Merge_Sheets()

    Dim startRow, startCol, lastRow, lastCol As Long
    Dim headers As Range
    
    'XXX Additional Dim statements
    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")
          
    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
            '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

 

[CY078]

It did kinda work. The data set was still all wonky (columns put in different columns when collated)

What did work was putting a character in Column A across the data set.

The column I am after is "Z" (which is the total $$ column) ... but when I tried changing the parameters below it didn't work

 

[Alex Blakenburg]

I don’t understand what you are trying to do. Everything up to now indicated the aim was to consolidate columns B to AD.
What are you trying to do with column Z ?
Also I need a picture of what are you are getting as a result and what you are expecting.
I am login off for the night shortly.