Results 1 to 6 of 6

Thread: Copy Sheets To New Workbook And Save

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14

    Copy Sheets To New Workbook And Save

    Hi Friends,

    I need VBA code for copy existing sheet(s) in to a new workbook.

    i.e. I needs to copy two sheets (Active sheet and previous to the active sheet)from a workbook and the same two sheet needs to same as a new work book In a specified location with specified name(example like "Statement as on 31stAugust 2011(current date)"
    Note:Sheet name may varies.I need to copy active and previous to the active sheet

    Plz any one can help me to get the same.

    Regards,

    Prabhu

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try this
    Code:
    Sub SaveCurrentAndPreviousSheetToNewWorkbook()
    
        Dim wbk As Workbook
        Dim wbkActive As Workbook
        Dim lngSheetsDefaultCount As Long
        
        Set wbkActive = ActiveWorkbook
        If ActiveSheet.Index < 2 Then
            MsgBox "There are no previous sheets before the current sheet!", vbInformation
            Exit Sub
        End If
        lngSheetsDefaultCount = Application.SheetsInNewWorkbook
        Application.SheetsInNewWorkbook = 2
        Set wbk = Workbooks.Add
        Application.SheetsInNewWorkbook = lngSheetsDefaultCount
        wbkActive.ActiveSheet.UsedRange.Copy wbk.Sheets(2).Cells(1)
        wbkActive.ActiveSheet.Previous.UsedRange.Copy wbk.Sheets(1).Cells(1)
        wbk.SaveAs wbkActive.Path & Application.PathSeparator & "Statement As On " & FormatDateTime(Date, vbLongDate)
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Last edited by DocAElstein; 06-11-2023 at 03:33 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14
    Hi Friend,

    Thanks it is working, A small change required.

    1) Both sheets needs to remain as the original sheets name(from where we copied)
    2) New workbook needs to save in a specified location. Example"D:\Reports"

    Plz help to amend the code.

    Regards,

    Prabhu

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Code:
    Sub SaveCurrentAndPreviousSheetToNewWorkbook()
    
        Dim wbk As Workbook
        Dim wbkActive As Workbook
        Dim lngSheetsDefaultCount As Long
        
        Set wbkActive = ActiveWorkbook
        If ActiveSheet.Index < 2 Then
            MsgBox "There are no previous sheets before the current sheet!", vbInformation
            Exit Sub
        End If
        lngSheetsDefaultCount = Application.SheetsInNewWorkbook
        Application.SheetsInNewWorkbook = 2
        Set wbk = Workbooks.Add
        Application.SheetsInNewWorkbook = lngSheetsDefaultCount
        wbkActive.ActiveSheet.UsedRange.Copy wbk.Sheets(2).Cells(1)
        wbk.Sheets(2).Name = wbkActive.ActiveSheet.Name
        wbk.Sheets(1).Name = wbkActive.ActiveSheet.Previous.Name
        wbkActive.ActiveSheet.Previous.UsedRange.Copy wbk.Sheets(1).Cells(1)
        wbk.SaveAs "D:\Reports\" & "Statement As On " & FormatDateTime(Date, vbLongDate)
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14
    Hi Friends,

    Thanks a lot for your help!

    Regards,

    Prabhu

Similar Threads

  1. How To Save Macro To Personal Workbook
    By NITIN SHETTY in forum Excel Help
    Replies: 1
    Last Post: 04-07-2013, 01:07 PM
  2. Save Workbook For Each Change Made In A Range
    By Stalker in forum Excel Help
    Replies: 4
    Last Post: 03-22-2013, 08:54 PM
  3. Replies: 1
    Last Post: 03-07-2013, 11:42 AM
  4. Replies: 2
    Last Post: 11-08-2012, 01:15 PM
  5. VBA -- Copy/Paste across sheets
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 09-21-2012, 02:07 PM

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
  •