Moving data using VBA from one sheet to multiple new files

[geojowett]

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