Copy specific data from closed file into another file

[Vipullade]

I got the below code online to copy data from a closed file to another file.

The problem being that the output, the data is copied but formatting disappears...please help

Sub CopyFromClosedWorkbook()


Dim arc As Workbook


Set arc = Workbooks.Open("C:\Users\vipul.lade\Desktop\Finance MIS Automation\Bank.xlsx")


ThisWorkbook.Activate


Worksheets("Data2").Range("A1:I50").Formula = arc.Worksheets("Data2").Range("A1:I50").Formula

 

[Marc L]

Hi !

Just use Range.Copy instead, to see in VBA inner help …​

 

[p45cal]

It depends on a few things:
whether there are formulae in the range you want to copy from
whether you're wanting to transfer the data (which is what you said on the first line of your message) and not the formula
or whether you want to copy the formula and the formatting

It could be as simple as:

arc.Worksheets("Data2").Range("A1:I50").copy Worksheets("Data2").Range("A1")

but it might be:

arc.Worksheets("Data2").Range("A1:I50").copy
Worksheets("Data2").Range("A1").PasteSpecial xlPasteFormats
Worksheets("Data2").Range("A1").PasteSpecial xlPasteValues

 

[Vipullade]

Thank you P45cal, the first one helped, i was struggling with the sheets.
I wanted to copy the data with the formats, there is no formula....
Once Again thanks, the code worked perfectly