Issue in Copying Row to a New Sheet

This appears to be a seemingly simple problem, but I cannot figure it out. Relative newbie to VBA, so any help would be appreciated.

I have a form that copies data to a worksheet. Works fine. I would like to take the last row in that dynamic worksheet and copy to yet another worksheet, copying to a specific location (cell A250).. Everything works except that I cannot get the last row to copy to the destination worksheet. The error is clearly in the last line of code, as follows:

Private Sub CopyFormData()

    Dim Last_Row1 As Long
    Dim ws1 As Worksheet, ws2 As Worksheet

    Set ws1 = Sheets("Info Data")
    Set ws2 = Sheets("Active X Controls")

' Determine the lastrow of the data to copy

    Last_Row1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
   
'Copies last row to Active X Controls, Row 250
   
    ws1.Range("A" & Last_Row1).Copy ws2.Range("A250")

End Sub

Can anyone troubleshoot this one?

Sorry, neither one of those options worked.

Originally Posted by lateniteNC1

Sorry, neither one of those options worked.

It will be much easier to understand your problem if you provide your file. This allows us to see and experiment with your data, layout, formulas, code, and possibly attach a file with a completed solution. If you are looking for formulas to produce a desired result, it helps if you create a mock-up of what you want the result to look like. Otherwise we would have to build something from scratch, trying to guess what you want it to look like.

Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window and save the post.

Hello LateniteNC1,

See if the following take on Jeff's code makes any difference:-

 ws1.Range(("A" & Last_Row1), ("Z" & Last_Row1)).Copy ws2.[A250]

If not, then supply a sample as requested by Jeff and Marc.

Cheerio,
vcoolio.

vcoolio:

Your code did the trick; thank you.

6StringJazzer:

Thank you for your suggestion; as I originally thought it a fairly simple problem (the data WAS in the last row, with all cells filled in (no blanks)) and could be de-bugged without the file. In the future, I will most assuredly take your advice.

Thanks to all who replied.

My code should have been logically equivalent to vcoolio's but I see I made a small error.

    ws1.Range(ws1.Cells(Last_Row1,"A"),ws1.Cells(Last_Row1, "Z")).Copy ws2.Range("A250")

Easy if data is really in first cell of last row :

PHP Code: 

Sub Demo1()
    With Worksheets("Info Data").UsedRange.Rows
        .Item(.Count).Cells(1).Copy Range("'Active X Controls'!A250")
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

No worries Latenite. Glad that we were able to help.

Cheerio,
vcoolio.

@ Jeff,

Sorry Jeff. I should have checked properly before posting.

Cheerio,
vcoolio.

Originally Posted by vcoolio

@ Jeff,

Sorry Jeff. I should have checked properly before posting.

Cheerio,
vcoolio.

No apology needed! It's always valuable to show more than one way to get to the same destination!

For entire row, unlike initial code, just remove from my code .Cells(1) if really last row …