How to write VBA code to auto insert the last row occupied into Master Sheet

[Laurie]

I have an excel document with 5 worksheets and 1 master sheet. I have written the VBA code to automatically update the Master Sheet as shown below. However, I now need to write a code with this to automatically insert the last row occupied from each of the 5 worksheets into the Master Sheet as well. Please help, I am desperate to complete this task.

Sub Auto_Update_All_Categories()
'
' Auto_Update_All_Categories Macro
' This will update all Category Tabs into the Master Roll-up Total Tab
'
'
    Sheets("Warehouse Demo").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("A1:R150").Select
    Selection.Copy
    Sheets("Master List").Select
    Range("A3").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=87
    Sheets("Contam Soil").Select
    ActiveWindow.SmallScroll Down:=-9
    Range("A1:S89").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master List").Select
    Rows("89:89").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=72
    Range("A161").Select
    Sheets("Mobilization-Rig Move").Select
    Range("A1:S49").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master List").Select
    Range("A161").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=39
    Sheets("General Drilling Operations").Select
    Range("A1:S109").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master List").Select
    ActiveWindow.SmallScroll Down:=-6
    Range("A195").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=66
    Sheets("Intermediate Hole Operations ").Select
    Range("N6:N7").Select
    Sheets("Surface Hole Operations ").Select
    ActiveWindow.SmallScroll Down:=-9
    Range("A1:T41").Select
    ActiveWindow.SmallScroll Down:=-24
    Range("A1:S34").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master List").Select
    Range("A262").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=21
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Intermediate Hole Operations ").Select
    ActiveWindow.SmallScroll Down:=-48
    Range("A1:S34").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master List").Select
    ActiveWindow.SmallScroll Down:=-6
    Range("A276").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=15
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Production Hole Operations").Select
    Range("A1:S54").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master List").Select
    ActiveWindow.SmallScroll Down:=-3
    Range("A289").Select
    ActiveSheet.Paste
End Sub

[SamT]

Lose all the unneccessary Active* and Select...Selection

[Laurie]

Hi, thank-you very much for the reply. I'm new to this.
May I ask, what do you mean, lose all the unnecessary Active* and Select...Selection? Do I have the code incorrectly written?

[Laurie]

This VBA Code works very well in my current document. It updates the 5 worksheets automatically onto my Master Sheet. My problem is, when I update my 5 worksheets to my Master Sheet, the new last row occupied lines on any of the 5 working worksheets are not being included in the auto update. Can you advise me, what VBA code I can write and where do I place this code on the VBA editor. I was thinking of (example set references up-front?)

[SamT]

It's Recorded Macro, Not a piece of real developed code, so to speak.

To make it a "real" piece of code
First, delete all lines that Scroll.

Move all

Application.CutCopyMode = False

to just after the next Paste line.CutCopyMode = False clears the ClipBoard.

When you something like

Range("A1:R150").Select
    Selection.Copy

delete the

.Select
    Selection

Leaving only

Range("A1:R150").Copy

Do the same with select... Paste operations so they look like

Range("A276").Paste

So... You done all that
When you now see something like

Sheets("Warehouse Demo").Select
    Range("A1:R150").Copy

Change that to

Sheets("Warehouse Demo").Range("A1:R150").Copy

Do the same with

Now, you should be seeing several pairs of lines that look like

Sheets("Warehouse Demo").Range("A1:R150").Copy
Sheets("Master List")Range("A3").Paste
Application.CutCopyMode = False

Get that done and post the revised Code back here so we can look at it and suggest more edits. I bet we wind up with less than a dozen lines in the final product.

Or, someody might just write the whole thing for you and take away your chance to learn for yourself. Let's hope not.

[offthelip]

Looking through your code (which looks like it was recorded using macro recorder and not actually written) It appears you are updating seven worksheets to the Master list
("Contam Soil")
("General Drilling Operations")
("Intermediate Hole Operations ")
("Mobilization-Rig Move")
("Production Hole Operations")
("Surface Hole Operations ")
("Warehouse Demo")

The range you selecting is different for each sheet and is fixed, this means that when you add new rows they don't get copied. I suspect that what you really want to do is copy all of the data from each of the sheets to the Master sheet. Is this what you want? because it is very easy with excel vba to do that and automatically put it in the next free row on the master sheet.
Are there other sheets in the workbook? or do you want to copy all sheets to the master once again this is very easy to do.

[Laurie]

ok, wow, I do understand your explanation there. Here is the new code as per your instructions.

Sub Auto_Update_All_Categories()
'
' Auto_Update_All_Categories Macro
' This will update all Category Tabs into the Master Roll-up Total Tab
'
'
    Sheets("Warehouse Demo").Range("A1:R150").Copy
    Sheets("Master List").Range("A3").Paste
    Sheets("Contam Soil").Range("A1:S89").Copy
    Application.CutCopyMode = False
    Sheets("Master List").Range("89:89").Paste
    Range("A161").Select
    Sheets("Mobilization-Rig Move").Range("A1:S49").Copy
    Application.CutCopyMode = False
    Sheets("Master List").Range("A161").Paste
    Sheets("General Drilling Operations").Range("A1:S109").Copy
    Application.CutCopyMode = False
    Sheets("Master List").Range("A195").Paste
    Sheets("Intermediate Hole Operations ").Range("N6:N7").Select
    Sheets("Surface Hole Operations ").Range("A1:T41").Range("A1:S34").Copy
    Application.CutCopyMode = False
    Sheets("Master List").Range("A262").Paste
    Sheets("Intermediate Hole Operations ").Range("A1:S34").Copy
    Application.CutCopyMode = False
    Sheets("Master List").Range("A276").Select
    ActiveSheet.Paste
    Sheets("Production Hole Operations").Range("A1:S54").Copy
    Application.CutCopyMode = False
    Sheets("Master List").Range("A289").Select
    ActiveSheet.Paste
End Sub

[Laurie]

Is this what you want? because it is very easy with excel vba to do that and automatically put it in the next free row on the master sheet.
Are there other sheets in the workbook? or do you want to copy all sheets to the master once again this is very easy to do.

Hello Offthelip, yes, that is exactly what I want to do. The 7 active worksheets I have, must be automatically updated to the Master Sheet on demand (I.e. Active Button). This must include the last row occupied on each or any of the 7 worksheets.

[SamT]

In this post, I am only going to finish change your recorded Macro to a real SubRoutine,

Sheets("Warehouse Demo").Range("A1:R150").Copy
    Sheets("Master List").Range("A3").Paste
Application.CutCopyMode = False

    Sheets("Contam Soil").Range("A1:S89").Copy
    Sheets("Master List").Range("89:89").Paste
Application.CutCopyMode = False

    Sheets("Mobilization-Rig Move").Range("A1:S49").Copy
    Sheets("Master List").Range("A161").Paste
    Application.CutCopyMode = False

Sheets("General Drilling Operations").Range("A1:S109").Copy
    Sheets("Master List").Range("A195").Paste
    Application.CutCopyMode = False

Sheets("Surface Hole Operations ").Range("A1:T41").Range("A1:S34").Copy
    Sheets("Master List").Range("A262").Paste
    Application.CutCopyMode = False

Sheets("Intermediate Hole Operations ").Range("A1:S34").Copy
    Sheets("Master List").Range("A276").Paste
    Application.CutCopyMode = False

Sheets("Production Hole Operations").Range("A1:S54").Copy
    Sheets("Master List").Range("A289").Paste
Application.CutCopyMode = False

I see some Range numerical errors in there. But they won't matter in my next post

[SamT]

With Sheets("Master List")
Sheets("Warehouse Demo").Range("A1").CurrentRegion.Copy .Range("A3")
    Sheets("Contam Soil").Range("A1").CurrentRegion.Copy .Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Sheets("Mobilization-Rig Move").Range("A1").CurrentRegion.Copy .Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Sheets("General Drilling Operations").Range("A1").CurrentRegion.Copy .Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Sheets("Surface Hole Operations ").Range("A1").CurrentRegion.Copy .Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Sheets("Intermediate Hole Operations ").Range("A1").CurrentRegion.Copy .Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Sheets("Production Hole Operations").Range("A1").CurrentRegion.Copy .Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Application.CutCopyMode = False
End With

Notes: The default action of a copy is to paste at the (unwritten "Dest") Range

Sheets("Warehouse Demo").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Range("A3")

When using "With," any otherwise unspecified Properties preceded by a dot, (ex: ".Range("A3")," belong to the "With" Object

Memorize the Pattern "Cells(Rows.Count, "A")End(xlUp)". You will use it many many many many times. it is the Pattern of code for finding the bottom used cell of a column
LastRow = Cells(Rows.Count, "A")End(xlUp).Row
Set BottomCell =Cells(Rows.Count, "A")End(xlUp)
Set FirstUnusedCell = Cells(Rows.Count, "A")End(xlUp).Offset(1)

"Cells" is another way of Addressing Ranges, the format is "Cells(RowNumber, ColumnNumber or ColumnLetter)"
Cells(1,2) is = to Range("B1")
Cells(3, "A") is = to Range("A3")
Rows.Count is = to the Row number of the bottommost Row on a Sheet.

"End" works the same way pressing Ctrl+Arrow key does
(xlUp) is = to Up Arrow
(xlDown) is = to Down arrow
(xlToRight) is = Right Arrow
(xlToLeft) is = Left Arrow.

Range(??).Offset(Number of Rows, Number of Columns)
Range("C3").Offset(-2, 0) will "select" Range C1
Range("C3").Offset(2, 4) will "select" Range G5
You can omit one of the parameters, but if you omit the Number of Rows, you must still use the comma
Range("C3").Offset(-2) will "select" Range C1
Range("C3").Offset(, 1) will "select" Range D3
What one number in (most of) the "Copy" lines above would you change to leave a blank Row between each Paste operation?

Cells(Rows.Count, "A")End(xlUp).Offset(1) is like starting in the bottom Row, in Column A and pressing Ctrl_Up Arrow, then selecting the cell under that result

[Laurie]

I am not sure if I completely understand your incredible message. A great message at that ~ thank-you ~. I have put this Macro into my worksheet as shown here, but now I get a Syntax error.

Sub Auto_Update_All_Categories()
'
' Auto_Update_All_Categories Macro
' This will update all Category Tabs into the Master Roll-up Total Tab
'
'With Sheets("Master List")
    Sheets("Warehouse Demo").Range("A1").CurrentRegion.Copy.Cells(Rows.Count,"A")End(xlUp).Offset(1)
    Sheets("Contam Soil").Range("A1").CurrentRegion.Copy.Cells(Rows.Count,"A")End(xlUp).Offset(1)
    Sheets("Mobilization-Rig Move").Range("A1").CurrentRegion.Copy.Cells(Rows.Count,"A")End(xlUp).Offset(1)
    Sheets("General Drilling Operations").Range("A1").CurrentRegion.Copy.Cells(Rows.Count,"A")End(xlUp).Offset(1)
    Sheets("Surface Hole Operations").Range("A1").CurrentRegion.Copy.Cells(Rows.Count,"A")End(xlUp).Offset(1)
    Sheets("Intermediate Hole Operations").Range("A1").CurrentRegion.Copy.Cells(Rows.Count,"A")End(xlUp).Offset(1)
    Sheets("Production Hole Operations").Range("A1").CurrentRegion.Copy.Cells(Rows.Count,"A")End(xlUp).Offset(1)
    Application.CutCopyMode = False
    
EndWith
       
End Sub

[SamT]

Looks like a Copy and Paste error. I don't see any space between the "Copy" and the ".Cells".
Don't see one between "End" and "With"
With Sheets("Master List") is a Code line, not a comment.

Generally, when you copy code from a forum Code Block and paste it directly to the VBE, (Visual Basic Editor,) it pastes correctly, but I have noticed that a lot of our guests seem to C&P in some different way that removes some required spaces. "dunno:

BTW,the # Icon in the Post Editor Menu inserts Code Formatting Tags for you. You can select the Code, then click the icon, or click the icon, then paste the code between the Code Formatting Tags. You can also manually type them where needed, but I never do. Have I mentioned that I am a lazy typist?

Please use Code Formatting Tags in the future. Thanks

[Laurie]

I'm so frustrated. I have read and read the Macro I've re-built. I've determined what exactly must be copied & where exactly it is suppose to be pasted at, & provided the rule of moving to the last row in the Master Sheet. Why will this not work?

Sub Auto_Update_All_Categories()
'
' Auto_Update_All_Categories Macro
' This will update all Category Tabs into the Master Roll-up Total Tab
'
With Sheets("Master List")
    Sheets("Warehouse Demo").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Range("A3")
    Sheets("Contam Soil").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Sheets("Mobilization-Rig Move").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Sheets("General Drilling Operations").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Sheets("Surface Hole Operations").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Sheets("Intermediate Hole Operations").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Sheets("Production Hole Operations").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A")End(xlUp).Offset(1)
    Application.CutCopyMode = False
End With
       
End Sub

Moderator Edit: Added Code Formatting Tags with the # icon

[SamT]

you need the Dot before the Keyword(s) End

...List").Cells(Rows.Count, "A").End(xlUp).Off...

Laurie, you are doing great, it's just simple errors that every newbie makes. Even I often miss putting some damme dot where it is needed and I've been using VBA for 15 years.

[Laurie]

Thanks for the re-assuring confidence! But now I am getting a Run-Time error '1004' Application-defined or object-definer error

Sub Auto_Update_All_Categories()
'
' Auto_Update_All_Categories Macro
' This will update all Category Tabs into the Master Roll-up Total Tab
'
With Sheets("Master List")
    Sheets("Warehouse Demo").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Range("A3")
    Sheets("Contam Soil").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Sheets("Mobilization-Rig Move").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Sheets("General Drilling Operations").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Sheets("Surface Hole Operations").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Sheets("Intermediate Hole Operations").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Sheets("Production Hole Operations").Range("A1").CurrentRegion.Copy Dest:=Sheets("Master List").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Application.CutCopyMode = False
End With
       
End Sub

[offthelip]

you have missed out specifying the column for the destination try soemthing like this:

Sheets("Contam Soil").Range("A1").CurrentRegion.Copy .Cells(Cells(rows.Count, "A").End(xlUp).Row.Offset(1), 1)

Note this is using the .cells construct which uses the with ("master list")

[Laurie]

I'm sorry to be such a pain! Do I put this statement in once or multiple times? If so, where? If not, where? Could I ask, please write out this Macro script from beginning to end in whole for me. Am I on the write path at all? I just just just can't get it right.

[SamT]

@ off the lip
Sheets("Master List").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Is a Range. In particular, it is the first empty cell at the bottom of Column A

This is the wrong syntax
.Cells(Cells(rows.Count, "A").End(xlUp).Row.Offset(1), 1)
I think you meant
.Cells(Cells(rows.Count, "A").End(xlUp).Offset(1).Row, 1) Which also returns the first empty cell at the bottom of Column A.

@ Laurie,

... now I am getting a Run-Time error '1004' ...

Use F8 to step thru the code and tell us what line is highlighted when you get the error.

[offthelip]

Samt: Thanks for point out the error, you are doing a great job with your teaching!!

[mdmackillop]

I'm a lazier typist than Sam and to take you on to the next step I'll introduce you to an Array and a Loop. Arrays generally start counting from 0 and if you step through, you should be able to follow this code. Hovering over a value such as "Arr(i)" as you step through will show you how Array values are used.
As an underscore splits a line (so counts as one), Sam's estimate of "a dozen" lines is looking generous!

Option Explicit
Sub Auto_Update_All_Categories()
'
' Auto_Update_All_Categories Macro
' This will update all Category Tabs into the Master Roll-up Total Tab
'
    Dim Arr As Variant, i As Long
   
    Arr = Array("Warehouse Demo", _
                "Contam Soil", _
                "Mobilization-Rig Move", _
                "General Drilling Operations", _
                "Surface Hole Operations", _
                "Intermediate Hole Operations", _
                "Production Hole Operations")


    With Sheets("Master List")
        'First item
        Sheets(Arr(0)).Range("A1").CurrentRegion.Copy .Range("A3")
        'Other items
        For i = 1 To UBound(Arr)
            Sheets(Arr(i)).Range("A1").CurrentRegion.Copy .Cells(Rows.Count, "A").End(xlUp).Offset(1)
        Next i
    End With
End Sub