PDA

View Full Version : [SOLVED] How to write VBA code to auto insert the last row occupied into Master Sheet



Laurie
07-27-2017, 01:02 PM
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
07-27-2017, 03:36 PM
Lose all the unneccessary Active* and Select...Selection

Laurie
07-27-2017, 03:48 PM
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
07-27-2017, 03:59 PM
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
07-27-2017, 04:20 PM
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.Copydelete the
.Select
Selection Leaving only

Range("A1:R150").CopyDo 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").CopyChange 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
07-27-2017, 04:27 PM
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
07-27-2017, 05:00 PM
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
07-27-2017, 05:05 PM
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
07-27-2017, 05:25 PM
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
07-27-2017, 05:50 PM
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
07-28-2017, 07:03 AM
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
07-28-2017, 07:19 AM
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
07-28-2017, 08:21 AM
I'm so frustrated. :banghead: 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
07-28-2017, 08:39 AM
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 :banghead: and I've been using VBA for 15 years.

Laurie
07-28-2017, 08:58 AM
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
07-28-2017, 11:22 AM
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
07-28-2017, 12:06 PM
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
07-28-2017, 05:22 PM
@ 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
07-29-2017, 01:04 AM
Samt: Thanks for point out the error, you are doing a great job with your teaching!!

mdmackillop
07-30-2017, 04:28 AM
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

Laurie
07-30-2017, 05:59 AM
It Works!!!! :hi: Thank you all so much. The update Master List from multiple worksheets problem is closed.

mdmackillop
07-30-2017, 06:05 AM
You can mark this solved using Thread Tools

Laurie
07-30-2017, 06:14 AM
This can be closed :bow:
Thank-you,