• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to copy paste & close cell values from multiple files

balaji3081

Member
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
 

Attachments

Marc L

Excel Ninja
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

Excel Ninja
- 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

Member
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
 
Last edited by a moderator:
Hi,

you can try this..

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

Code:
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
 
Last edited by a moderator:

Marc L

Excel Ninja
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

Member
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

Member
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

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

balaji3081

Member
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

Excel Ninja
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.
Screenshot 2022-05-04 at 14.02.24.png
This is the right Forum.
 

balaji3081

Member
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

Excel Ninja
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 …​
 
Top