Copying code causing compiling error

Lollygirlie1

New Member
Joined
Oct 5, 2006
Messages
4
Good morning,

I wrote some code today and it compiled fine in one xcel workbook. I copied it and set up the macro in a different workbook. Now it's giving me the following error:

Argument not optional

and pointing at my variable to determine the last row used in the tab. What did I do wrong? It's the SAME CODE!

Here is the entire code:

Sub UpdateFinal()
'
' UpdateFinal Macro
' Written 10/5/2006 to update and sort master list
'
Sheets("Master").Select
'Selects Master list
LastRow = ActiveSheet.Cells(65536, 1).End(xlUp).Row
Range("A3", ActiveSheet.Cells(LastRow, 26)).Select
Selection.Delete
'Clears all data from "Master" tab
Sheets("FoHF").Select
LastRow = ActiveSheet.Cells(65536, 1).End(xlUp).Row
Range("A3", ActiveSheet.Cells(LastRow, 26)).Select
Selection.Copy
'Copies current information from "FoHF" tab
Sheets("Master").Select
ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
'Pastes to "Master" tab
Sheets("RA").Select
Application.CutCopyMode = False
LastRow = ActiveSheet.Cells(65536, 1).End(xlUp).Row
Range("A3", ActiveSheet.Cells(LastRow, 26)).Select
Selection.Copy
'Copies current information from "RA" tab
Sheets("Master").Select
ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
'Pastes to "Master" tab
Sheets("RE").Select
Application.CutCopyMode = False
LastRow = ActiveSheet.Cells(65536, 1).End(xlUp).Row
Range("A3", ActiveSheet.Cells(LastRow, 26)).Select
Selection.Copy
'Copies current information from "RE" tab
Sheets("Master").Select
ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
'Pastes to "Master" tab
Sheets("PE").Select
Application.CutCopyMode = False
LastRow = ActiveSheet.Cells(65536, 1).End(xlUp).Row
Range("A3", ActiveSheet.Cells(LastRow, 26)).Select
Selection.Copy
'Copies current information from "PE" tab
Sheets("Master").Select
ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
'Pastes to "Master" tab
Rows("3:65536").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Sorts alphabetically by Manager Name
End Sub

HELP!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Compiles fine for me.:)

Is there any other code?

Has LastRow been declared somewhere else?
 
Upvote 0
The error indicates the line "lastRow = ..." is where the problem is.
The code was originally written in a module for a different workbook, which I then copied into a new module for the current workbook.
lastRow (lowercase l) was not defined anywhere in the original code.

I've tried writing the code over, instead of copying it from the module where it was originally written, but that didn't help.

If I try adding ("Master") to tell it which worksheet I'm selecting, it yells at me too. I'm not a programmer, and I'm at my wit's end!
 
Upvote 0
Are you saying the the variable LastRow changes case to lastRow when you paste the code into the other workbook?

If that's the case that indicates that it has been declared somewhere in the workbook you are copying to.

Try this.
Code:
Sub UpdateFinal()
Dim wsMst As Worksheet
Dim LastRow As Long
Dim I As Long
Dim arrSheets

    Set wsMst = Sheets("Master")
    
    arrSheets = Array("FoHF", "RA", "RE", "PE")
    
    With wsMst
        LastRow = .Cells(65536, 1).End(xlUp).Row
        .Range("A3", .Cells(LastRow, 26)).Delete
    End With
    
    For I = LBound(arrSheets) To UBound(arrSheets)
        With Worksheets(arrSheets(I))
            LastRow = .Cells(65536, 1).End(xlUp).Row
            .Range("A3", .Cells(LastRow, 26)).Copy wsMst.Range("A1").End(xlDown).Offset(1, 0).Select
        End With
    Next I
    
    With wsMst
        .Rows("3:65536").Sort Key1:=.Range("C3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    End With

End Sub
 
Upvote 0
Technically, lastRow is being changed to LastRow when I paste the code.

I copied your code and it is arguing with it as well.
'Run time error 1004'
Application-defined or object-defined error

The debugger highlights ".Range("A3", .Cells(LastRow, 26)).Copy wsMst.Range("A1").End(xlDown).Offset(1, 0).Select "

What should I try now?
 
Upvote 0
Remove the Select, my mistake.:)
Code:
The debugger highlights ".Range("A3", .Cells(LastRow, 26)).Copy wsMst.Range("A1").End(xlDown).Offset(1, 0)"
Note however LastRow/lastRow is being changed, if it's being changed it indicates that a variable called LastRow (or whatever) has been declated somewhere.
 
Upvote 0
I got it to work! I figured (with the help that had been posted) that it was a problem with the name "lastRow" as opposed to anything else. I declared a variable "lr" as a long, and replaced "lastRow" with "lr" and it works! Hooray! THANKS SO MUCH!
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top