Log In
Register
Home
Active Member
November 17, 2018
Offline
Hi All,
Below is the code for Macro which opens all the excel files in the given folder and pastes the data in the Macro enabled sheet
sub openfile()
Dim spath as string
dim sfil as string
dim strname as string
dim owbk as workbook
dim twbk as workbook
dim ws as worksheet
set twbk = activeworkbook
spath = "h:\desktop
ewfoldersfil = Dir(spath & "*.xlsx")
Do while sfil <> ""
strname = spath & sfil
set owbk = workbooks.open(strname)
set ws = owbk.sheets(1)
Range("A5").select
Range(selection, selection.end(xltoright)).select
Range(selection, selection.end(xldown)).copy
twbk.sheets(1).range("A1048576").end(xlup)(2).pastespecial xlpastevalues
owbk.close false
sfil = Dir
loop
twbk.save
end sub
Can you help me to create a Button where i can save the path instead of always opening the code to choose the path and a button to run the Macro.
Thank you
VIP
Trusted Members
June 25, 2016
Offline
Hi Karthi
You can key the full path into a cell and then refer to it via the macro.
Eg. enter the full path in cell D1 like h:\desktop\
then in your code, write spath=RANGE("D1")
To create a button, from the ribbon select Insert-Shapes and select a shape of your choice.
Then right-click the shape and select Assign Macro and select the macro that you want (in your case is openfile).
Hope this helps
Sunny
1 Guest(s)
