excel for mac vba to copy a sheet without command button

If the copied worksheet held a worksheet code sheet in a VBA project, that is also carried into the new workbook.

The single worksheet retains the Name and CodeName properties of the source worksheet. 'Worksheets('NewSheet').Range('A1').ListObject. The newly created workbook holds the Application.ActiveWorkbook property and contains a single worksheet. 'Alternative to ActiveSheet because worksheet name is known.

WsActive.Range('AllData').Copy Destination:=Worksheets('NewSheet').Range('A1')ĪctiveSheet.Range('A1').ListObject.Name = 'MyNewName' Worksheets.Add(After:=Worksheets(2)).Name = 'NewSheet' Set wsActive = ActiveSheet 'Assign the ActiveSheet to a worksheet variable Or see my alternative commented out code by referencing it by its name. The new sheet can now be referenced as the ActiveSheet Cell A1 is the best choice because that is where you are pasting the table so it must be part of the new table. To rename the table then reference the table from any cell which is known to be within the table.

Start by assigning the ActiveSheet to a worksheet variable and then use the worksheet variable to reference the original ActiveSheet.

The reason that the Copy and Paste does not work is because when a new worksheet is added then the new worksheet becomes the ActiveSheet and the 'Range('AllData').Copy ' code is referencing the ActiveSheet (which is 'NewSheet' and of course AllData

Excel for mac vba to copy a sheet without command button