+ Reply to Thread
Results 1 to 5 of 5

Save entire workbook as copy in specific folder with VBA

Hybrid View

cychua Save entire workbook as copy... 10-01-2012, 05:39 AM
patel45 Re: Save entire workbook as... 10-01-2012, 06:31 AM
cychua Re: Save entire workbook as... 10-01-2012, 07:18 AM
patel45 Re: Save entire workbook as... 10-01-2012, 08:17 AM
cychua Re: Save entire workbook as... 10-01-2012, 08:35 AM
  1. #1
    Forum Contributor
    Join Date
    08-28-2012
    Location
    Klang, Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    113

    Save entire workbook as copy in specific folder with VBA

    Hi All
    I have all my codes and it work fine. Just that i add on 2nd sheet, named "HBL".
    While i click the button on 1st sheet, i thought that excel will save my 2nd sheet too. But excel don't.

    Please assist or advise me on this. How to alter my code.
    Meantime, it is much appreciated to teach, advise and guide me, how to run VBA on a specific sheets only. (Example, clear contents from different range and sheet 2 will be different as well.

    Private Sub Workbook_Open()
    Sheets("To-do list").Protect _
    Password:="q1w2e3r4", _
    UserInterfaceOnly:=True
    End Sub
    Sub SaveScreeningWithNewName()
        Dim NewFN As Variant
        ' Copy Invoice to a new workbook
        ActiveSheet.Copy
        NewFN = "C:\Users\CYCHUA\Documents\JobScreening\" & Range("c2").Value & ".xlsx"
        ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close
        NextScreening
    End Sub
    
    
    Sub NextScreening()
        Range("C2").Value = Range("C2").Value + 1
        Range("B5:L15").ClearContents
        Range("D19:G34").ClearContents
        Range("D35:F57").ClearContents
        Range("G40:G41").ClearContents
        Range("G46:G47").ClearContents
        Range("C4").ClearContents
        Range("I1").ClearContents
    End Sub
    1. Saving 2 Sheets in new copy instead of 1 Sheet, without sheet2 - Done
    2. Clearing Sheet2 contents with VBA. Done
    3. Creating PDF File with macro button on Sheet 2 and save a copy to specific folder. (Do have the code, but not doing right)
    4. Print out "Sheet 2" (HBL) with macro button. When the form is being filled up on page 2- Sheet2(HBL), then only print out Page 1 and page 2 on Sheet2(HBL)
    Attached Files Attached Files
    Last edited by cychua; 10-01-2012 at 08:32 AM. Reason: To be more specific on problems

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Save entire workbook as copy in specific folder with VBA

    to clear contents on different range and sheet 2
    with sheets(2)
        .Range("B5:L15").ClearContents
        .Range("D19:G34").ClearContents
        .Range("D35:F57").ClearContents
    end with
    If solved remember to mark Thread as solved

  3. #3
    Forum Contributor
    Join Date
    08-28-2012
    Location
    Klang, Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    113

    Re: Save entire workbook as copy in specific folder with VBA

    Quote Originally Posted by patel45 View Post
    to clear contents on different range and sheet 2
    with sheets(2)
        .Range("B5:L15").ClearContents
        .Range("D19:G34").ClearContents
        .Range("D35:F57").ClearContents
    end with

    Hi Patel
    Thanks for your reply. Clearing contents part look good for now.
    My major issue is, my saving code is not saving include my 2nd sheet in new file copy. I am not sure what is the issue and error.
    I had tried to alter "ActiveSheet.Copy" to "ThisWorkbook.Copy" and so on. But still no luck.

    Sub SaveScreeningWithNewName()
        Dim NewFN As Variant
        ' Copy Invoice to a new workbook
        ActiveSheet.Copy
        NewFN = "C:\Users\CYCHUA\Documents\JobScreening\" & Range("c2").Value & ".xlsx"
        ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close
        NextScreening
    End Sub
    Last edited by cychua; 10-01-2012 at 07:25 AM.

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Save entire workbook as copy in specific folder with VBA

    ActiveSheet.Copy creates a workbook with ActiveSheet
    Sheets(2).Copy creates a workbook with Sheets(2)

  5. #5
    Forum Contributor
    Join Date
    08-28-2012
    Location
    Klang, Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    113

    Re: Save entire workbook as copy in specific folder with VBA

    Thanks, Patel
    I make it as below,
    Sub SaveScreeningWithNewName()
        Dim NewFN As Variant
        ' Copy Invoice to a new workbook
        Sheets(Array("Job", "HBL")).Copy
        NewFN = "C:\Users\CYCHUA\Documents\JobScreening\" & Range("c2").Value & ".xlsx"
        ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close
        NextScreening
    End Sub
    Looking forward to modify a little on protecting my copy of save file now. Is this possible to set a code through my master copy and the copy/output file will be protected?

    Private Sub Workbook_Open()
    Sheets("Job").Protect _
    Password:="q1w2e3r4", _
    UserInterfaceOnly:=True
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1