Part of Excel sheets save into a new excel file

Hi there,

I have a quite a complex file. It has more sheets. Every sheet is a calculation, and only a small part of it is to be sent out to third party. That is way I set the print preview and usually we print the page (only the part I set) as pdf and send to clients. Now we need this "printed" part not as pdf but in Excel format. Is it possible to save the file into another excel file with all the sheets but only with the set part (print preview)?

Thank you!

jolivanes said:

Re: "save the file into another excel file with all the sheets but only with the set part (print preview)?"
Do you want to save a copy of your existing file but clear all the sheets with the exception of your "print preview" which should be your print range?

Click to expand...

Yes, exactly.

Do you want to keep the formulas on all sheets?
If you do, you don't want to show the formula results I assume.
So we need to know which cells on each sheet has data that is used by the formulas and can be cleared.
Maybe attach a workbook with before and after sheets. Preferably, for me anyway, in one workbook. Change personal data (names etc)

jolivanes said:

Do you want to keep the formulas on all sheets?
If you do, you don't want to show the formula results I assume.
So we need to know which cells on each sheet has data that is used by the formulas and can be cleared.
Maybe attach a workbook with before and after sheets. Preferably, for me anyway, in one workbook. Change personal data (names etc)

Click to expand...

I don't need the formula, I just need the value and formatting. The range would be columns from A to J (rows are changing depend on the complexity of the projekt).
I will send you the sheet.

This is the code I have tried, but it doesn't work:

Sub SaveAsRange()
Dim saveFile As Variant
Dim Wb As Workbook
Dim Source As Range

'Refer to the source cells in the active sheet
Set Source = Range("A1:J190")

'Ask for the filename
saveFile = Application.GetSaveAsFilename( _
"Desktop " & Format(Now, "yyyy-mm-dd hh-nn-ss"), "Excel Workbooks (*.xlsx),*.xlsx")
'Aborted?
If VarType(saveFile) = vbBoolean Then Exit Sub

'Prepare
With Application
.ScreenUpdating = False
End With

'Add a new file
Set Wb = Workbooks.Add
'Copy the source cells
Source.Copy
With Wb
'Paste a values
.Sheets(1).Range("A1").PasteSpecial xlPasteValues
'Save and close
.SaveAs saveFile
.Close
End With

'Done
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub

This is a screenshot, when I am trying to debug it.


I have a Mac.

If I remember right, saving to a folder is different with a Mac then with a windows system.
Maybe wait for someone with a Mac to pop around.
I work with a windows machine.

jolivanes said:

If I remember right, saving to a folder is different with a Mac then with a windows system.
Maybe wait for someone with a Mac to pop around.
I work with a windows machine.

Click to expand...

The problem is, only I have a Mac. All my colleagues work with Windows machine.

If you just want to save a sheet, this works in on a windows machine.
Change all references as and where required.