I have a variable number of XLSX workbooks (product1, product2 ..... productX) from which I import data into Master.xlsm
I want to import from a closed workbook a certain range of cells 'B1: M19', which is in the same workbook in each workbook.
But I want to paste one below the other
My idea is to use a VBA macro for each workbook. So, if I have new workbooks, I add one VBA macro more.
- The first VBA macro works and copies the import range 'B1: M19' from the closed workbook to the active workbook 'Master.xlsm' in the 'Base' sheet into the cell range 'B1: M19'
- The other VBA macro doesn't work? This VBA macro should copy the range 'B2: M19' from the closed workbook to the active workbook 'Master.xlsm' in the 'Base' sheet to the cell range 'B20: M37'.
Can anyone tell why second VBA macro is not working and what lines of VBA code should be edited?
This is the VBA I use (I'm not sure how to create a VBA differently)
Plesae see attached workbooks (xlsx format).
I want to import from a closed workbook a certain range of cells 'B1: M19', which is in the same workbook in each workbook.
But I want to paste one below the other
My idea is to use a VBA macro for each workbook. So, if I have new workbooks, I add one VBA macro more.
- The first VBA macro works and copies the import range 'B1: M19' from the closed workbook to the active workbook 'Master.xlsm' in the 'Base' sheet into the cell range 'B1: M19'
- The other VBA macro doesn't work? This VBA macro should copy the range 'B2: M19' from the closed workbook to the active workbook 'Master.xlsm' in the 'Base' sheet to the cell range 'B20: M37'.
Can anyone tell why second VBA macro is not working and what lines of VBA code should be edited?
This is the VBA I use (I'm not sure how to create a VBA differently)
Code:
Option Explicit
Sub Start()
Call product1
Call product2
End Sub
Sub product1()
Dim mydata As String
Dim sPath As String
Dim sFileName As String
Dim sSheetName As String
Dim sRange As String
sPath = ThisWorkbook.Path & "\"
sFileName = "product1.xlsx"
sSheetName = "view"
sRange = "B1:M19"
mydata = "='" & sPath & "[" & sFileName & "]" & sSheetName & "'!" & sRange
With ThisWorkbook.Worksheets("base").Range("B1:M19")
.Formula = mydata
.Value = .Value
End With
End Sub
Sub product2()
Dim mydata As String
Dim sPath As String
Dim sFileName As String
Dim sSheetName As String
Dim sRange As String
sPath = ThisWorkbook.Path & "\"
sFileName = "product2.xlsx"
sSheetName = "view"
sRange = "B2:M19"
mydata = "='" & sPath & "[" & sFileName & "]" & sSheetName & "'!" & sRange
With ThisWorkbook.Worksheets("base").Range("B20:M37")
.Formula = mydata
.Value = .Value
End With
End Sub
'Sub product3() etc etc.....
Plesae see attached workbooks (xlsx format).