Error 1004
Please look at my workbook. It's a very simple one.
Sometimes it works sometimes it gives me Error 1004 Invalid Parameter.
I haven't changed anything in the code. Do you know why?
If I click on cell B3 and run the macro it seems to work, if i delete sheet("chartgroupingsforchart") and rerun the macro it doesn't work any more
| Attachment | Size |
|---|---|
| Book1.xlsm | 19.09 KB |
Sheet already exists
Your error code 1004 only Comes if you *don't* delete the sheet before running the macro. If yu run the macro, delete the sheet, you can repeat this Loop as often as you like. Only if you don't delettet the sheet, Excel Errors because you are trying to create a sheet with the same Name.
replace your code with the following *quick-and-dirty* fix
Sub Macro2()
Dim R(1) As Range
Dim A As Range
Dim W As Range
Dim rownumber As Integer
Sheets("Sheet1").Activate
Set W = Sheets("Sheet1").Range("B4:B683")
rownumber = 3 + Application.Count(W)
Sheets("Sheet2").Range("A1") = rownumber
Set R(1) = Sheets("Sheet1").Range(Cells(4, 2), Cells(rownumber, 2))
Set A = Sheets("Sheet1").Range("B3")
On Error Resume Next
Application.DisplayAlerts = False
Sheets("ChartGroupsforCharting").Delete
Application.DisplayAlerts = True
On Error GoTo 0
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlAreaStacked
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="ChartGroupsforCharting"
Sheets("ChartGroupsforCharting").Select
ActiveChart.SeriesCollection(1).Name = A
ActiveChart.SeriesCollection(1).Values = R(1)
End Sub