VBA - Simple cut and paste, I'll do the rest

erutherford

Active Member
Joined
Dec 19, 2016
Messages
390
Spent hours combing through for samples, but coming up empty

Worksheet 1 is where I fill out work orders. Worksheet 2 is a cumulative history of each of the work orders from worksheet 1.

I figured if I could get one line working properly, then adding additional line would be easy (that is why you see additional lines "noted").

"last row" seems to be where I am having issues

Below is a sample of the code I have been working with.

Private Sub CommandButton2_Click()
'Sub Paste_My_Data()
Dim LastrowA As String
'Dim LastrowB As String
'Dim LastrowC As String
'Dim LastrowD As String

'Below IDs where next blank cell is
LastrowA = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
'LastrowB = Sheets("sheet2").Cells(Rows.Count, "B").End(xlUp).Row + 1
'LastrowC = Sheets("sheet2").Cells(Rows.Count, "C").End(xlUp).Row + 1
'LastrowD = Sheets("sheet2").Cells(Rows.Count, "D").End(xlUp).Row + 1

'Below is actual assigning of data

Sheets("sheet2").Range("A2:A7" & LastrowA).Value = Sheets("sheet1").Range("A2:A7").Value
'Sheets("sheet2").Range("B2:B7" & Lastrow).Value = Sheets("sheet1").Range("B2:B10").Value
'Sheets("sheet2").Range("C2:C7" & LastrowC).Value = Sheets("sheet1").Range("C2:C10").Value
'Sheets("sheet2").Range("D2:D7" & LastrowD).Value = Sheets("sheet1").Range("D2").Value
'Sheets("sheet1").Range("A2:A10").ClearContents
'Sheets("sheet1").Range("B2:B10").ClearContents
'Sheets("sheet1").Range("C2:C10").ClearContents
'Sheets("sheet1").Range("D2:D10").ClearContents
'MsgBox "Your entry has updated"
End Sub
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Are you trying to copy one row, or a whole range of rows at a time?
Are columns A, B, C, and D "in sync" where they can be copied at the same time, or do they need to be done separately?

By the way, the way you are trying to use LastRow is not quite right.
Your issue is that you are trying to apply the last row calculation, but aren't removing the existing ending row from your reference!
Code:
[COLOR=#333333]Sheets("sheet2").Range("A2:A[/COLOR][COLOR=#ff0000]7[/COLOR][COLOR=#333333]" & LastrowA)[/COLOR]
should be:
Code:
[COLOR=#574123][COLOR=#333333]Sheets("sheet2").Range("A2:A[/COLOR][COLOR=#333333]" & LastrowA)[/COLOR][/COLOR]
Otherwise, if your LastRow was 50, "A7" & "50" makes "A750"!

But there may be other issues too, but I cannot be sure until I have a clearer picture of what your data looks like and what exactly you are trying to do.
 
Last edited:

erutherford

Active Member
Joined
Dec 19, 2016
Messages
390
Are you trying to copy one row, or a whole range of rows at a time? - A range of rows
Are columns A, B, C, and D "in sync" where they can be copied at the same time, or do they need to be done separately - They can be copied at the same time, which is preferred.

 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
So, if I am understanding you correctly, are you trying to copy Sheet1, from row A2 to D7 to Sheet2, starting in the first available row?
If so, then you can do it in one single line of code:
Code:
Sheets("sheet1").Range("A2:D7").Copy Destination:=Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
 

erutherford

Active Member
Joined
Dec 19, 2016
Messages
390
Works perfect AND much cleaner code. I don't really need any of the other identifiers.
Early Christmas for me!

thank you and safe holidays to you!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
You are welcome!
Glad I could help!:)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,488
Messages
5,601,974
Members
414,487
Latest member
inxlsplot

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