Workbook & Worksheet referencing

[Tom Antony]

I have 2 workbooks saved at 2 different location with same number and name of worksheet. I want to copy a particular range of data from source workbook to destination workbook based on sheet name. Code is in destination workbook. Looking to extract data without opening source work book.

For example, I want to copy A10 to Y45 of sheet1 from source workbook to A10 to Y45 in sheet1 of destination workbook.

[jolivanes]

Re: "Looking to extract data without opening source work book."
May I ask why?



Sub With_Formula()
With Sheets("Sheet1").Range("A10:Y45")
.Formula = "='C:\Folder Name Of Source Workbook Here\[Name Of Source Workbook including Proper Extension Here]Sheet1'!RC"
.Value = .Value
End With
End Sub

[Tom Antony]

2951729518Dear Jolivanes,

Thanks for the script & response. There are more than 40 source workbook (which is export from another application (Revit)). I trying to avoiding opening all these workbook and save time. Data in the exported workbooks get updated. Hence want to update destination workbook time to time.

Let me reframe the question -

I have 2 workbooks saved at 2 different location with same number and name of worksheet. I want to copy a particular range of data from source workbook to destination workbook based on sheet name. Code is in destination workbook. Looking to extract data without opening source work book.

For example, I want to copy A10 to Y45 of sheet1 (Sheet name- FDB-001) from source workbook to A15 to Y50 in sheet1 (Sheet name- FDB-001) of destination workbook and so on. Following worksheet names are

FDB-002E
FDB-003
FDB-004E
FDB-005
NLP-001E
NLP-002E
UDB-001
UDB-002

[jolivanes]

You keep on saying that you want to save time by not opening workbooks. How long does it take to open a closed workbook, copy from it, paste copied data and close the opened workbook with code?

[Tom Antony]

ok. No issues. You are right. Will do that way.

[snb]

If you want to save time you can use GetObject in VBA.


Sub M_snb()
with getobject("G:\OF\source.xlsx")
sn = .sheets(1).usedrange
.close, 0
end with

thisworkbook.sheets(1).cells(rows.count,1).end(xlup).offset(1).resize(uboun d(sn),ubound(sn,2))=sn
End Sub