VBA Copy and Paste - Row

AndrewGKenneth

Board Regular
Joined
Aug 6, 2018
Messages
59
Hi there,

I am having some issues with the vba formula below and any help would be appreciated. Sub test1()

Dim ws As Worksheet
Dim copyRng As Range
Dim LR As Long, LC As Long

Set ws = Worksheets("Sheet1")

With ws
LR = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
LC = .Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
Set copyRng = .Range(.Cells(1, 1), .Cells(LR, LC))
End With

copyRng.Copy
Worksheets("Sheet2").Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub


To explain my situation, I have a sheet ("Productivity") that has information added to it from a userform. Once the data is added to this sheet ("Productivity") I then need to paste the values directly to ("Productivity2"). The problem is that data is added from the userform to the sheet ("Productivity") a row at a time, however each time information is added it copies 1500 rows to productivity 2. I have worked out why this is happening because on the sheet ("Productivity") in Columns A, I, K, L, N,O, P, R, S and T I have formulas pasted down to row 1500. I only need 1 row to copy at a time once data is added, this could be down by say using column B as a reference.

Can anyone please help to amend my formula and if you require any additional info please let me know.

Thanks in advance,
Andrew
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
First of all what you have is code not a formula, anyway if you want your code based on column B then you change
Code:
LR = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
to
Code:
LR = .Columns(2).Find("*", , xlValues, , xlByRows, xlPrevious).Row

and if it is only 1 row at a time from

Code:
Set copyRng = .Range(.Cells(1, 1), .Cells(LR, LC))
to
Code:
Set copyRng = .Range(.Cells(LR, 1), .Cells(LR, LC))
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top