Markie Posted June 1, 2011 Posted June 1, 2011 Looking for some help here: Got a spreadsheet setup as an order form and a button on the form linked to some VB code to copy the sheet to a new tab and delete all user inputs. At the moment it renames the new Tab Sheet1(2) What I would want ideally is for the code to be such that it copies the sheet, deletes user inputs and then renames the new tab in the normal fashion or to allow the user to add a custom name themselves. Current code is: Sub Button7_Click() Sheets("Sheet1").Select Sheets("Sheet1").Copy Before:=Sheets(1) Columns("F:IV").Select Selection.EntireColumn.Hidden = True Rows("32:65536").Select Selection.EntireRow.Hidden = True Range("B3:B6").Select Selection.ClearContents Range("A11:C25").Select Selection.ClearContents Range("E11:E25").Select Selection.ClearContents Range("B30:B31").Select Selection.ClearContents End Sub Any help??
BervieJambo Posted June 1, 2011 Posted June 1, 2011 Looking for some help here: Got a spreadsheet setup as an order form and a button on the form linked to some VB code to copy the sheet to a new tab and delete all user inputs. At the moment it renames the new Tab Sheet1(2) What I would want ideally is for the code to be such that it copies the sheet, deletes user inputs and then renames the new tab in the normal fashion or to allow the user to add a custom name themselves. Current code is: Any help?? The following should help you... Sub Button7_Click() Dim iSht As Integer iSht = Sheets.Count ActiveSheet.Copy After:=Sheets(iSht) With ActiveSheet .Name = "Sheet" & iSht + 1 .Columns("F:IV").EntireColumn.Hidden = True .Rows("32:65536").EntireRow.Hidden = True .Range("B3:B6").ClearContents .Range("A11:C25").ClearContents .Range("E11:E25").ClearContents .Range("B30:B31").ClearContents End With End Sub
Markie Posted June 1, 2011 Author Posted June 1, 2011 The following should help you... Sub Button7_Click() Dim iSht As Integer iSht = Sheets.Count ActiveSheet.Copy After:=Sheets(iSht) With ActiveSheet .Name = "Sheet" & iSht + 1 .Columns("F:IV").EntireColumn.Hidden = True .Rows("32:65536").EntireRow.Hidden = True .Range("B3:B6").ClearContents .Range("A11:C25").ClearContents .Range("E11:E25").ClearContents .Range("B30:B31").ClearContents End With End Sub Excellent Cheers
Recommended Posts
Archived
This topic is now archived and is closed to further replies.