Copy worksheet and save as new workbook on my Mac desktop

R

Roband41

New Member
Joined
Jul 26, 2015
Messages
37
Hi everyone,
After trying numerous suggested codes and always running into errors, my head is spinning.
I want to copy a worksheet and save it in a new workbook saved on my Desktop, in .xlsx format

My source workbook is ''SA NEW" - The sheet I want to copy is ''Members(2)''
I want to copy "Members(2) and save it to my Desktop under workbook name "TEST"
I use a Mac so the full path is "Macintosh HD/Users/MyName/Desktop
I use Excel for Mac version 15.13.1

Can anyone relieve my headache?:eeek: Thanks in advance.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hi Roband

try the code below replacing XXXXX with your user name (e.g. Roband):

Sub test()
' opens workbook and saves a tab as a new file to the desktop on a Mac computer

Dim wkb As Workbook

Set wkb = Workbooks.Open("MacHD:Users:XXXXX:Desktop:SA NEW.xlsx")
Sheets("Member(2)").Select
Sheets("Member(2)").Copy
Range("G30").Select
ActiveWorkbook.SaveAs Filename:="MacHD:Users:XXXXX:Desktop:TEST.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Hope this helps, works fine on my Mac

Cheers,

Nico
 
Last edited:
Upvote 0
nils7 said:
Hi Roband

try the code below replacing XXXXX with your user name (e.g. Roband):

Sub test()
' opens workbook and saves a tab as a new file to the desktop on a Mac computer

Dim wkb As Workbook

Set wkb = Workbooks.Open("MacHD:Users:XXXXX:Desktop:SA NEW.xlsx")
Sheets("Member(2)").Select
Sheets("Member(2)").Copy
Range("G30").Select
ActiveWorkbook.SaveAs Filename:="MacHD:Users:XXXXX:Desktop:TEST.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Hope this helps, works fine on my Mac

Cheers,

Nico
Click to expand...

<style> <!-- /* Font Definitions */ @font-face {font-family:"Courier New"; panose-1:2 7 3 9 2 2 5 2 4 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:-536859905 -1073711037 9 0 511 0;} @font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0; mso-font-charset:2; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:0 268435456 0 0 -2147483648 0;} @font-face {font-family:"Cambria Math"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:-536870145 1073786111 1 0 415 0;} @font-face {font-family:Menlo; panose-1:2 11 6 9 3 8 4 2 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:-436198657 -771687941 33554472 0 479 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Calibri; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-ansi-language:EN-CA;} p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Calibri; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-ansi-language:EN-CA;} p.MsoListParagraphCxSpFirst, li.MsoListParagraphCxSpFirst, div.MsoListParagraphCxSpFirst {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Calibri; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-ansi-language:EN-CA;} p.MsoListParagraphCxSpMiddle, li.MsoListParagraphCxSpMiddle, div.MsoListParagraphCxSpMiddle {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Calibri; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-ansi-language:EN-CA;} p.MsoListParagraphCxSpLast, li.MsoListParagraphCxSpLast, div.MsoListParagraphCxSpLast {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Calibri; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-ansi-language:EN-CA;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-family:Calibri; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-ansi-language:EN-CA;} @page WordSection1 {size:612.0pt 792.0pt; margin:23.0pt 58.05pt 30.4pt 72.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} /* List Definitions */ @list l0 {mso-list-id:334769235; mso-list-template-ids:1843289918;} @list l0:level1 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:36.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Symbol;} @list l0:level2 {mso-level-number-format:bullet; mso-level-text:o; mso-level-tab-stop:72.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:"Courier New"; mso-bidi-font-family:"Times New Roman";} @list l0:level3 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:108.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level4 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:144.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level5 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:180.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level6 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:216.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level7 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:252.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level8 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:288.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level9 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:324.0pt; mso-level-number-position:left; text-indent:-18.0pt; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l1 {mso-list-id:338316391; mso-list-type:hybrid; mso-list-template-ids:160211192 67698711 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l1:level1 {mso-level-number-format:alpha-lower; mso-level-text:"%1\)"; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt;} @list l1:level2 {mso-level-number-format:alpha-lower; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt;} @list l1:level3 {mso-level-number-format:roman-lower; mso-level-tab-stop:none; mso-level-number-position:right; text-indent:-9.0pt;} @list l1:level4 {mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt;} @list l1:level5 {mso-level-number-format:alpha-lower; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt;} @list l1:level6 {mso-level-number-format:roman-lower; mso-level-tab-stop:none; mso-level-number-position:right; text-indent:-9.0pt;} @list l1:level7 {mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt;} @list l1:level8 {mso-level-number-format:alpha-lower; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt;} @list l1:level9 {mso-level-number-format:roman-lower; mso-level-tab-stop:none; mso-level-number-position:right; text-indent:-9.0pt;} ol {margin-bottom:0cm;} ul {margin-bottom:0cm;} --> </style> “Hi Nico,
Thank you for your prompt response. When reading what follows, keep in mind that I am a total VBA beginner and trying to make sense of it. I am taking an online course and reading a book, but I am really at the very beginning. This will explain why I may not make much sense at times (or always).
When reviewing your code, I realized I had forgotten to mention that my sheet “Members(2)” was the result of a macro applied on sheet “Members”. The macro copied the “Members” sheet at the end of the sheets of the workbook, then performed a couple of sort operations before becoming “Members(2)”
This is then the active worksheet when I want to start the new macro (copy the “Members(2) sheet to the Desktop and save it as “Test.xlsx”
When I ran your code, I immediately got the message that it could not find the file “SA NEW.xlsm”
(In your code I had changed the path to Macintosh HD because this is how my Mac describes it, added my user name and changed the extension of “SA NEW” to .xlsm)
Because after the execution of the first macro I was in the active sheet “Members(2)” I figured I did not need the full path and modified the code accordingly.
The following became my tentative new code:
· Sub sb_Copy_Save_ActiveSheet_As_Workbook()
· Range("G30").Select
· ActiveWorkbook.SaveAs Filename:="Macintosh HD:Users:robertandres:Desktop:TEST.xlsx", _
· FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
· EndSub
When running this, I get a couple of warnings:
a) “Cannot save in the specified format if I want to preserve the macros” (I don’t)
b) “You do not have permission to save files to this location.Make sure that you have write access for this location, or select a different location”

Questions:

In regards to a) above:
Once on the desktop, whatever Excel format is used, it will be converted in a .pdf to be sent to other people. In what format should the new workbook on the desktop be to avoid the message I get?

In regards to b) above:
I do have full rights as administrator. Where do I have to make a change to be able to save the file on the desktop?

And to further complicated the macro, once the file has been saved on the desktop, I want to automatically delete the sheet “Members(2)” in the “SA NEW.xslm” workbook.
It was created only to be viewed (result of the first macro) and maybe copied to the desktop if judged necessary by way of the second macro.

Hope this is not too confusing ;)

Thanks in advance for your assistance.

Roband41”
 
Upvote 0
Hi Roband,

The following works for me:

Code: 
Sub test2()
' copies a worksheet into a new file and saves it onto desktop
    
    Dim newWB As Workbook
    Sheets("Member(2)").Select
'Add a new workbook and copy the Members sheet over
    Set newWB = Workbooks.Add
    ThisWorkbook.Sheets("Member(2)").Copy Before:=newWB.Sheets(1)

'this saves the new workbook
    With newWB
    .SaveAs Filename:="[FONT=arial][COLOR=black]Macintosh HD:Users:robertandres[/COLOR][/FONT]:Desktop:TEST.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    .Saved = True
    .Close
    End With
'Delete the no longer needed sheet in the original file
    Application.DisplayAlerts = False
    Workbooks("SA NEW.xlsm").Sheets("Member(2)").Delete
    Application.DisplayAlerts = True

End Sub

If you still have any problems with access when saving, double check that the user path (robertandres) is correct.

Hope this helps,

Nils
 
Upvote 0
nils7 said:
Hi Roband,

The following works for me:

Code: 
Sub test2()
' copies a worksheet into a new file and saves it onto desktop
    
    Dim newWB As Workbook
    Sheets("Member(2)").Select
'Add a new workbook and copy the Members sheet over
    Set newWB = Workbooks.Add
    ThisWorkbook.Sheets("Member(2)").Copy Before:=newWB.Sheets(1)

'this saves the new workbook
    With newWB
    .SaveAs Filename:="[FONT=arial][COLOR=black]Macintosh HD:Users:robertandres[/COLOR][/FONT]:Desktop:TEST.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    .Saved = True
    .Close
    End With
'Delete the no longer needed sheet in the original file
    Application.DisplayAlerts = False
    Workbooks("SA NEW.xlsm").Sheets("Member(2)").Delete
    Application.DisplayAlerts = True

End Sub

If you still have any problems with access when saving, double check that the user path (robertandres) is correct.

Hope this helps,

Nils
Click to expand...

"Thanks for your new code, however when I ran it, I got the following error:
Run Time error '9'
Subscript out of range

Clicking Debug highlighted the second line of the code : Sheets("Members(2)").Select



'Sub test2()
' copies a worksheet into a new file and saves it onto desktop

Dim newWB As Workbook
Sheets("Members(2)").Select
'Add a new workbook and copy the Members sheet over
Set newWB = Workbooks.Add
ThisWorkbook.Sheets("Members(2)").Copy Before:=newWB.Sheets(1)

'this saves the new workbook
With newWB
.SaveAs Filename:="Macintosh HD:Users:robertandres:Desktop:TEST.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
.Saved = True
.Close
End With
'Delete the no longer needed sheet in the original file
Application.DisplayAlerts = False
Workbooks("SA NEW copy.xlsm").Sheets("Members(2)").Delete
Application.DisplayAlerts = True

End Sub"
 
Last edited:
Upvote 0
Try this instead. It should work even if SA new is not active before.

Code: 
Sub test2()
' opens workbook and saves a tab as a new file to the desktop on a Mac computer
    
    Dim newWB As Workbook
    Workbooks("SA new.xlsm").Sheets("Members(2)").Activate
    Set newWB = Workbooks.Add
    ThisWorkbook.Sheets("Members(2)").Copy Before:=newWB.Sheets(1)

    With newWB
    .SaveAs Filename:="Macintosh HD:Users:robertandres:Desktop:TEST.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    .Saved = True
    .Close
    End With
    Application.DisplayAlerts = False
    Workbooks("SA NEW.xlsm").Sheets("Members(2)").Delete
    Application.DisplayAlerts = True

End Sub
 
Upvote 0
nils7 said:
Try this instead. It should work even if SA new is not active before.

Code: 
Sub test2()
' opens workbook and saves a tab as a new file to the desktop on a Mac computer
    
    Dim newWB As Workbook
    Workbooks("SA new.xlsm").Sheets("Members(2)").Activate
    Set newWB = Workbooks.Add
    ThisWorkbook.Sheets("Members(2)").Copy Before:=newWB.Sheets(1)

    With newWB
    .SaveAs Filename:="Macintosh HD:Users:robertandres:Desktop:TEST.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    .Saved = True
    .Close
    End With
    Application.DisplayAlerts = False
    Workbooks("SA NEW.xlsm").Sheets("Members(2)").Delete
    Application.DisplayAlerts = True

End Sub
Click to expand...


"Hi again. and thanks for your quasi-instant reply.
I still get the same message as before, for the second line : Workbooks("SA NEW copy.xlsm").Sheets("Members(2)").Activate

I can't understand why it would be out of range. The worksheet is open, the workbook name is correct (I renamed it as ''SA NEW copy.xlsm'' because I am working on a copy of the original file."
 
Upvote 0
Hm, that doesn't make much sense. The code works fine here. I assume you have double-checked the spelling for both file and sheet name and the file-extension?

Have you tried a really simple code to see if VBA can find the sheet?

E.g:

Code: 
Sub Msgboxoutput()
MsgBox Workbooks("SA NEW.xlsm").Sheets("Members(2)").Range("A1").Value
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA Script: Saving worksheet as CSV on MacOS
Replies
1
Views
2K
temphelp
T
P
  • Solved
writing a macro to copy an entire xlsm workbook to a users desktop as xlsx values only.
Replies
9
Views
642
Alex Blakenburg
A
A
  • Solved
Copy Entire Workbook, save as sheet name with current date, hide all sheets except one, & attach to email
Replies
2
Views
470
Amo840812
A
S
  • Solved
Run-time error '1004'
Replies
3
Views
606
Domenic
D
S
  • Solved
I need help with Module to copy data and put into a new workbook
Replies
6
Views
396
smozgur
smozgur

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top