Invoices and invoice Register

[LesHunter]

Hi, I have just become involved in VBA and macros
Its a new Challenge in my retirement
I have followed to the letter the VBA code shown in you You Tube Posting [h=1]Learn Excel - Create an Invoice Register - Podcast 1808[/h]Finding it extremely helpful However when I compile it I get error messages
The part I'm having trouble with is the PostToRegister Macro
line 10 Variable not defined.
NextRow = WS3.Cells(Rows.Count, 1).End(xlUp).Row + 1
I didn't see any errors occur on the Video so may be its my version of Excel
Which is 2016

many thanks in anticipation
les hunter

 

[C with no eyes]

If it helps...

I have created many macros but not written much within VBA (although I have recently started to dabble). How? Well...

(I'm on 2013 pro plus so I hope it's similar)
Go to the developer tab
[[If you can't see it: file | options | customize ribbon make sure the developer tab is ticked]]
Click record macro, give it a name and go through the motions that you want to achieve then stop recording.
Then you can edit the macro in VBA.
ActiveSheet, ActiveCell are very useful substitutions

I'm sure other people may be able to give you more specific advice but I thought I'd offer some tips on how to build your knowledge.

 

[Fluff]

Without being able to see all your code, its difficult to tell for certain. But I suspect you're missing a line like

Dim NextRow as Long

 

[LesHunter]

Hi Fluff i bet you are a Lancastrian with that red rose,
Thanks for the quick response. put the line you suggested and the compiler moved on to the next error.
I have copied the whole VBA. There are 3 macros the bottom 2 work fine the 1st one PostToRegister is the culprit
I have underlined the problem area.
Any Help would be most gratefully received.

N.B. The project I'm working on involves creating an Invoice (Done) Works OK
Creating a button to save and clear the current invoice and put the next invoice number in (Done) works OK
Create a second button just to create a new invoice number (Done) works OK
The clever bit Copy certain fields from the invoice WS1 to a Register WS3
And that's where I am stuck!!!!!!

Option Explicit
Sub PostToRegister()


Dim NextRow As Long (this is the line suggested yesterday)Dim WS1 As Worksheet
Dim WS3 As Worksheet
Set WS1 = Worksheets("Invoice")
Set WS3 = Worksheets("Register")

'figure out which row is next row
NextRow = WS3.Cells(Rows.Count, 1).End(xlUp).Row + 1
MsgBox NextRow
'Write the important Values to Register
WS3.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS1.Range("D12").WS1.Range("D13"). _
WS1.Range("D15"), Range("G35"))
End Sub

Sub Nextinvoice()
Range("D12").Value = Range("D12").Value + 1
Range("D18:E29").ClearContents
End Sub


Sub SaveInvWithNewName()
Dim NewFN As Variant
PostToRegister
' Copy invoice to a new workbook
ActiveSheet.Copy
NewFN = "D:\Attachments\InvGBE-" & Range("D12").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
Nextinvoice
End Sub


Thank you for your time I really am grateful

 

[LesHunter]

Hi Thanks for posting a response so quickly, I seem to get lost in the mellay of things, I still need to learn a lot , Once again thank you for taking the time to post

I will keep the instructions and have a practice
Thanks
les Hunter

 

[vcoolio]

Hello Les,

As far as I can tell, in this line of code from your post #4 (which you have underlined as the "culprit"):-

WS3.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS1.Range("D12").WS1.Range("D13").WS1.Range("D15"), Range("G35"))

you need to change some "dots" to commas. Also, a "WS1" is missing. Hence try the following amendment:-

WS3.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS1.Range("D12")[COLOR=#ff0000],[/COLOR]WS1.Range("D13")[COLOR=#ff0000],[/COLOR]WS1.Range("D15"),[COLOR=#ff0000]WS1.[/COLOR]Range("G35"))

The bits in red above are the alterations that need to be made.

I hope that this helps,

Cheerio,
vcoolio.

 

[LesHunter]

WOW what can I say. Thank you very very much I really have learnt a lot in these past couple of days
Everything is now working, Just a couple of mistakes and wow

Very much apreciated
Thanks again.

 

[vcoolio]

You're welcome Les. Glad that I was able to help.

Cheerio,
vcoolio.

 

[Fluff]

@LesHunter

Hi Fluff i bet you are a Lancastrian with that red rose,

I be a West Countryman I be.
That's an English rose

 

[LesHunter]

I thought you must be a lancastrian, as a Yorkshire tyke I wondered about the response...
any way it's nice to meet your acquaintance and thanks for your help as can be seen from the posts that someone as come up with the answer so everything working now.
thanks again for your time
Les