Copying last 30 data sets from one sheet to another

[tlaltmey]

Hi All,

I'm currently trying to locate the last 30 data sets of Column "U" located in Sheet1 as well as the corresponding data located in Column "A" of the same row.

I'd like to copy this data into the most empty cells of Sheet 2 where the Column "U" data of Sheet 1 goes into Column "B" empty cells of sheet 2 and the Column "A" data of Sheet 1 goes into the Column "A" data of Sheet 2.

I've attached my current code below. Currently, this does not locate the last data set in the column based off of Column "U" of sheet1 due to my Offset(690) value I believe (Currently there are 689 data sets with one header).

Sub Plot()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

Dim mainWB As Workbook
Set mainWB = ActiveWorkbook

Set wsCopy = mainWB.Worksheets("Sheet1")
Set wsDest = mainWB.Worksheets("Sheet2")



'Finds the last used row based on Column B and places it in the last empty row of Column A in the Log File
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "U").End(xlUp).Offset(690).Row

wsDest.Range("B" & lDestLastRow).Value = wsCopy.Range("U" & lCopyLastRow).Value
wsDest.Range("A" & lDestLastRow).Value = wsCopy.Range("A" & lCopyLastRow).Value

End Sub

 

[StephenCrump]

I'm currently trying to locate the last 30 data sets of Column "U" located in Sheet1 as well as the corresponding data located in Column "A" of the same row.

Do you mean the last 30 rows? If column U is fully populated, then perhaps:

'....
N = 30

'Finds the last used row based on Column B and places it in the last empty row of Column A in the Log File
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "U").End(xlUp).Offset(1 - N).Row

wsDest.Range("B" & lDestLastRow).Resize(N).Value = wsCopy.Range("U" & lCopyLastRow).Resize(N).Value
wsDest.Range("A" & lDestLastRow).Resize(N).Value = wsCopy.Range("A" & lCopyLastRow).Resize(N).Value

 

[tlaltmey]

Hi Stephen,
Apologies, I did mean the last 30 rows of data in column U and Column A from Sheet 1 into Columns B and A respectively of Sheet 2.
Additionally, the last 30 rows will be changing dynamically. Currently there is 688 rows of data for example when tomorrow there maybe 720. Currently, the very last row of data is on line 692 row U. I would like the previous 30 data points starting at line 692 up to line 663. (although this data points may change later).

I look to be getting a run-time error from the following line of the code you provided:

lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "U").End(xlUp).Offset(1 - N).Row

 

[StephenCrump]

I look to be getting a run-time error from the following line of the code you provided:

lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "U").End(xlUp).Offset(1 - N).Row

If the last populated row in U:U is 692, and N=30 then

lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "U").End(xlUp).Offset(1 - N).Row

will return the row number 663.

A run-time error suggests that you're testing when the last populated row is less than 30? Ideally the code would include a test here to make sure we do have 30 rows to copy.