That code will not compile as is. You have the line
Set rgNewCell = rgLastCell.Offset(4, 0).Select

You can not both Set a rnage object and select in the same line...

Set rgNewCell = rgLastCell.Offset(4, 0)
rgNewCell.Select

As for whether your code fixes the problem I have not looked at that...
--
HTH...

Jim Thomlinson


"Joel" wrote:

Set SumBk = Workbooks.Open( _
Filename:="G:\WRO\WRO Year Summery Under$10.xls")

Dim rgLastCell As Range
Dim rgNewCell As Range
Set rgLastCell = SumBk.ActiveSheet.Range("A65536").End(xlUp)
Set rgNewCell = rgLastCell.Offset(4, 0).Select
ThisWorkbook.ActiveSheet.Range("CopyData").Copy _
Destination:=rgNewCell

Application.CutCopyMode = False
SumBk.ActiveSheet.HPageBreaks.Add Befo=rgNewCell
SumBk.Close savechanges:=True

"mathel" wrote:

Hi,

I have a macro I have been using in a workbook for about 6 months with no
problem. Last week, when someone else used the same file, the macro no
longer works properly.

In short, I have a form that once completed, the macro prints certain
worksheets, copies a specific range of one worksheet, opens another file and
the copied data is to be pasted starting in Column A, 4 lines down from the
last 'used' cell.

What is happening is the macro is not finding the last cell and simply
pasting the information where ever the file was last saved. Following is a
portion of the macro in the workbook:

Application.Goto Reference:="CopyData"
Application.CutCopyMode = False
Selection.Copy

Workbooks.Open Filename:= _
"G:\WRO\"WRO Year Summery Under$10.xls"

Dim rgLastCell As Range
Set rgLastCell = ActiveSheet.Range("A65536").End(xlUp)
ActiveCell.Offset(4, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.End(xlDown).Select
Application.CutCopyMode = False
ActiveCell.Offset(4, 0).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
ActiveWorkbook.Save
ActiveWindow.Close

Can anyone tell me what I am doing wrong?

Thanks
--
Linda