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


--
WayneK
------------------------------------------------------------------------
WayneK's Profile: http://www.excelforum.com/member.php...o&userid=23037
View this thread: http://www.excelforum.com/showthread...hreadid=376671

Hi WayneK

Try instead:

WorkBooks(XWb).Sheets("Inventory").Range("B65536") .End(xlUp).Offset(1,0).Resize(3,237).Value_
= WorkBooks(YWb).Sheets("Inventory").Range("B11").Re size(3, 237).Value

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"WayneK" skrev i en
meddelelse ...

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


--
WayneK
------------------------------------------------------------------------
WayneK's Profile:
http://www.excelforum.com/member.php...o&userid=23037
View this thread: http://www.excelforum.com/showthread...hreadid=376671

Suggested is this:

Sub Test()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim P As String, FN As String
Dim rng1 As Range, rng2 As Range

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
P = "C:\Proj\May\"
FN = P & "TBook2.xls"
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(FN)
wb1.Activate
Set ws1 = wb1.Sheets("Inventory")
Set ws2 = wb2.Sheets("Inventory")

Set rng1 = ws1.Range("B65536").End(xlUp). _
Offset(1, 0).Resize(3, 237)
Set rng2 = ws2.Range("B11").Resize(3, 237)
rng1.Value = rng2.Value

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

Regards,
Greg

Sir,

The code below should accomplish the task. Just replace the problem
line with the code. I have documented what each line is supposed to
do. I tested it here and worked fine. Test it and let me know if it
works.

Regards

Juan Carlos


'Copy Source Data
Windows("Tbook2.xls").Activate 'Activate source workbook
Sheets("Inventory").Range("B65536").End(xlUp).Sele ct 'Select last row
in source workbook
Range(Selection, Selection.End(xlToRight)).Copy 'copy entire continuous
range begining at column B

'Paste in Destination Workbook
Windows("Tbook1.xls").Activate 'Activate destination workbook
Sheets("Inventory").Range("B65536").End(xlUp).Offs et(1, 0).Select
'Select first empty row from down up
ActiveSheet.Paste 'Paste data from source destination
Application.CutCopyMode = False 'Deactivate cut copy mode


--
cscorp
------------------------------------------------------------------------
cscorp's Profile: http://www.excelforum.com/member.php...o&userid=24015
View this thread: http://www.excelforum.com/showthread...hreadid=376671

Thank each one of you for your answering quickly with your cod
suggestions.
I will try them out soon. I am most appreciative.

Wayne

--
Wayne
-----------------------------------------------------------------------
WayneK's Profile: http://www.excelforum.com/member.php...fo&userid=2303
View this thread: http://www.excelforum.com/showthread.php?threadid=37667

Youre welcome :-)

LeoH

"WayneK" skrev i en
meddelelse ...

Thank each one of you for your answering quickly with your code
suggestions.
I will try them out soon. I am most appreciative.

WayneK