Copy worksheets (names, data and format) from saved data file to open active workbook

[cdtouchberry]

I have data in a saved desktop folder: C:\Users\NAME\Desktop\QuickFS\Data.xlsx

The "Data" file has 5 worksheets (Overview, Income Statement, Balance Sheet, Cash Flow Statement,Ratios), which I would like to copy into my active workbook (named"Data_Analysis"). My active workbook is located at: C:\Users\Name\Desktop\Data_Anlaysis.xlsx

"Data_Analysis" will be open at the time of the import, while the "Data" file will be closed. The "Data_Analysis" file has many worksheets, including 5 worksheets named identically to those found in the "Data" file(Overview, Income Statement, Balance Sheet, Cash Flow Statement, Ratios). When the data in the "Data" file changes, I would like to run the VBA and import the new data into the correct worksheets and overwrite the previous data. Because I have formulas in the "Data_Analysis" file linking to the 5 worksheets (Overview, Income Statement, Balance Sheet, Cash Flow Statement, Ratios), these sheets can't be created as new, and they must overwrite on the appropriate sheets in the "Data Analysis"workbook.

Thanks in advanced for your help.

CDT

[garyj]

Hello CDT..

I have done some code in Excel, though I wonder if there are other ways to do this. That being said, perhaps I don't understand your application. Are you trying to create a summary of columns across various tables? Have you looked into the features offered in Excel's Data tab. Look under Data/ Connections. Unless my understanding is off that should answer your problem without code.

GJ

[cdtouchberry]

Hello CDT..

I have done some code in Excel, though I wonder if there are other ways to do this. That being said, perhaps I don't understand your application. Are you trying to create a summary of columns across various tables? Have you looked into the features offered in Excel's Data tab. Look under Data/ Connections. Unless my understanding is off that should answer your problem without code.

GJ

There is, but when I am doing several of these an hour, it seems clunky and slow. Hence the need for the VBA.

[garyj]

I apologize, CDT as my strength is Access and I have only dabbled in Excel VBA. This seems like a large project, and I would do a lot of Google searching, such as this one, which you have likely done. I will have to leave this for someone else. :S
GJ

[Rob342]

CDT
This will copy 2 sheets into open workbook

Private Sub Transfer_Data()
    With GetObject("C:\Users\NAME\Desktop\QuickFS\Data.xlsx")
        .Sheets("Overview").cells(1).CurrentRegion.Copy ThisWorkbook.Sheets("Overview").cells(1)
       .Sheets("Income Statement").cells(1).CurrentRegion.Copy ThisWorkbook.Sheets("Income Statement").cells(1)
'Add sheets as req
       .Close 0
    End With
End Sub