Copy 2 Sheets And Close

charlesa920 · Jan 29th 2007

Thanks to the help from this Forum and its members: I can now open a file and copy two sheets but need to close the second file...

In wkb "C*commissions database.xls" I have this code which directs the user to open the current month's commissions. And with it open I can copy two sheets (always named the same). but now I need to close the current month's workbook, so I can continue working with the data in my permenant workbook.

Code

Sub CopySheetsToHereFromCurrentdownloadxls()
'
' MoveSheetsToHereFromCurrentdownloadxls Macro
' Macro recorded 1/27/2007 by Charles
'


'   open the download for this month
'
    Application.Dialogs(xlDialogFindFile).Show


 '  lets try an Array to copy both sheets at one time
    Sheets(Array("MANUAL ADJUSTMENTS", "TRANSACTIONS")).Copy Before:=Workbooks( _
        "C* COMMISSION DATABASE.xls").Sheets(1)  
'  okay I've got the new sheets in my database workbook
'  now I need to close the download for this month
'  but the name of the file changes each month


 End Sub

Display More

Thank you for taking the time to read this, hope you can give me a suggestion. Charles

MrkFrrl · Jan 29th 2007

Re: Copy 2 Sheets And Close, variable Wbk Names

Code

Sub CopySheetsToHereFromCurrentdownloadxls()


' Macro created 1/29/2007 by Charles
Dim MyWrkbkName as string
Dim NewWrkbkName as string


MyWrkbkName = ThisWorkbook.name


'   open the download for this month
    Application.Dialogs(xlDialogFindFile).Show


 '  let's try an Array to copy both sheets at one time
    Sheets(Array("MANUAL ADJUSTMENTS", "TRANSACTIONS")).Copy Before:=Workbooks( _
        "C* COMMISSION DATABASE.xls").Sheets(1)  
'  okay I've got the new sheets in my database workbook
'  now I need to close the download for this month
' assigning variable name to new workbook
NewWrkbkName = activeworkbook.name
' checking to make sure that a new workbook has been opened
' and not been canceled by the user by comparing two variables
' so that we don't close the instance of Excel with the macro
If NewWrkbkName <> MyWrkbkName then
' turning off warning alerts
application.displayalerts=false
'Closing the active workbook
Workbooks(NewWrkbkName).Close
' turning on warning alerts
application.displayalerts=true
end if
 End Sub

Display More

charlesa920 · Jan 29th 2007

Re: Copy 2 Sheets And Close, variable Wbk Names

Yes, thank you.

This is going to work, it doesn't yet, but I'm sure it will.

After it copies the sheets to:
MyWrkbkName, MyWrkbkName is active and it does not close either

I think if we could make NewWrkbkName active, we could close it (and not save changes)

Could you suggest a line of code to activate the NewWrkbkName?

charlesa920 · Jan 29th 2007

Re: Copy 2 Sheets And Close, variable Wbk Names

WE got it working!:p I say "WE" because I could not have done it alone...Thank you so much!

right after the dialogbox I inserted this line:
NewWrkbkName = ActiveWorkbook.Name

then after I copied the sheets I inserted this line:
Workbooks(NewWrkbkName).Activate

looks like this now:

Code

Sub CopySheetsToHereFromCurrentdownloadxls()
'
' CopySheetsToHereFromCurrentdownloadxls Macro
' Macro recorded 1/27/2007 by charles
'
    Dim MyWrkbkName As String
    Dim NewWrkbkName As String
     
    MyWrkbkName = ThisWorkbook.Name
    
'   open a specific file
'
    Application.Dialogs(xlDialogFindFile).Show
'   assigning variable name to new workbook
    NewWrkbkName = ActiveWorkbook.Name


'   lets try an Array to copy both sheets at one time
    Sheets(Array("MANUAL ADJUSTMENTS", "TRANSACTIONS")).Copy Before:=Workbooks( _
        "C* COMMISSION DATABASE.xls").Sheets(1)
'   Now we need to activate the workbook with this month's data
     Workbooks(NewWrkbkName).Activate
     ' next step is to close the NewWrkbkName and save changes = false
     ' checking to make sure that a new workbook has been opened
     ' and not been canceled by the user by comparing two variables
     ' so that we don't close the instance of Excel with the macro


    If NewWrkbkName <> MyWrkbkName Then
         ' turning off warning alerts
        Application.DisplayAlerts = False
         'Closing the active workbook
        Workbooks(NewWrkbkName).Close
         ' turning on warning alerts
        Application.DisplayAlerts = True
    End If


End Sub

Display More

Copy 2 Sheets And Close

Participate now!

Share