Im having an issue with the macro below. I ONLY get a 1004 error when the block that is commented out is active.
Error Result:
- the first sheet in the array is created, header row pasted in and Formatted correctly
- Second sheet created, NO header row pasted
- Error stops script at that point -> no further sheets created
I cant figure out what the problem is, can anyone help?
Dim ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets("AGESTOCK")
Dim newSheets(1 To 4) As String
newSheets(1) = "CHEM - ALSO"
newSheets(2) = "LBS - LBLA"
newSheets(3) = "LBS - LBFG"
newSheets(4) = "Chemicals"
Dim sheetName As Variant
'Copy Header Row from ws1
ws1.Cells(1, 1).EntireRow.Copy
'Create New Worksheets
For Each sheetName In newSheets
Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = sheetName
'Paste Header Row
ActiveSheet.Cells(1, 1).Select
ActiveSheet.Paste
' Autofit Columns & Zoom & Scroll
ActiveWindow.Zoom = 90
ActiveSheet.Columns("A:Y").AutoFit
ActiveSheet.Columns("B").ColumnWidth = 60 'Description
ActiveSheet.Columns("E").ColumnWidth = 12 'WAS
ActiveSheet.Columns("F").ColumnWidth = 12 'NOW
'THIS IS THE SECTION THAT BREAKS IT
'Format WAS, NOW, AVGCOST, VALUE to CURRENCY
' ActiveSheet.Columns("E").NumberFormat = "$#,##0.00" 'WAS
' ActiveSheet.Columns("F").NumberFormat = "$#,##0.00" 'NOW
' ActiveSheet.Columns("H").NumberFormat = "$#,##0.00" 'AvgCost
' ActiveSheet.Columns("L").NumberFormat = "$#,##0.00" 'Value
Next sheetName
ws1.Activate
Aucun commentaire:
Enregistrer un commentaire