Hi there. I'm in DIRE need of help here please.

I've to create a master excel sheet that contains various data from different excel files. The excel file comes in everyday and it contains multiple data but I only need the key data that have already been set specific cells. I've managed to write the codes for all this but now I've encountered one PROBLEM. For each data to be extracted, I keep having to click on the file based on the number of data. For example, 3 data, I've to select the file 3 times.

My question is, is there a way for me to extract all these data by selecting the file once? The FOLLOWING is the code I have.

Your help will be very much appreciated.

Code: [Select]Private Sub CommandButton1_Click()

Dim flPath As String, flName As String, frmla As String, shtName As String, i As Integer, j As Integer '>>Declaring variables

flPath = "" '>>Daily Production Log - calls the file
flName = "Production Data" '>>Master Database - Data to be extracted TO this file
shtName = "PAGE 01" '>>Current sheet containing the production data in the daily production log
frmla = Replace(shtName, "'", "''") '>>Function that replaces cell's values

With Worksheets("Daily Production") '>>Current sheet in the new file

.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Date - 1 '>>Inserts date from previous day

End With

With Worksheets("Daily Production")

.Range("B" & Rows.Count).End(xlUp).Offset(1).Formula = "='" & frmla & "'!R43"
.Range("C" & Rows.Count).End(xlUp).Offset(1).Formula = "='" & frmla & "'!R46"
.Range("D" & Rows.Count).End(xlUp).Offset(1).Formula = "='" & frmla & "'!R47"

End With


End Sub