Click here to Skip to main content
14,699,609 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi. I am trying to write a macro (for the first time ever) to move data from one sheet to the other in excel so that I can populate a table.

This is my macro so far. When I try to run it it tells me "error 1004: application defined or object defined error" what am i doing wrong?

'
' Movedata Macro
'
Sub SubMoveData()

'
    Dim SrcSht       As Worksheet
    Dim DestSht      As Worksheet
    Dim lngDestLrow  As Long
    
    'Define Worksheets
    Set SrcSht = Sheets("Measures Worksheet")
    Set DestSht = Sheets("RS - Measures Data Compiled")
    
    'Define Destination Sheet Lrow
    lngDestLrow = DestSht.Cells(Rows.Count, "B7").End(xlUp).Row
    
    'Move Data
    DestSht.Cells(lngDestLrow + 1, "B7") = SrcSht.Range("C14") 'Enter the form Field B7 on the next available row
    DestSht.Cells(lngDestLrow + 1, "C7") = SrcSht.Range("K14") 'Enter the form Field C7 on the next available row
    DestSht.Cells(lngDestLrow + 1, "D7") = SrcSht.Range("L14") 'Enter the form Field D7 on the next available row
    DestSht.Cells(lngDestLrow + 1, "E7") = SrcSht.Range("M14") 'Enter the form Field E7 on the next available row
   
        
End Sub


What I have tried:

I've tried to change the info in
lngDestLrow = DestSht.Cells(Rows.Count, "B7").End(xlUp).Row
but nothing is working.
Posted
Updated 27-Nov-19 10:31am
Comments
ZurdoDev 21-Nov-19 14:24pm
   
No idea what the problem is. How would we? You did not tell us what line of code causes the error. The error you got is a generic one.
biologisttryingtocode 21-Nov-19 14:27pm
   
My bad. It's telling me this line is causing the error: lngDestLrow = DestSht.Cells(Rows.Count, "B7").End(xlUp).Row

it gets highlighted whenever I try to run the code
Richard MacCutchan 22-Nov-19 3:56am
   
I do not think that the Rows object has a property called Count.
biologisttryingtocode 22-Nov-19 10:17am
   
It is - it's worked in the past. Do you have any suggestions for how to fix this problem?

1 solution

Try to change this:
lngDestLrow = DestSht.Cells(Rows.Count, "B7").End(xlUp).Row

to this:
lngDestLrow = DestSht.Range("B" & Rows.Count).End(xlUp).Row + 1


Change the code accordingly to this part:
'Move Data
'DestSht.Cells(lngDestLrow + 1, "B7") = SrcSht.Range("C14") 'Enter the form Field B7 on the next available row
DestSht.Range("B" & lngDestLrow) = SrcSht.Range("C14") 'Enter the form Field B7 on the next available row
'and so on....


Let me know if it help.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900