• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with VBA coding, need another POV

cparks

cparks

Member
I have a button that when clicked, it copies to the last row of data, then pastes it into another worksheet.

My problem is it gives me an Error Message dealing with the "paste" section of the code.
And it pastes under A2, it needs to be in B2.

I just want to get rid of all the blank cells under the last row of data when pasted.

Code: 
Sub Button10_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim Last_Row As Long

Set copySheet = Worksheets("Data Entry Sheet")
Set pasteSheet = Worksheets("Main Data Sheet")

Last_Row = Range("G2").End(xlDown).Offset(1).Row
copySheet.Range("G2:M2").Copy Range("G" & Last_Row)
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Suggestions? Where did I go wrong?

Thanks.
 
cparks said:
I have a button that when clicked, it copies to the last row of data, then pastes it into another worksheet.

My problem is it gives me an Error Message dealing with the "paste" section of the code.
And it pastes under A2, it needs to be in B2.

I just want to get rid of all the blank cells under the last row of data when pasted.

Code: 
Sub Button10_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim Last_Row As Long

Set copySheet = Worksheets("Data Entry Sheet")
Set pasteSheet = Worksheets("Main Data Sheet")

Last_Row = Range("G2").End(xlDown).Offset(1).Row
copySheet.Range("G2:M2").Copy Range("G" & Last_Row)
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Suggestions? Where did I go wrong?

Thanks.
Click to expand...
Hi @cparks

Try it like this:
Code: 
Sub Button10_Click()

    Application.ScreenUpdating = False
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim Last_Row As Long
 
    Set copySheet = Worksheets("Data Entry Sheet")
    Set pasteSheet = Worksheets("Main Data Sheet")
 
'    Last_Row = Range("G2").End(xlDown).Offset(1).Row
    Last_Row = Columns("G").Cells(Rows.Count).End(xlUp).Row + 1
    copySheet.Range("G2:M2").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

The problem was that you were doing End(xldown) on G2 and you probably don't have any data after G2 so it returns an error... also you just need to input the paste destination once, either after .copy in the same line or on the line after

Let me know if you have any further questions

Regards
 
Last edited:
cparks said:
On "Main Data Sheet", it has the correct range selected, but nothing has been pasted. Almost like we took care of one problem, then made another lol
Click to expand...
Can you upload the file?
I will gladly take a look at it

Regards
 
Right on the money @PCosta87 ! You're 2/2. Again, bravo!

Sorry for the other message...I kept looking the code over and over and I accidentally typed in "G"'s instead of "C"'s. My mistake. I deleted the message but looks like it was too late.

Sorry about that. You had it all along! I can get some sleep now. Again, much appreciated.
 
cparks said:
Right on the money @PCosta87 ! You're 2/2. Again, bravo!

Sorry for the other message...I kept looking the code over and over and I accidentally typed in "G"'s instead of "C"'s. My mistake. I deleted the message but looks like it was too late.

Sorry about that. You had it all along! I can get some sleep now. Again, much appreciated.
Click to expand...
You are welcome ;)
 
cparks said:
You know what @PCosta87 . I'm not going to bother you with this. Youve done great and i found out what it was. No issues. Going to bed. Again, thanks :)
Click to expand...

Just replace:
Code: 
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).PasteSpecial xlPasteValues

with
Code: 
    pasteSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

If you need anything else just ask ;)
 
Last edited:
You must log in or register to reply here.
Back
Top