I am trying to write a macro with VBA that will copy the data from my table in a sheet tiled "Front End" to a table on another sheet called "Raw Data". here is my code so far for the copying and pasting into the other sheet in the next empty row. I keep getting error that says "application defined or object defined error" at the copy/paste statements that have an X in place of the row name in an attempt to change the row with each loop.
any help is greatly appreciated, my main issue is i cant figure out how to get the row number in the copy/paste statement to change after each loop
Code:
Sub transfer() Dim x As Long
'set starting point at row 8
x = 8
Dim BlankFound As Boolean
'defines the sheet the data is being coppied from
Dim sourceSheet As Worksheet: Set sourceSheet = ThisWorkbook.Worksheets("Front End")
'defines the sheet the data is being pasted into
Dim destSheet As Worksheet: Set destSheet = ThisWorkbook.Worksheets("Raw Data")
Do While BlankFound = False
'selects the next row where the 1st column is empty
lMaxRows = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row
'pastes the data from the specified cells into the next empty row
destSheet.range("A" & lMaxRows + 1).Value = sourceSheet.range("C2").Value
destSheet.range("M" & lMaxRows + 1).Value = sourceSheet.range("E2").Value
destSheet.range("N" & lMaxRows + 1).Value = sourceSheet.range("E4").Value
destSheet.range("P" & lMaxRows + 1).Value = sourceSheet.range("G4").Value
destSheet.range("Q" & lMaxRows + 1).Value = sourceSheet.range("C4").Value
destSheet.range("O" & lMaxRows + 1).Value = sourceSheet.range("I3").Value
destSheet.range("B" & lMaxRows + 1).Value = sourceSheet.range("Ix").Value
destSheet.range("C" & lMaxRows + 1).Value = sourceSheet.range("Jx").Value
destSheet.range("D" & lMaxRows + 1).Value = sourceSheet.range("Kx").Value
destSheet.range("E" & lMaxRows + 1).Value = sourceSheet.range("Lx").Value
x = x + 1
If Cells(x, "K").Value = "" Then
BlankFound = True
End If
Loop
End Sub
any help is greatly appreciated, my main issue is i cant figure out how to get the row number in the copy/paste statement to change after each loop