Merge Worksheets to a Master Worksheet - Issue with LastRow

BoyMom826

New Member
Joined
Dec 11, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hello!
I am trying to merge all of my account manager's data into one master spreadsheet. I keep getting an error on the LastRow. Can anyone assist with what I am doing wrong here? Or information on a better way to do it?
I am a major newbie when it comes to VBA, but I am trying to get this master sheet to be functional management who has very little knowledge of excel. Hoping to add a button for manager to push to have updated info and this seems like the best option considering I have 7 lines of income formulas above it.

VBA Code:
Sub MergeRARSheets()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim shLast As Long
    Dim CopyRng As Range
    Dim StartRow As Long
    Dim LastRow As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Delete the sheet "Master" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Master").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "Master"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Master"

    'Fill in the start row
    StartRow = 9

    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13", "Sheet14"))
    
            'Find the last row with data on the DestSh and sh
            Last = LastRow(DestSh)
            shLast = LastRow(sh)

            'If sh is not empty and if the last row >= StartRow copy the CopyRng
            If shLast > 0 And shLast >= StartRow Then

                'Set the range that you want to copy
                Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

                'Test if there enough rows in the DestSh to copy all the data
                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                    MsgBox "There are not enough rows in the Destsh"
                    GoTo ExitTheSub
                End If

                'This example copies values/formats, if you only want to copy the
                'values or want to copy everything look below example 1 on this page
                CopyRng.Copy
                With DestSh.Cells(Last + 1, "A")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With

            End If

        End If
    Next

ExitTheSub:

    Application.Goto DestSh.Cells(1)

    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Do you have a function called "LastRow"
 

BoyMom826

New Member
Joined
Dec 11, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Do you have a function called "LastRow"
You know what, I am not sure. I copied this code from an article online in hopes of automating with little knowledge. :-/
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
In that case I suspect you didn't copy th function.
Will column A have a value in every cell? If not is there a column that will?
 

BoyMom826

New Member
Joined
Dec 11, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case I suspect you didn't copy th function.
Will column A have a value in every cell? If not is there a column that will?
Yes, column A will have values in each cell.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Ok, in that case replace these two lines
VBA Code:
            Last = LastRow(DestSh)
            shLast = LastRow(sh)
with
VBA Code:
            Last = DestSh.Range("A" & Rows.count).End(xlUp).Row
            shLast = sh.Range("A" & Rows.count).End(xlUp).Row
 
Solution

BoyMom826

New Member
Joined
Dec 11, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, in that case replace these two lines
VBA Code:
            Last = LastRow(DestSh)
            shLast = LastRow(sh)
with
VBA Code:
            Last = DestSh.Range("A" & Rows.count).End(xlUp).Row
            shLast = sh.Range("A" & Rows.count).End(xlUp).Row
Thank you. I have updated. I am now showing an "End if without block if" which is highlighting the End If above the next. :/
 

BoyMom826

New Member
Joined
Dec 11, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I figured the end if problem... but now i'm having this one.

I bet you're wishing you didn't start helping me ;):ROFLMAO:
1608763622453.png
 

BoyMom826

New Member
Joined
Dec 11, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Actually, I am going to repost a completely different question because I just now realized that there is not a way to have VBA code on sharepoint docs. Holy cow. All of the information to keep in mind -all of you experts are amazing!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,005
Messages
5,622,133
Members
415,879
Latest member
drumsmasher

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top