Macro not finding last cell in range

[mathel]

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 Before:=ActiveCell
ActiveWorkbook.Save
ActiveWindow.Close

Can anyone tell me what I am doing wrong?

Thanks

 

[Tim879]

try resetting the last cell before running all your code.

Sub Reset_lastcell()
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Dim x As Long 'Attempt to fix the lastcell on the current
worksheet
x = ActiveSheet.UsedRange.Rows.Count 'see J-Walkenbach tip 73
End Sub

Also, microsoft have code that will do this on each sheet of the
workbook:
http://support.microsoft.com/kb/231007

 

[Joel]

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 Before:=rgNewCell
SumBk.Close savechanges:=True

 

[Jim Thomlinson]

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...

 

[mathel]

--
Linda

try resetting the last cell before running all your code.

Sub Reset_lastcell()
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Dim x As Long 'Attempt to fix the lastcell on the current
worksheet
x = ActiveSheet.UsedRange.Rows.Count 'see J-Walkenbach tip 73
End Sub

Also, microsoft have code that will do this on each sheet of the
workbook:
http://support.microsoft.com/kb/231007

Thank you for the links for additional information. I have resolved the
problem (or at least its working). I removed the programming in the macro on
the user form that was to find the next blank cell on opening the workbook
"G:\WRO\"WRO Year Summery Under$10.xls". In the Code of the workbook
"G:\WRO\"WRO Year Summery Under$10.xls" I input the following so that on
opening the file it automatically opens on the correct worksheet a finds the
blank cell in Column A:

Private Sub Workbook_Open()
Sheets("WRO Summary").Select
Cells(Cells.Rows.Count, "A").End(xlUp).Select
ActiveCell.Offset(4, 0).Select
End Sub