G
Guest
Guest
Hi, I am using the code below, pieced together using info I learned on this board, it is taking info from one sheet, by looking at the code letter in a column that the user inputs from a message box, and then adding a sheet named Code ?, Code and whatever the code letter was, it is working fine but I need help modifying the code so if the sheet is already there it will delete the old one and replace it with the new one. In the past I have used something like On Error Resume Next Sheets("Sheet Name").Delete, I thought something like Sheets("Code") & FilterCriteria .Delete would work but I can't get it to work. Thanks to all who take the time to reply.
Sub DataMove()
'will copy info from B1:G250,& add a sheet named code?"whatever the code was"
Application.ScreenUpdating = False
Dim FilterCriteria
Dim CurrentsheetName As String
Dim NewFileName As String
CurrentsheetName = ActiveSheet.Name
Range("B1:G250").Select
Selection.AutoFilter
FilterCriteria = InputBox("Enter the code letter you want")
Selection.AutoFilter field:=4, Criteria1:=FilterCriteria
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add
With ActiveSheet
'this is where the sheet is being added
'how can I delete it if it is aready there?
.Name = " Code " & FilterCriteria
End With
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1") = "Totals"
Range("E1") = "=sum(E3:E2500)"
Range("F1") = "=sum(F3:F2500)"
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Worksheets(CurrentsheetName).Activate
Selection.AutoFilter field:=1
Selection.AutoFilter
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Sub DataMove()
'will copy info from B1:G250,& add a sheet named code?"whatever the code was"
Application.ScreenUpdating = False
Dim FilterCriteria
Dim CurrentsheetName As String
Dim NewFileName As String
CurrentsheetName = ActiveSheet.Name
Range("B1:G250").Select
Selection.AutoFilter
FilterCriteria = InputBox("Enter the code letter you want")
Selection.AutoFilter field:=4, Criteria1:=FilterCriteria
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add
With ActiveSheet
'this is where the sheet is being added
'how can I delete it if it is aready there?
.Name = " Code " & FilterCriteria
End With
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1") = "Totals"
Range("E1") = "=sum(E3:E2500)"
Range("F1") = "=sum(F3:F2500)"
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
Worksheets(CurrentsheetName).Activate
Selection.AutoFilter field:=1
Selection.AutoFilter
Range("A1").Select
Application.ScreenUpdating = True
End Sub