In my worksheet I have a button which generates a new invoice number and clears the contents of certain fields. This is the code:
Sub PostToCommercialTracking()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Tracking Sheet")
Workbooks.Open "M:\Commercial Clients\Current Year Test.xlsx"
Set WS2 = Worksheets("Current")
' Figure out which row is the next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
' Write the important values to the Commercial Tracker
WS2.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS1.Range("D2"), WS1.Range("B1"), WS1.Range("D1"), WS1.Range("F1"))
End Sub
Sub NextJobNumber()
Range("D2").Value = Range("D2").Value + 1
Range("A4:A9,B1:B9,C32,C33,D1,D3,D4,D10,E8,G1:G13,I6,J1:J5,K1:K13").ClearContents
ActiveSheet.CheckBoxes.Value = False
Range("E18,E19,E20,E21,E22,E23,E24,E25,E26,E27,E28,E29,E30").Value = "Select"
End Sub
Sub SaveTrackingSheetWithNewName()
Dim NewFN As Variant
' Copy Tracking Sheet to a new workbook
ActiveSheet.Copy
NewFN = "M:\Commercial Clients\Commercial Booking Forms\" & Range("D2").Value & Range("B1").Value & Range("D1").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
PostToCommercialTracking
Workbooks("Current Year Test.xlsx").Close SaveChanges:=True
NextJobNumber
End Sub
The problem I have is that some of the cells are merged cells and it won't let me do this. I thought I might be able to get around it by using a named range but that doesn't work either.
Is there a way of clearing the contents of merged cells as well? The cells in question are D10, I6, C32, C33
Bookmarks