copying and pasting from sheet1 to sheet 'New' with number format

[Patriot2879]

HI, good morning, I have the code below where I am trying to copy data from sheet1 to New, but its not working I am trying to copy C2 to last row in Sheet1 and paste into 'New' from B2 to last row and do the number format . please can you help?

Private Sub CommandButton3_Click()
    Worksheets("Sheet1").Range("C2", Range("C2").End(xlDown)).Copy Worksheets("New").Range("B2")
    Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"
End Sub

 

[Patriot2879]

Hi I have also tried the below but that didn't work either, hope you can help.

Private Sub CommandButton3_Click()
    Worksheets("Sheet1").Range("C2", Range("C2").End(xlDown)).Copy Worksheets("New").Range("B2")
    Worksheet("New").Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"
End Sub

 

[Patriot2879]

Hi good afternoon, the error I get is 'Run time error 1004 -Application defined or object defined error' hope you can help me please?

Private Sub CommandButton3_Click()
    Worksheets("Sheet1").Range("C2", Range("C2").End(xlDown)).Copy Worksheets("New").Range("B2")
    Worksheets("New").Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"
End Sub

 

[Patriot2879]

I have also tried the below but getting the same error

Private Sub CommandButton3_Click()
    Sheets("Sheet1").Range("C2", Range("C2").End(xlDown)).Copy Sheets("New").Range("B2")
    Sheets("New").Range("B2", Range("B2").End(xlDown)).NumberFormat = "0"
End Sub

 

[Patriot2879]

Hi any ideas on how I can fix this please? Your help will be much appreciated

 

[Fluff]

Try

Private Sub CommandButton3_Click()
   With Sheets("Sheet1")
      .Range("C2", .Range("C2").End(xlDown)).Copy Sheets("New").Range("B2")
   End With
   Sheets("New").Range("B:B").NumberFormat = "0"
End Sub

For future reference, you are not helping yourself by constantly bumping your thread

This is because of the “Zero Reply Posts” menu selection (which appears in the middle of the top menu). This list shows all recent threads with zero replies. Many users use this list to look for unanswered questions. However, once a reply is made to a thread, it falls off of this list. So for those who use this list to look for unanswered questions, they will not see any "bumped" threads.

Combine that with the fact that this is an international forum with experts all over the world logging on at different times during the day, many experts may never see your question because it is no longer on “Zero Reply Posts” list, whereas if it were not “bumped”, it would still appear on that list and they may see it.