Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not finding last cell in range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not finding last cell in range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not finding last cell in range
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not finding last cell in range
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro not finding last cell in range
-- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Cell Outside Of Range | Excel Worksheet Functions | |||
Problems with a finding macro and copying a range of cells | Excel Programming | |||
Finding the last cell in a range | Excel Programming | |||
Finding first (end of range) empty cell | Excel Programming | |||
Finding if a cell is within a Range | Excel Programming |