Without switching (using activating/select), add a sheet to Thisworkbook not working

[JackDanIce]

Hi,

Code is several procedures/functions, too long to post and haven't quite found required answer through several searches online.

Workbook macro opens a file, extract data from opened file and then creates output sheets in the workbook macro is run from:

Sub CreateOutput(byref dic as variant)

Dim wkb as Workbook
Dim x     as Long

Set wkb = Workbooks.Open(a path)

For x = 0 to dic.Count - 1
   CreateSheet dic.Items()(x), wkbSource
Next x

wkb.Close False
Set wkb = Nothing

End Sub




Private sub CreateSheet(ByRef arr As Variant, ByRef wkbSource As Workbook)

 ThisWorkbook.Worksheets.add after:=(ThisWorkbook.Worksheets.Count)

'Other code

End Sub

Line in red: run-time error 1004 Method 'Add' of objects 'Sheets' Failed

This doesn't work either, same error:
ThisWorkbook.Worksheets.add after:=(Worksheets.Count)

When the private Sub is called, the prior opened file is active, I'm trying to avoid using Thisworkbook.Activate as shouldn't be a need for it.

What's the correct syntax please? I'm guessing the compiler isn't understanding the request as asked.

Thanks in advance,
Jack

 

[JackDanIce]

This syntax works IF I use Thisworkbook.Activate after opening source data file

ThisWorkbook.Activate
ThisWorkbook.Worksheets.Add after:=ThisWorkbook.Sheets(Worksheets.Count)

 

[RoryA]

ThisWorkbook.Worksheets.Add after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

should work without activating.

 

[JackDanIce]

Thank you Rory