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

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,227
Office Version
  1. 2010
Platform
  1. Windows
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?
Code:
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
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi I have also tried the below but that didn't work either, hope you can help.
Code:
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
 
Upvote 0
Hi good afternoon, the error I get is 'Run time error 1004 -Application defined or object defined error' hope you can help me please?
Code:
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
 
Last edited:
Upvote 0
I have also tried the below but getting the same error
Code:
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
 
Upvote 0
Hi any ideas on how I can fix this please? Your help will be much appreciated
 
Upvote 0
Try
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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