VBA 424 Code error

[Florida1510]

Hi folks,

I'm hoping someone could assist me. I'm trying to figure out why I keep getting a 424 error on this line of code. I have spent several hours trying to troubleshoot it on my own with no luck. I'm hoping someone here might be able to assist me. I have attached a screenshot of my screen. Thank you in advance this group is awesome

Line of code with 424 error
Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)

Full set of code:
Sub RecordofInvoice()

Dim invno As Long
Dim custname As String
Dim amt As Currency
Dim dt_issue As Date
Dim term As Byte
Dim nextrec As Range


invno = Range("C3")
custname = Range("B10")
amt = Range("I36")
dt_issue = Range("C5")
term = Range("C6")

Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)

nextrec.Value = invno
nextrec.Offset(0, 1) = custname
nextrec.Offset(0, 2) = amt
nextrec.Offset(0, 3) = dt_issue
nextrec.Offset(0, 4) = dt_issue + term


End Sub


Thanks Owen

 

[davesexcel]

Is the sheet protected?, is there a sheet3?

 

[Joe4]

Any merged cells, protected cells, or hidden cells in column A on Sheet3?
Can you tell us where the data resides in column A on that sheet (which rows actually contain data)?

 

[Florida1510]

Is the sheet protected?, is there a sheet3?

Hi Dave no it's a brand new work book nothing protected.

 

[Florida1510]

Any merged cells, protected cells, or hidden cells in column A on Sheet3?
Can you tell us where the data resides in column A on that sheet (which rows actually contain data)?

Noting Merged on that tab of the spreadsheet. I can send the spreadsheet to you if you would like to see it. It's a template with no client data on it.

 

[Joe4]

I think I see the issue - you stored the code in your Personal Macro Workbook and not in the workbook you are using.
So when you say "Sheet3", I think it is trying to locate in in the Personal Macro Workbook, and it is not finding it.
I have never tried storing VBA code in the Personal Macro Workbook that spans multiple sheets before. I will need to play around with it when I get a chance.

 

[Joe4]

Noting Merged on that tab of the spreadsheet. I can send the spreadsheet to you if you would like to see it. It's a template with no client data on it.

If its a template, why is the code stored in the Personal Macro Workbook and not in the file itself?
Code in the Personal Macro Workbook is only in your computer, and will not travel with the file if other people try to use it.

 

[Florida1510]

Noting Merged on that tab of the spreadsheet. I can send the spreadsheet to you if you would like to see it. It's a template with no client data on it.

Joe,
I didn't answer all of your questions yes on the Invoice Template tab the data going in column A (PO No. is being pulled from a merged field. Not sure if that makes a difference.

If its a template, why is the code stored in the Personal Macro Workbook and not in the file itself?
Code in the Personal Macro Workbook is only in your computer, and will not travel with the file if other people try to use it.

No it's a template that I will be using to generate invoicing for my clients. The template won't be share with others.

 

[Florida1510]

I think I see the issue - you stored the code in your Personal Macro Workbook and not in the workbook you are using.
So when you say "Sheet3", I think it is trying to locate in in the Personal Macro Workbook, and it is not finding it.
I have never tried storing VBA code in the Personal Macro Workbook that spans multiple sheets before. I will need to play around with it when I get a chance.

 

[Joe4]

Still think it is best to store the VBA code for the template right in the template itself, and not in the Personal Macro Workbook (which you will lose if you get a new computer).

But this line seems to work:

Set nextrec = ActiveWorkbook.Sheets(3).Range("A1048576").End(xlUp).Offset(1, 0)