-
Oct 5th, 2018, 05:05 AM
#1
Thread Starter
Lively Member
[RESOLVED] Macro for tab wise invoice creation
Hi
I want to develop a macro to create an invoice as per the template for every tab.
Please help...ill attached excel hereVDA.zip
Last edited by dinukamp; Oct 5th, 2018 at 05:09 AM.
-
Oct 5th, 2018, 05:57 AM
#2
Thread Starter
Lively Member
Re: Macro for tab wise invoice creation
-
Oct 5th, 2018, 07:04 AM
#3
Re: Macro for tab wise invoice creation
i tested this it does what you ask, but i noticed some other things you may still need to do, like update dates, or check if there is any value to invoice (as in the first sheet), also the tos number (cell i2) for the above image does not match the tos numbers available in the other worksheets, which only have a value in d2
first i created an invoice template worksheet (named invoice) for which i copied the sample invoice and deleted all the values to be updated
Code:
Sub mkinvoice()
Dim inv As Worksheet, tmp As Worksheet, t As Worksheet
Set inv = Sheets("invoice")
For Each t In ThisWorkbook.Worksheets
If Not t.Name = "invoice" Then
inv.Copy , t
Set tmp = Sheets(t.Index + 1)
tmp.Name = t.Name & "_INV"
tmp.Cells(15, 6) = Replace(tmp.Cells(15, 6), " ", " " & t.Name & " ")
tmp.Cells(17, 2) = t.Name
tmp.Cells(23, 3) = t.Cells(2, 4)
tmp.Cells(24, 3) = t.Cells(2, 4)
tmp.Cells(24, 5) = t.Cells(25, 10)
End If
Next
End Sub
the code worked as i intended, without error, positioned each invoice after the relevant data sheet, if it is not as you expected then i misunderstood
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Oct 5th, 2018, 11:27 AM
#4
Thread Starter
Lively Member
Re: Macro for tab wise invoice creation
getting a error message
-
Oct 5th, 2018, 03:30 PM
#5
Re: Macro for tab wise invoice creation
It's hard to read error messages in images. What is the error, and on which line?
-
Oct 5th, 2018, 04:45 PM
#6
Re: Macro for tab wise invoice creation
first i created an invoice template worksheet (named invoice)
the error s that there is no sheet named invoice
it is almost impossible to read the image
is not an adequate description for any problem
i should have also told you that for F15 i left 2 spaces in the content DFRCL AC, to populate the centre part, though in hindsight it would have been just as easy to have the cell empty and concatenate the whole string in the code
as in some places i was just guessing as to the desired result you will have to fix some parts
Last edited by westconn1; Oct 5th, 2018 at 04:53 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Oct 6th, 2018, 12:27 AM
#7
Thread Starter
Lively Member
Re: Macro for tab wise invoice creation
It worked when i rename the sheet as invoice
How do we fix this problem....can you help me with the below
Originally Posted by westconn1
i should have also told you that for F15 i left 2 spaces in the content DFRCL AC, to populate the centre part, though in hindsight it would have been just as easy to have the cell empty and concatenate the whole string in the code
as in some places i was just guessing as to the desired result you will have to fix some parts
-
Oct 6th, 2018, 01:16 AM
#8
Re: Macro for tab wise invoice creation
tmp.Cells(15, 6) = Replace(tmp.Cells(15, 6), " ", " " & t.Name & " ")
replace this line with
Code:
tmp.Cells(15, 6) = "DFRCL " & t.Name & " AC"
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Oct 6th, 2018, 03:45 AM
#9
Thread Starter
Lively Member
Re: Macro for tab wise invoice creation
It worked ..thank you very much
i want to get first code on each tab to CELL A16
how do i do that..
-
Oct 6th, 2018, 04:34 AM
#10
Re: Macro for tab wise invoice creation
try like
Code:
tmp.Cells(16, 1) = Left(t.Name, InStr(t.Name, " ") - 1)
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 11th, 2018, 02:09 AM
#11
Thread Starter
Lively Member
Re: Macro for tab wise invoice creation
need to change as per the below
Cost sheet needs to be updated as per the below from each vessel sheet
Also need to change the cell as follows;
VDA.zipVDA.zip
Last edited by dinukamp; Dec 11th, 2018 at 02:51 AM.
-
Dec 14th, 2018, 05:39 AM
#12
Re: Macro for tab wise invoice creation
i am unable to follow exactly what you want to do, you
need to clarify the details
where does the number in I2 come from?
are the values appended to the cost sheet or should a new one be created for each tab?
i did look at this thread before, but did not find enough information to achieve any result
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 14th, 2018, 10:44 PM
#13
Thread Starter
Lively Member
Re: Macro for tab wise invoice creation
I2 is a manually filled detail...I2 "TOS" number should come to the "invoice" tab C23 & C24 (Not the D2 "TOS" number)
New cost sheet should be created for each tab let say "ZLA 59E cost sheet" same as the invoice..."ZLA 59E_INV"
Hope image above in the filling of cost sheet is clear to you
-
Dec 14th, 2018, 10:45 PM
#14
Thread Starter
Lively Member
Re: Macro for tab wise invoice creation
I2 is a manually filled detail...I2 "TOS" number should come to the "invoice" tab C23 & C24 (Not the D2 "TOS" number)
New cost sheet should be created for each tab let say "ZLA 59E cost sheet" same as the invoice..."ZLA 59E_INV"
Hope image above in the filling of cost sheet is clear to you
-
Dec 15th, 2018, 05:19 AM
#15
Re: Macro for tab wise invoice creation
I2 is a manually filled detail
do i assume that the values in d2 and i 2 will be entered in the correct positions by the user? or does the code need to swap them?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 15th, 2018, 06:49 AM
#16
Re: Macro for tab wise invoice creation
you can test this as i haven't
Code:
Set inv = Sheets("invoice")
Set costsht = Sheets("cost sheet")
For Each t In ThisWorkbook.Worksheets
If t.Name =costsht.name then exit for ' should process all sheets before cost sheet
inv.Copy , t
Set tmp = Sheets(t.Index + 1)
tmp.Name = t.Name & "_INV"
tmp.Cells(15, 6) = "DFRCL " & t.Name & " AC"
tmp.Cells(17, 2) = t.Name
tmp.Cells(23, 3) = t.Cells(2, 4)
tmp.Cells(24, 3) = t.Cells(2, 4)
tmp.Cells(24, 5) = t.Cells(25, 10)
tmp.Cells(16, 1) = Left(t.Name, InStr(t.Name, " ") - 1)
costsht.Copy , tmp
Set tmp = Sheets(t.Index + 2)
With tmp
.Name = t.Name & "_cost_sheet"
.Cells(2, 4) = t.Cells(2, 2)
.Cells(1, 1) = t.Cells(4, 1)
.Cells(2, 1) = t.Cells(5, 1)
.Cells(25, 2) = t.Cells(2, 9)
.Cells(26, 2) = t.Cells(2, 9)
.Cells(27, 2) = t.Cells(2, 4)
.Cells(28, 2) = t.Cells(2, 4)
.Cells(29, 2) = t.Cells(2, 9)
.Cells(25, 3) = t.Cells(21, 7)
.Cells(26, 3) = t.Cells(46, 7) - t.Cells(21, 7) ' this may not be correct, bit of a guess
.Cells(27, 3) = ""
.Cells(28, 3) = t.Cells(26, 10)
.Cells(29, 3) = ""
End With
Next
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 16th, 2018, 09:50 PM
#17
Thread Starter
Lively Member
Re: Macro for tab wise invoice creation
-
Dec 16th, 2018, 09:51 PM
#18
Thread Starter
Lively Member
Re: Macro for tab wise invoice creation
-
Dec 17th, 2018, 02:15 AM
#19
Re: Macro for tab wise invoice creation
have a try to see if you can manage to some yourself
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|