Macro to copy paste & close cell values from multiple files

[balaji3081]

Hi All,

I have to reconcile two sets of files in different folders on a monthly basis, currently I open each file apply filter if required and paste value in the recon file.

Takes a long time manually.

I have attached a template , where -

- Folder path is mentioned in cell C3 & O3
- File name is in col C (C7 to C11) & O (O7 to O11)
- For LOB files need to apply repective filter in each file ( mentioed in col D)
- From All LOB files need data from Cell H26 to M26 to be copied in the recon file
- From P&L file find value on Col F for Text as - "Net Expenses" and copy from Col H - M, for eg if Net Expenses in on row 26 then copy paste from cell H26 to M26

Thanks

 

[Marc L]

Hi,​

​

As a reminder the Ask an Excel Question section is for Excel features, not for VBA !​

​

It should be easier to help with the source files and accordingly the exact expected result workbook​

or are you enough confident with your Excel / VBA skills to amend any starting point VBA procedure ?​

​

 

[Marc L]

- Folder path is mentioned in cell C3 & O3

So each cell must contain only the exact path, can't work as it is …​

- File name is in col C (C7 to C11) & O (O7 to O11)

As the filename extension is missing …​

And the number of files is definitively fixed, never change ?​

- From All LOB files need data from Cell H26 to M26 to be copied in the recon file

Is it always from row #26 or is it like for P&L files ? To see below …​

- From P&L file find value on Col F for Text as - "Net Expenses" and copy from Col H - M, for eg if Net Expenses in on row 26 then copy paste from cell H26 to M26

If it is not always in row #26 so headers must contain only the column reference without any row # …​

​

• Why duplicates headers ?!​

• Headers columns references can change or can it be hardcoded ?​

​

 

[balaji3081]

So each cell must contain only the exact path, can't work as it is …The folder path changes each month, hence prefer to be called out in the excel​

As the filename extension is missing …​

And the number of files is definitively fixed, never change ? the file name is mentioned in col C and O with extention as .xls​

Is it always from row #26 or is it like for P&L files ? To see below …​

Always Row 26 for LOB files , for P&L files its basisthe text "Net Expenses"​

If it is not always in row #26 so headers must contain only the column reference without any row # …​

I am okay with that don't see any issue.​

​

• Why duplicates headers ?! - P&L & LOB File are different in many ways but the col headers are same​

• Headers columns references can change or can it be hardcoded ? In future can be changed​

​

 

[jawaharprm]

Hi,

you can try this..

>>> You have asked many times <<<
>>> use code - tags <<<

Option Explicit
Sub Main_Button1_Click()
Dim x As Workbook
Dim y As Workbook
Dim pat As Variant
Dim lr1, lr2 As Long
Dim i As Long
'x workbook is download file
'y workbook is macro file

Application.ScreenUpdating = False

Set y = ThisWorkbook
pat = Application.GetOpenFilename("Excel files (*.xls*),*.xls*", , "Browse for file containing table to be imported")
If pat = False Then Exit Sub

Set x = Workbooks.Open(pat)

With x.Sheets("sheetname")
    lr1 = .Range("DV" & .Rows.Count).End(xlUp).Row
End With

With y.Sheets("sheetname")
    lr2 = .Range("A" & .Rows.Count).End(xlUp).Row
End With

'copy and paste between 2 sheet what range u required
x.Sheets("sheetname").Range("DV5:DV" & lr1).Copy
y.Sheets("sheetname").Range("A5").PasteSpecial
Application.CutCopyMode = False

end sub

 

[Marc L]

P&L & LOB File are different in many ways but the col headers are same

No I was not stating for duplicates between both ranges but duplicates within a same range like H6 & J6 or like R6 & T6 !​

​

So if you are not 'confy' with the previous post solution​

then you should attach a correct expected result workbook according to some source files …​

​

The path for each source folder can be asked during the execution rather than grabbing them from cells.​

​

Another question : the filtering column reference in cell D6 can change or never ?​

​

 

[balaji3081]

No I was not stating for duplicates between both ranges but duplicates within a same range like H6 & J6 or like R6 & T6 ! - It was a typo please ignore​

​

So if you are not 'confy' with the previous post solution - Yet to try the solution​

then you should attach a correct expected result workbook according to some source files …​

​

The path for each source folder can be asked during the execution rather than grabbing them from cells. I am good with that option​

​

Another question : the filtering column reference in cell D6 can change or never ? it can change as per business requirement​

​

 

[balaji3081]

Hi Marc - With the above solution I can only open and copy one file at a time. Instead I need is to work in loop for all the files I have listed.

 

[Marc L]

Yes I know ! The reason why you must well read this time posts #2 & 6 as both contain the same requirement …​

​

 

[balaji3081]

I don't think I understood your earlier reply, I will ask this question in the right forum (under VBA)and with more details. Thanks

 

[vletm]

balaji3081
About Your I will ask this question in the right forum (under VBA)and with more details.
If You check Your thread's line above the Title, You'll notice that this thread has moved to VBA Macros-forum.

This is the right Forum.

 

[balaji3081]

Thanks, I have been not very active and surely creating confusion for all.

Giving it a last shot


To put it simply

I am trying to get value from 5 different files in a recon file one below the other.

The source file is = LOB1/2/3/4/5 - prefer to select the folder and the file names can be picked up from the list
Source Cell - H26 to M26 - will not change
Target file = Recon file
Target cell to paste = E - K respectively for each file

Additionally Need to apply filter in the source file col D with values mention in the recon file (Col D i.e. - Operations/finance/ Fraud.....)


Hope I am able to provide all necessary information.

Regards,
Balaji

 

[Marc L]

So 'cause of missing information and without the required workbooks I won't waste any time​

as too many red lights to launch the creation of a VBA procedure …​

​