Results 1 to 9 of 9

Thread: Part of Excel sheets save into a new excel file

  1. #1
    Seeker Kanita's Avatar
    Join Date
    Sep 2020
    Posts
    7
    Articles
    0
    Excel Version
    Excel for Mac

    Cool Part of Excel sheets save into a new excel file



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Seeker jolivanes's Avatar
    Join Date
    May 2020
    Posts
    10
    Articles
    0
    Excel Version
    2007 & 2016
    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?

  3. #3
    Seeker Kanita's Avatar
    Join Date
    Sep 2020
    Posts
    7
    Articles
    0
    Excel Version
    Excel for Mac
    Quote Originally Posted by jolivanes View Post
    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?
    Yes, exactly.

  4. #4
    Seeker jolivanes's Avatar
    Join Date
    May 2020
    Posts
    10
    Articles
    0
    Excel Version
    2007 & 2016
    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)

  5. #5
    Seeker Kanita's Avatar
    Join Date
    Sep 2020
    Posts
    7
    Articles
    0
    Excel Version
    Excel for Mac
    Quote Originally Posted by jolivanes View Post
    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)
    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.

  6. #6
    Seeker Kanita's Avatar
    Join Date
    Sep 2020
    Posts
    7
    Articles
    0
    Excel Version
    Excel for Mac
    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.
    Click image for larger version. 

Name:	Screenshot 2020-10-02 at 21.43.17.jpg 
Views:	4 
Size:	92.7 KB 
ID:	10037

    I have a Mac.

  7. #7
    Seeker jolivanes's Avatar
    Join Date
    May 2020
    Posts
    10
    Articles
    0
    Excel Version
    2007 & 2016
    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.

  8. #8
    Seeker Kanita's Avatar
    Join Date
    Sep 2020
    Posts
    7
    Articles
    0
    Excel Version
    Excel for Mac
    Quote Originally Posted by jolivanes View Post
    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.

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

  9. #9
    Seeker jolivanes's Avatar
    Join Date
    May 2020
    Posts
    10
    Articles
    0
    Excel Version
    2007 & 2016
    If you just want to save a sheet, this works in on a windows machine.
    Change all references as and where required.
    Code:
    Sub Save_Copy_Of_Sheet()
    Dim wb1 As Workbook, wbNew As Workbook
    Set wb1 = ThisWorkbook
    Set wbNew = Workbooks.Add
    wb1.Sheets("Sheet1").Range("A1:J38").Copy
    With wbNew.Sheets("Sheet1").Range("A1")
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With
    wbNew.SaveAs Filename:="C:\FolderNameHere\FileNameHere.xlsm", FileFormat:=52
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •