Clearing contents of specific cells on different sheets within the same Workbook

tjamestx · Apr 24th 2014

Stops running after "Sheets("CUTTING").Select". Any help would be appreciated.

Code

Private Sub Sav_Click()
Dim Path As String
Dim FileName As String
Path = "C:\Users\TOM\Documents\Company Files\RSC\Orders\"
FileName = Range("C3")
ActiveWorkbook.SaveAs FileName:=Path & FileName & ".xlsx", FileFormat:=51
Sheets("VO").Select
Range("B19:D27").Select
Selection.ClearContents
Range("B13:D13").Select
Selection.ClearContents
Range("C9").Select
Selection.ClearContents
Range("C7").Select
Selection.ClearContents
Range("C5").Select
Selection.ClearContents
Sheets("CUTTING").Select
Range("A1:A5").Select
Selection.ClearContents
End Sub

Display More

stildawn · Apr 24th 2014

Re: Clearing contents of specific cells on different sheets within the same Workbook

You should go through and remove all the .select lines and just merge the first parts of the .select lines with the last part of the following Selection. lines

For example:

Yours:

Code

Sheets("VO").Select
Range("B19:D27").Select
Selection.ClearContents

Is better as:

Code

Sheets("VO").Range("B19:D27").ClearContents

If its stopping on Sheets("Cutting").Select, the sheet of that name either doesnt exist or is hidden I believe

Smallman · Apr 24th 2014

Re: Clearing contents of specific cells on different sheets within the same Workbook

Just to add to the point already made and to really start cleaning your code the following should do what you want.

Code

Private Sub Sav_Click()
Const Path = "C:\Users\TOM\Documents\Company Files\RSC\Orders\"


    ActiveWorkbook.SaveAs Filename:=Path & [C3] & ".xlsx"
    Union([B19:D27], [B13:D13], [C5], [C7], [C9]).ClearContents
    Sheets("Cutting").[A1:A5].ClearContents
End Sub

Take care

Smallman

Clearing contents of specific cells on different sheets within the same Workbook

Participate now!

Share