VBA Copy and Paste - Row

[AndrewGKenneth]

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

 

[MARK858]

First of all what you have is code not a formula, anyway if you want your code based on column B then you change

LR = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

to

LR = .Columns(2).Find("*", , xlValues, , xlByRows, xlPrevious).Row

and if it is only 1 row at a time from

Set copyRng = .Range(.Cells(1, 1), .Cells(LR, LC))

to

Set copyRng = .Range(.Cells(LR, 1), .Cells(LR, LC))