Copy/Paste Data and formatting any user PC & MAC save as Dialog box prompt

[JarrydNAR]

Hi all, Hoping someone can help here

I have a VBA Script that i have put together that Already Copy and Pastes Data and formatting that I use internally. I'm looking to re use this Script for a similar purpose for External Sales Reps

Effectively I need it to Extract a Cell Range into a new file, Paste Data, Paste Formatting and save the file. there will be multiple users some using PC some using MAC versions of Excel. i was thinking the easiest method would be to have while the Script is processing the Copy and Paste process it prompts the Save As Dialog box for the user to define where they want the file Saved

Below is the Script I am currently using for my own file

I have done a lot of googling but cant seem to make sense of the functions needed to prompt for the Save as Dialog box

all help appreciated,

Cheers


Sub CopyData()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet

Set wbI = ThisWorkbook
'~~> Set the relevant sheet from where you want to copy
Set wsI = wbI.Sheets("Sheet1")

Set wbO = Workbooks.Add

With wbO
'~~> Set the relevant sheet to where you want to paste
Set wsO = wbO.Sheets("Sheet1")

.SaveAs Filename:="T:\Templates\Contract Report Output\Book2.xls", FileFormat:=56

wsI.Range("=A:T,X:AA").Copy

wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

wsO.Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
End Sub

 

[JarrydNAR]

Bump.

Let me know if any other info is needed to assist on this one

Cheers

 

[JarrydNAR]

is anyone able to help here?

 

[Marc L]

​

Hi,​

​

use the VBA method GetSaveAsFilename like you can see in VBA help …​

 

[JarrydNAR]

​

Hi,​

​

use the VBA method GetSaveAsFilename like you can see in VBA help …​

So if i wanted to Nest this in the VBA Process i have above do i need to nest it before the copy and past functions?

 

[JarrydNAR]

So if i wanted to Nest this in the VBA Process i have above do i need to nest it before the copy and past functions?

So i got tis to work on PC but it throws a 400 Error on MAC

Is there something that can be added/changed for this function to Work on Excel on MAC users computers?



Sub ETAExtract()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet


Set wbI = ThisWorkbook
'~~> Set the relevant sheet from where you want to copy
Set wsI = wbI.Sheets("Orders by Customer")

Set wbO = Workbooks.Add

With wbO

Set wsO = wbO.Sheets("Sheet1")


Dim fname
fname = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Save As")
If fname = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fname


wsI.Range("=A:AA").Copy


wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

wsO.Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
End Sub