Copy and Save Worksheet in Workbook

JLS01

New Member
Joined
Feb 27, 2016
Messages
11
Hi,

I have the below at the moment but its just not quite what I want,

Private Sub CommandButton1_Click()

Dim sName As String
Sheets("REPORT").Copy After:=Sheets(Sheets.Count)
sName = Sheets("DATA").Range("G12")
On Error Resume Next
Do
ActiveSheet.Name = sName
If ActiveSheet.Name = sName Then Exit Do
sName = InputBox("Enter new valid worksheet name")
If sName = "" Then Exit Do
Loop

End Sub


My issue is that the next time I use this command button the sheet copied the first time is also updated with the new information, so its the same as the sheet just copied.

What I want to do is each time the sheet is copied to the end of the workbook it will reflect only what was on the main sheet at that time.


Hope this makes sense.....
 

Some videos you may like

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.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I assume the loop is intended to allow the Report sheet to be copied more than once without clicking the button again. Try this and see if it still updates your previously copied sheet.

Code:
Private Sub CommandButton1_Click()
 Dim sName As String, q As Variant
 sName = Sheets("DATA").Range("G12")
 Do
    Sheets("REPORT").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = sName
    sName = InputBox("Enter new valid worksheet name")
    If ActiveSheet.Name = sName Then Exit Sub
    If sName = "" Then Exit Sub
    q = MsgBox "Do you want to copy another sheet?", vbYesNo + vbQuestion, "CONTINUE?"
    If q = vbNo Then Exit Sub
 Loop
End Sub
 
Last edited:

JLS01

New Member
Joined
Feb 27, 2016
Messages
11
Hi JLGWhiz,

Thanks for the help, I tried you code however 'Syntax error' appeared and your line of q = MsgBox "Do you want to copy another sheet?", vbYesNo + vbQuestion, "CONTINUE?"
highlighted red.

The section of code I have was from another workbook that I copied and changed slightly to reflect what I'm trying to do with this workbook. The 'Loop' was in the old section of code so I have just left it in there. The REPORT is only to be copied when the button is used.


Thanks,
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Sorry about that. Needed parentheses when using the equal sign.
Code:
Private Sub CommandButton1_Click()
 Dim sName As String, q As Variant
 sName = Sheets("DATA").Range("G12")
 Do
    Sheets("REPORT").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = sName
    sName = InputBox("Enter new valid worksheet name")
    If ActiveSheet.Name = sName Then Exit Sub
    If sName = "" Then Exit Sub
    q = MsgBox("Do you want to copy another sheet?", vbYesNo + vbQuestion, "CONTINUE?")
    If q = vbNo Then Exit Sub
 Loop
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,053
Messages
5,599,524
Members
414,315
Latest member
Yolanda5050

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
Top