Copy Data From Dynamic Column To Another Dynamic Column in the Same Workbook-CodePudding

I have a workbook with dynamic log sheet that performs calculations on data entered by the user. I would like specific dynamic columns copied from this log sheet to another sheet in the workbook for graphing purposes. This copy would only be for values and mainly is done to make it easier to run a final macro for producing a XY scatter plot. However, I am getting an object error and am not sure why this is happening. Thank you in advance for any and all help. Could you please help me figure out the best way to accomplish this task? Here is my current VBA:

Sub UpdateCharts()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim sourcelRow As Long
Dim targetlRow As Long


Set sourceSheet = ThisWorkbook.Worksheets("Inventory Log")
Set targetSheet = ThisWorkbook.Worksheets("Tables")
sourcelRow = sourceSheet.Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Row
targetlRow = targetSheet.Cells(Row.Count, 6).End(xlDown).Offset(1, 0).Row

sourceSheet.Cells(sourcelRow, 1).Copy
targetSheet.Cells(targetSheet, 6).PasteSpecial xlPasteValues


End Sub

CodePudding user response：

There are several errors/typos in your code:

you should always use explicit referencing
Row and Rows are two different commands
to retrieve the last row you should always use the same function
you don't need to copy/paste values - you can write them directly to

You could e.g. use this function to retrieve the last row of a sheet and columnIndex:

Public Function getLastRow(ws As Worksheet, columnIndex As Long) As Long
With ws
    getLastRow = .Cells(.Rows.Count, columnIndex).End(xlUp).Row
End With
End Function

Then your code would look like this

Sub UpdateCharts()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim sourcelRow As Long
Dim targetlRow As Long

Set sourceSheet = ThisWorkbook.Worksheets("Inventory Log")
Set targetSheet = ThisWorkbook.Worksheets("Tables")


sourcelRow = getLastRow(sourceSheet, 1)
targetlRow = getLastRow(targetSheet, 6)   1 'adding 1 row to have the next empty row

targetSheet.Cells(targetlRow, 6).Value = sourceSheet.Cells(sourcelRow, 1).Value

End Sub