I have a workbook called "Excel Macro.xlsm". Contained within this file is a number of sheets - the only 2 to be concerned with for now is a sheet called "BCH" and one called "FJJ"
I have created a vba script from a command button which locates the "BCH" sheet and extracts the data contained within it - and then opens a new excel file called "BCH.xlsx" and imports this data into the worksheet called 'BCH'
What I would now like to do is extract the data from the FJJ sheet (also in the Excel MAcro.xlsm workbook) at the same time as the BCH data, and place the FJJ data into a new file called "FJJ.xlsx", whilst also still extracting the data fomr the BCH sheet into the BCH.xlsx workbook.
So essentially one click of the command button will export data form each sheet and put it into its own excel file.
The vba code I have so far and which extracts BCH data is below- I have tried to replicate the code using "sub procedures" but it doesnt work could someone tell me how to change this code so it extracts multiple sheets to multiple workbooks?
Private Sub CommandButton2_Click()
Procedure1
Procedure2
End Sub
Sub Procedure1()
Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbSource = Workbooks.Open("c:\Users\chris\desktop\Excel Macro.xlsm")
Set wbTarget = Workbooks.Open("c:\Users\chris\desktop\BCH.xlsx") ' Workbooks.Open(" ") ' <<< path to destination workbook
'Now, transfer values from wbSource to wbTarget:
wbTarget.Sheets("BCH1").Range("A1:E100").Value = wbSource.Sheets("BCH").Range("A1:E100").Value
wbSource.Close
End Sub
Sub Procedure2()
Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbSource = Workbooks.Open("c:\Users\chris\desktop\Excel Macro.xlsm")
Set wbTarget = Workbooks.Open("c:\Users\chris\desktop\FJJ.xlsx") ' Workbooks.Open(" ") ' <<< path to destination workbook
'Now, transfer values from wbSource to wbTarget:
wbTarget.Sheets("FJJ1").Range("A1:E100").Value = wbSource.Sheets("FJJ").Range("A1:E100").Value
wbSource.Close
End Sub
Many thanks in advance
(Im a novice to this so if poss to explain clearly that would be great
) )
Chris
I have created a vba script from a command button which locates the "BCH" sheet and extracts the data contained within it - and then opens a new excel file called "BCH.xlsx" and imports this data into the worksheet called 'BCH'
What I would now like to do is extract the data from the FJJ sheet (also in the Excel MAcro.xlsm workbook) at the same time as the BCH data, and place the FJJ data into a new file called "FJJ.xlsx", whilst also still extracting the data fomr the BCH sheet into the BCH.xlsx workbook.
So essentially one click of the command button will export data form each sheet and put it into its own excel file.
The vba code I have so far and which extracts BCH data is below- I have tried to replicate the code using "sub procedures" but it doesnt work could someone tell me how to change this code so it extracts multiple sheets to multiple workbooks?
Private Sub CommandButton2_Click()
Procedure1
Procedure2
End Sub
Sub Procedure1()
Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbSource = Workbooks.Open("c:\Users\chris\desktop\Excel Macro.xlsm")
Set wbTarget = Workbooks.Open("c:\Users\chris\desktop\BCH.xlsx") ' Workbooks.Open(" ") ' <<< path to destination workbook
'Now, transfer values from wbSource to wbTarget:
wbTarget.Sheets("BCH1").Range("A1:E100").Value = wbSource.Sheets("BCH").Range("A1:E100").Value
wbSource.Close
End Sub
Sub Procedure2()
Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbSource = Workbooks.Open("c:\Users\chris\desktop\Excel Macro.xlsm")
Set wbTarget = Workbooks.Open("c:\Users\chris\desktop\FJJ.xlsx") ' Workbooks.Open(" ") ' <<< path to destination workbook
'Now, transfer values from wbSource to wbTarget:
wbTarget.Sheets("FJJ1").Range("A1:E100").Value = wbSource.Sheets("FJJ").Range("A1:E100").Value
wbSource.Close
End Sub
Many thanks in advance
(Im a novice to this so if poss to explain clearly that would be great
) )Chris
