How to use current selection as variable later

[Nightlytic]

So, found code below:

Sub CopyAll()
'sub copies the worksheets within another, closed excel file pastes them into current open file
Dim Wb1 As Workbook
Dim Wb2 As Workbook
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set Wb1 = Workbooks.Open("c:\temp\Test.xls")
'need to change location above to a relative reference
Set Wb2 = ThisWorkbook
Wb1.Sheets.Copy Before:=Wb2.Sheets(6)
'modifly number in brackets in line above to change after how many sheets to paste new tabs
Wb1.Close False
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub

Works like a charm. Copies all sheets of another workbook into my current workbook. very handy at my work. But requires me to go into vba and insert the path.
My questions:
1. How can I make this so that the current selection becomes the variable used as path? For example, if I was to select range A1 which has a folder path c:\temp\Test.xls written in it, and run the code via a keyboard shortcut, it performs the operation and copies the worksheets?
2. If I had a list of paths in range A1:A6, different paths, and had buttons in B1:B6 for each line to import the path shown to the left, if clicked. Sort of like the offset argument?

 

[Hui]

Sub CopyAll()
'sub copies the worksheets within another, closed excel file pastes them into current open fileDim Wb1 As Workbook
Dim Wb2 As Workbook
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
myPath=Selection
Set Wb1 = Workbooks.Open(myPath)
'need to change location above to a relative referenceSet Wb2 = ThisWorkbook
Wb1.Sheets.Copy Before:=Wb2.Sheets(6)
'modifly number in brackets in line above to change after how many sheets to paste new tabsWb1.Close False
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub

For Q2, Why not use the Selection line above
select the cell and have a single button?

Otherwise it is typical to assign each Button a Macro

Public caller As Integer

Sub Button1()
  caller = 1
End Sub
Sub Button2()
  caller = 2
  CopyAll (caller)
End Sub
Sub Button3()
  caller = 3
  CopyAll (caller)
End Sub

Sub CopyAll(caller As Integer)
Dim Wb2 As Workbook
With Application
  .ScreenUpdating = False
  .EnableEvents = False
  .DisplayAlerts = False
End With

myPath = Range("A1").Offset(caller - 1)

Set Wb1 = Workbooks.Open(myPath)
Wb1.Sheets.Copy Before:=Wb2.Sheets(6)
With Application
  .ScreenUpdating = True
  .EnableEvents = True
  .DisplayAlerts = True
End With
End Sub

 

[Marc L]

Hi !

No button using ActiveCell.Text and event
Worksheet_BeforeDoubleClick for example …
​

 

[Nightlytic]

Th

Sub CopyAll()
'sub copies the worksheets within another, closed excel file pastes them into current open fileDim Wb1 As Workbook
Dim Wb2 As Workbook
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
myPath=Selection
Set Wb1 = Workbooks.Open(myPath)
'need to change location above to a relative referenceSet Wb2 = ThisWorkbook
Wb1.Sheets.Copy Before:=Wb2.Sheets(6)
'modifly number in brackets in line above to change after how many sheets to paste new tabsWb1.Close False
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub

For Q2, Why not use the Selection line above
select the cell and have a single button?

Otherwise it is typical to assign each Button a Macro

Public caller As Integer

Sub Button1()
  caller = 1
End Sub
Sub Button2()
  caller = 2
  CopyAll (caller)
End Sub
Sub Button3()
  caller = 3
  CopyAll (caller)
End Sub

Sub CopyAll(caller As Integer)
Dim Wb2 As Workbook
With Application
  .ScreenUpdating = False
  .EnableEvents = False
  .DisplayAlerts = False
End With

myPath = Range("A1").Offset(caller - 1)

Set Wb1 = Workbooks.Open(myPath)
Wb1.Sheets.Copy Before:=Wb2.Sheets(6)
With Application
  .ScreenUpdating = True
  .EnableEvents = True
  .DisplayAlerts = True
End With
End Sub

Thank you Hui, works great!