Jarlisle
07-31-2013, 09:00 AM
I have a document that I want to copy the values and formats in certain columns and take that information and post to a new sheet. I want the macro to ask for the new sheet name, but if the name given is already in the workbook then I want to prompt for a new name.
Here's what I have so far, but I can't figure out how to check the sheet names and prompt for a new one if the given one exists.
Application.ScreenUpdating = False
Dim NewName As String
Sheets("Exec Summary").Select
Columns("A:F").Copy
Sheets.Add Before:=ActiveSheet
NewName = InputBox("What date is this sheet for? (Use period to separate months, days & 2 digit years)")
ActiveSheet.Name = NewName
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.ScreenUpdating = True
End Sub
Here's what I have so far, but I can't figure out how to check the sheet names and prompt for a new one if the given one exists.
Application.ScreenUpdating = False
Dim NewName As String
Sheets("Exec Summary").Select
Columns("A:F").Copy
Sheets.Add Before:=ActiveSheet
NewName = InputBox("What date is this sheet for? (Use period to separate months, days & 2 digit years)")
ActiveSheet.Name = NewName
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.ScreenUpdating = True
End Sub