Copy and Save Worksheet in Workbook

[JLS01]

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.....

 

[JLGWhiz]

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.

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

 

[JLS01]

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]

Sorry about that. Needed parentheses when using the equal sign.

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