mercredi 1 avril 2015

Excel Macro - Creating new sheets, pasting stuff in -> 1004 Error

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