Need help with a complicated macro, copy cell value, copy sheet, and rename.

[tahashahzadm]

Hello,

Using excel 2013 and I am a complete newbie with excel vba, so your guidance is needed.

I am making a work book in which i will add all the invoices to keep the track of payment this workbook has invoices which will have have same format so i have made a sample invoice sheet and a master sheet which will have all the invoices numbers, date of invoice, amount etc. now what i want to do is that when i buy items and get an invoice i will first add it manually in the master sheet. master sheet will have a button on top called 'Build Invoice' when i click that a text box it will ask me to put invoice number i will enter the invoice number and the sample invoice sheet will be copied and renamed with "invoice_number-Date" with "invoice number" cell in copied sheet will have the invoice number which i entered.

this looks pretty tough to me as i am a complete beginner with VBA stuff. your any kind of help is highly Appreciated.

If my explanation is not clear please do tell me.

 

[AlphaFrog]

Hi and welcome to the forum.

Change the name of your "sample invoice" sheet that gets copied and cell that receives the Invoice number.

[COLOR=darkblue]Sub[/COLOR] Build_Invoice()
    
    [COLOR=darkblue]Dim[/COLOR] InvoiceNumber [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], strInvSheetName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=green]'Prompt for Invoice Number[/COLOR]
    InvoiceNumber = Application.InputBox("Enter the Invoice number.", "Invoice Number", Type:=2)
    [COLOR=darkblue]If[/COLOR] InvoiceNumber = "False" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=green]'User canceled[/COLOR]
    
    [COLOR=green]'Invoice sheet name (InvoiceNumber mm-dd-yyyy)[/COLOR]
    strInvSheetName = InvoiceNumber & Format(Date, " mm-dd-yyyy")
        
    [COLOR=green]'copy sheet[/COLOR]
    Sheets("[B]sample invoice[/B]").Copy After:=Sheets(Sheets.Count)
    [COLOR=green]'Name copied sheet[/COLOR]
    ActiveSheet.Name = strInvSheetName
    [COLOR=green]'Invoice number in cell[/COLOR]
    Range([B]"A1"[/B]).Value = InvoiceNumber
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[tahashahzadm]

thanks alot,

this is not exactly what i want but it's pretty awesome.

the problem here is that the date value is of today's i want it to be the value of "date of issue" of that invoice.
secondly when it prompts for invoice number i can enter any number and it makes a copy without validation what i want is that if the invoice number is already entered in master sheet then only it should build invoice other wise a slightest mistake and we will end up with alot of sheets.

 

[AlphaFrog]

thanks alot,

this is not exactly what i want but it's pretty awesome.

the problem here is that the date value is of today's i want it to be the value of "date of issue" of that invoice.
secondly when it prompts for invoice number i can enter any number and it makes a copy without validation what i want is that if the invoice number is already entered in master sheet then only it should build invoice other wise a slightest mistake and we will end up with alot of sheets.

You're welcome.

"date of issue"
Where is that info or how is that determined?

Validate invoice input?
I have no idea what your criteria is.

 

[tahashahzadm]

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]invoice number[/TD]
[TD]phone[/TD]
[TD]date issue[/TD]
[TD]due date[/TD]
[TD]invoice amount[/TD]
[TD]sales rep[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]4586979897[/TD]
[TD]1/4/2013[/TD]
[TD]5/4/2013[/TD]
[TD]10000[/TD]
[TD]taha[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
this is a sample of master sheet i have, now what i want to do is when i run macro i will enter the invoice number mentioned and sheet will be renamed with this invoice number and date issue. macro should only copy sheet if the invoice number entered is already added in this sheet manually. i hope u got my point now

 

[AlphaFrog]

[color=darkblue]Sub[/color] Build_Invoice()
    
    [color=darkblue]Dim[/color] InvoiceNumber [color=darkblue]As[/color] [color=darkblue]String[/color], rngFound [color=darkblue]As[/color] Range, DateOfInv [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    [color=green]'Prompt for Invoice Number[/color]
    InvoiceNumber = Application.InputBox("Enter the Invoice number.", "Invoice Number", Type:=2)
    [color=darkblue]If[/color] InvoiceNumber = "False" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color] [color=green]'User canceled[/color]
    
    [color=green]'Match Invoice[/color]
    [color=darkblue]Set[/color] rngFound = Range("A:A").Find(InvoiceNumber, MatchCase:=False)
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] rngFound [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        
        DateOfInv = rngFound.Columns("C").Value
            
        [color=green]'copy sheet[/color]
        Sheets("sample invoice").Copy After:=Sheets(Sheets.Count)
        [color=green]'Name copied sheet[/color]
        ActiveSheet.Name = InvoiceNumber & Format(DateOfInv, " mm-dd-yyyy")
        [color=green]'Invoice number in cell[/color]
        Range("A1").Value = InvoiceNumber
    
    [color=darkblue]Else[/color]
        MsgBox "No match for invoice number '" & InvoiceNumber & "'. ", vbExclamation, "No Invioce match"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[tahashahzadm]

thanks!! that works great!