--
Linda
"Tim879" wrote:
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
On Oct 2, 12:13 pm, 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
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