Command button to open file explorer in Macro and store path, run the VBA|VBA & Macros|Excel Forum|My Online Training Hub

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
ewfolder

sfil = 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