0

I used this example, as follows, from Microsoft Docs website and received the message Sheets.Add returns a 1004 Method 'Add' of object 'Sheets' failed.

Dim sheet As Worksheet
Set sheet = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))

Please advise solution.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
SnowMonkey
  • 1
  • 1

2 Answers2

0
  1. I wouldn't use 'sheet' as a variable name
  2. After:= requires a sheet object, not a number

Could be

Set sheet = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets(1)))

Sorry if errors in code, written on my phone.

T.M.
  • 9,436
  • 3
  • 33
  • 57
iDevlop
  • 24,841
  • 11
  • 90
  • 149
0

Sheet is reserved keyword. So, try another name using below sub.

Sub addsh()
    Dim wb As Workbook
    Dim sh As Worksheet
    Set wb = ThisWorkbook
    Set sh = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    
    Set sh = Nothing
    Set wb = Nothing
End Sub

To add a sheet with name use below sub.

Sub addsh()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = "MyNewSheet"
End Sub

See this answer from @Siddharth

T.M.
  • 9,436
  • 3
  • 33
  • 57
Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Created and ran 'Sub addsh() as advised. No 1004 error but sheet "MyNewSheet" was not created in the active workbook. – SnowMonkey Mar 28 '21 at 10:09
  • Strange behavior. Can you test codes to a new workbook? – Harun24hr Mar 28 '21 at 10:12
  • Created new workbook and reran code with exactly the same result, i.e. no 1004 error but new sheet not created. BTW each time I run the code, I have to change the name as I am informed that 'the sheet name is already in use - use a different one" – SnowMonkey Mar 28 '21 at 11:36
  • You have to use a variable for sheet name. Otherwise run first codes to add default sheet name. – Harun24hr Mar 28 '21 at 12:01
  • OK, got it; thanks. Had code in PERSONAL.XLSB instead of ThisWorkbook; – SnowMonkey Mar 28 '21 at 20:19
  • Sheet is not a reserved word, any more than Cell is. – Tim Williams Mar 28 '21 at 20:55
  • Have now resolved the code location issue and the code, as follows, works a treat; thank you. – SnowMonkey Mar 29 '21 at 04:35
  • Dim wb As Workbook Set wb = Workbooks(CSVFilename) wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = WorksheetFunction.Text(Now(), "m-d-yyyy h_mm_ss am/pm") ActiveSheet.Name = "Transposed" – SnowMonkey Mar 29 '21 at 04:35