Excel Help

[Markie]

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]

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]

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