W
WayneK
Hello. I am using Excel 2000, am self-taught in using VBA, and need your
help.
For some reason, the code I have written below is not working. When I
run my
macro, the only error being flagged is the one I show below as the *
problem line*.
The run time error which comes up is 424 Object required.
The goal of this * problem line * is to gather a range of values from
a
different Workbook in a different directory and place those values
into
the original, opened Workbook.
Could you please review my code below -- especially the * problem line
* -- and
tell (show me) if my syntax is wrong. If you can suggest a simpler,
smoother way
to get the work done, I will gladly welcome it and change my code.
The code :
'Variables beginning with X represent info relative to original
Workbook
'Variables beginning with Y represent info relative to additional
opened
'up Workbook
'It is from the 2nd Workbook, the Y one, that a range of values is to
be
'drawn and placed into the original Workbook, the X Workbook
Dim XPath As String
Dim YPath As String
Dim XWb As String
Dim YWb As String
Dim X as String
Dim Y as String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'The currently opened Workbook is C:\Proj\June\TBook1.xls
'Obtain current Workbook's Path
XPath = ThisWorkbook.Path & "\"
'Obtain current Workbook's Name
XWb = ThisWorkbook.Name
X = XPath & XWb
'Determine next empty row on Sheet of current Workbook
'In this instance, NextRow evaluates to 7
'The 2nd Workbook, the Y Workbook's Path
YPath = "C:\Proj\May\"
The 2nd Workbook, the Y Workbook's Name
YWb = "TBook2.xls"
Y = YPath & YWb
'Open the 2nd Workbook, the Y Workbook
Workbooks.Open FileName:=Y
'The key focal point for both Workbooks is the Inventory Sheet
'On that Sheet, cell B11 is the starting point/cell to determine
'where the range data is to be drawn and is to be placed
'The Offset portion is used to place the new data range to its
'proper row (the enxt empty row). The Resize portion is used
'because in the area in this case is 3 rows deep and 237 columns wide.
'The next line is the * problem line *
[X(Inventory!B65536)].End(xlUp).Offset(1,0).Resize(3,237).Value_
= [Y(Inventory!B11)].Resize(3, 237).Value
'In the line above, the computer should go the very last row in
'column B, then to the next empty line above it and at that spot,
'place all the values from the Source Range of the other Workbook
'into the Target Workbook.
Application.ScreenUpdating = True
Application.DisplayAlerts = True
help.
For some reason, the code I have written below is not working. When I
run my
macro, the only error being flagged is the one I show below as the *
problem line*.
The run time error which comes up is 424 Object required.
The goal of this * problem line * is to gather a range of values from
a
different Workbook in a different directory and place those values
into
the original, opened Workbook.
Could you please review my code below -- especially the * problem line
* -- and
tell (show me) if my syntax is wrong. If you can suggest a simpler,
smoother way
to get the work done, I will gladly welcome it and change my code.
The code :
'Variables beginning with X represent info relative to original
Workbook
'Variables beginning with Y represent info relative to additional
opened
'up Workbook
'It is from the 2nd Workbook, the Y one, that a range of values is to
be
'drawn and placed into the original Workbook, the X Workbook
Dim XPath As String
Dim YPath As String
Dim XWb As String
Dim YWb As String
Dim X as String
Dim Y as String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'The currently opened Workbook is C:\Proj\June\TBook1.xls
'Obtain current Workbook's Path
XPath = ThisWorkbook.Path & "\"
'Obtain current Workbook's Name
XWb = ThisWorkbook.Name
X = XPath & XWb
'Determine next empty row on Sheet of current Workbook
'In this instance, NextRow evaluates to 7
'The 2nd Workbook, the Y Workbook's Path
YPath = "C:\Proj\May\"
The 2nd Workbook, the Y Workbook's Name
YWb = "TBook2.xls"
Y = YPath & YWb
'Open the 2nd Workbook, the Y Workbook
Workbooks.Open FileName:=Y
'The key focal point for both Workbooks is the Inventory Sheet
'On that Sheet, cell B11 is the starting point/cell to determine
'where the range data is to be drawn and is to be placed
'The Offset portion is used to place the new data range to its
'proper row (the enxt empty row). The Resize portion is used
'because in the area in this case is 3 rows deep and 237 columns wide.
'The next line is the * problem line *
[X(Inventory!B65536)].End(xlUp).Offset(1,0).Resize(3,237).Value_
= [Y(Inventory!B11)].Resize(3, 237).Value
'In the line above, the computer should go the very last row in
'column B, then to the next empty line above it and at that spot,
'place all the values from the Source Range of the other Workbook
'into the Target Workbook.
Application.ScreenUpdating = True
Application.DisplayAlerts = True
