macro to copy data on a loop until a blank row is hit errors

exhatcher

New Member
Joined
Jun 7, 2016
Messages
3
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.

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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,214,958
Messages
6,122,475
Members
449,087
Latest member
RExcelSearch

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