Copy Data and Close 1 of 2 Files

[Papi]

I am trying to run a macro that works between two files. I would like be in File A and when the macro runs to open File B then copy Columns A:CK from sheet1 (fileB), return to File A and paste those columns in the File A sheet1. Then to return to File B and close the file and return back to File A. So far I record the macro using the macro recorder and although I have recorded the above somehow I end up in File B as it did not close.

Also, what can a person type in a macro that when asked if a person wants to save a file before closing, how to write a bit of code to accept "No" so the file closes without seeing that window and the file closes without saving that file?

 

[texasalynn]

post the code you have

 

[Papi]

Thanks...

Sub Report ()
'
' Report Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Workbooks.Open Filename:= _
"Y:\Vehicles\Maintenance.xls"
Application.Goto Reference:="R1C1"
Application.Run "'Report.xlsm'!Show_ALL"
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Report.xlsm").Activate
Application.Goto Reference:="R2C1"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Select
ActiveWindow.ActivateNext
ActiveWorkbook.Close
ActiveCell.Select
End Sub


Note that The Open file is 2010 and the closed file is 2003. I am not sur if the column length is causing the problem or if it is just me!

 

[texasalynn]

try the changes I made marked bold - note this is untested

Sub Report()
'
' Report Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Workbooks.Open Filename:= _
    "Y:\Vehicles\Maintenance.xls"
[B]x = ActiveWorkbook.Name[/B]
Application.Goto Reference:="R1C1"
Application.Run "'Report.xlsm'!Show_ALL"
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Report.xlsm").Activate
Application.Goto Reference:="R2C1"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Select
ActiveWindow.ActivateNext
[B]Workbooks(x).Close False[/B]
ActiveCell.Select
End Sub

 

[Papi]

Hello texasalynn,

Thanks for your help. The code works fine but this is odd. Once I assign a shortcut key i.e. Ctrl + Shift + R it does the same as my previous macro and stops inside of book2. I checked to ensure there was no confilicting shortcut keys and is will not work unless I simply go to macros and click RUN.

Soemting else, if the other staff remove rows i.e. say they had 500 to start with and removed 30 then when the macro runs it leaves the old 30 in the original file and simply copies down to 470 (leaving the other 30 in place). Any ideas on these two issues?