Get Data from Second Workbook "Please Help"
 

I am trying to get data from a second file in same folde
What am I missing. Code reads as follows

CommandButton1 is in HC.xls Tab H

Private Sub CommandButton1_Click(
Dim xl As New Excel.Applicatio
Dim xlw As Excel.Workboo
Set xlw = xl.Workbooks.Open("C:\LKUP\HC_DB.XLS"
xlw.Sheets("m_data").Selec

'GET DAT
For Each CL In Worksheets("HC").Range("B1", Worksheets("HC").Range("B65536").End(xlUp).Address
If CL.Offset(1, 0).Text = Worksheets("M_DATA").Range("B2", Worksheets("M_DATA").Range("B65536").End(xlUp).Add ress)
The
With Worksheets("M_DATA").Range("B65536").End(xlUp).Off set(0, 0
.Value = CL.Valu
'LOCATION ON SHEET"HC" FROM LOCATION ON SHEET"HC_DB
.Offset(0, 1).Value = CL.Offset(0, 1).Valu
.Offset(0, 2).Value = CL.Offset(0, 4).Valu
.Offset(0, 3).Value = CL.Offset(0, 6).Valu
.Offset(0, 4).Value = CL.Offset(0, 7).Valu
.Offset(0, 5).Value = CL.Offset(0, 8).Valu
End Wit
End I
Next C

xlw.Close Fals
Set xlw = Nothin
Set xl = Nothin
End Su

Get Data from Second Workbook "Please Help"
 

JimO

What is it supposed to do? What's not working?

You shouldn't need to create a new Excel application, just open the workbook
from within the same application. Also, what is CL? It appears to be a
variable, but I don't see it defined anywhere.

If you're getting an error, post the exact text of the error message and the
error number.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"JimO" wrote in message
...
I am trying to get data from a second file in same folder
What am I missing. Code reads as follows:

CommandButton1 is in HC.xls Tab HC

Private Sub CommandButton1_Click()
Dim xl As New Excel.Application
Dim xlw As Excel.Workbook
Set xlw = xl.Workbooks.Open("C:\LKUP\HC_DB.XLS")
xlw.Sheets("m_data").Select

'GET DATA
For Each CL In Worksheets("HC").Range("B1",
Worksheets("HC").Range("B65536").End(xlUp).Address )
If CL.Offset(1, 0).Text = Worksheets("M_DATA").Range("B2",
Worksheets("M_DATA").Range("B65536").End(xlUp).Add ress) _
Then
With
Worksheets("M_DATA").Range("B65536").End(xlUp).Off set(0, 0)
.Value = CL.Value
'LOCATION ON SHEET"HC" FROM LOCATION ON
SHEET"HC_DB"
.Offset(0, 1).Value = CL.Offset(0, 1).Value
.Offset(0, 2).Value = CL.Offset(0, 4).Value
.Offset(0, 3).Value = CL.Offset(0, 6).Value
.Offset(0, 4).Value = CL.Offset(0, 7).Value
.Offset(0, 5).Value = CL.Offset(0, 8).Value
End With
End If
Next CL

xlw.Close False
Set xlw = Nothing
Set xl = Nothing
End Sub