Macro that copies and paste the sheet to another workbook works on Windows but not on Mac

[Zem32619]

Hi Excel Masters.

I need your help for a code that will work for Mac too. Currently, my code works perfectly fine for Windows but not on Mac, specfically in pasting the sheet from one workbook to another workbook. The copy code works, but I am encountering error in pasting it to another sheet. The error is higlighted at "Sheets(2).Select" line. Here's my code:

Sheets(1).Select
Cells.Select
Selection.Copy
ActiveWorkbook.Close SaveChanges:=False

'Adding new sheet and pasting the SB STR to SP STR file


'Sheets.Add After:=Sheets(2)
Sheets(2).Select
Sheets.Add After:=ActiveSheet
'ActiveSheet.Name = "SB"
sheetName = "SB"
Application.CutCopyMode = False
ActiveSheet.Name = sheetName
'ActiveSheet.Paste
Sheets(sheetName).Paste
Range("A1").Select

Looking forward for your help on this. Thank you so much.

 

[shinigamilight]

In the 4th line you are closing the workbook and in the 11th line you are selecting sheet2. When you've closed the workbook how can you select the sheet.

 

[Zem32619]

Hi shinigamilight, thanks for your reply. In the 4th line I'm closing the workbook that I copied a sheet from and in the 11th line it is selecting the sheet2 of the workbook where the copied sheet is to be pasted. This works perfectly fine on Windows, but not on Mac and the error is on the "Sheets(2).Select" line.

 

[shinigamilight]

Hi shinigamilight, thanks for your reply. In the 4th line I'm closing the workbook that I copied a sheet from and in the 11th line it is selecting the sheet2 of the workbook where the copied sheet is to be pasted. This works perfectly fine on Windows, but not on Mac and the error is on the "Sheets(2).Select" line.

You're running this code from the workbook you're copying from or from the workbook you're pasting. Because your code doesn't use workbook references it's hard to make out anything. I pasted your code in my workbook and ran it but nothing happened. You don't need select all the cells to copy the sheet, you can directly send the sheet to another workbook using this:

ActiveWorkbook.Sheets(1).Copy after:=Workbooks(2).Sheets(2)

 

[Zem32619]

Hi shinigamilight, I'm running the code on the workbook I'm pasting the sheet. Will try your suggestion and let you how it works. Really appreciate it. Thank you.