Populate Data from Source to Destination as shown

**rizmomin** · 02-15-2013, 07:43 PM

Hello:

Please refer to attached sheet.

I need a VB Macro to transfer data from Sourse Sheet to Destination sheet as shown.

Please let me know if any questions.

Thanks

Riz

**rizmomin** · 02-16-2013, 01:34 AM

Hello:

Column A has Clinic names.
Column B thru X would have names of the doctor in the clinic.

I need to populate these data in Destination sheet as follows.

1st Clinic Name from Source sheet would need to be pasted in cell F2 of Destination sheet.
The blue highlighted field needs to be as shown.
Doctors name (if any) from Column C thru X would need to pasted in column J starting at J3 of destination sheet.

This above needs to be repeated until all data is transfered.

Please let me know if any questions.

Thanks in advance for your help

Riz

**mike7952** · 02-16-2013, 09:05 AM

Try this

Option Explicit

Sub abc()
 Dim aArr As Variant
 Dim ptr As Long, i As Long, ii As Long
 
 
 With Worksheets("Sourse")
    aArr = .Range("a1").CurrentRegion
 End With
 
 ptr = 2
 With Worksheets("Destination")
    For i = 2 To UBound(aArr, 1)
        If Not IsEmpty(aArr(i, 2)) Then
            .Cells(ptr, "a").Resize(, 19) = Array("Yes", Date, "Invoice", , aArr(i, 1), , , , , _
                                                  "Supna Customer Invoices", , , "?", "N", "N", , _
                                                  "Mail Check : ABC Corp, 123 Street, VA", , "Transcription")
            For ii = 2 To UBound(aArr, 2)
                If IsEmpty(aArr(i, ii)) Then Exit For
                ptr = ptr + 1
                .Cells(ptr, "i").Resize(, 7) = Array(aArr(i, ii), , , , "?", "N", "N")
            Next
            .Cells(ptr, "h").Offset(1).Resize(4) = Application.Transpose(Array(".", "Thank you for your Business..", _
                                                                     ".", "Any Query: please forward to rizmomin@supna.com"))
            ptr = ptr + 5
        End If
    Next
 End With
End Sub

**rizmomin** · 02-16-2013, 10:45 AM

Hello Mike:

Thank you so much for your help.
This works and i really aprreciate your help.
Love it...Can you please watch this thread if i need further help with this thread.
I will close it for now and once again thank u so much.

Riz

**mike7952** · 02-16-2013, 10:56 AM

Yes I will and your welcome.

**rizmomin** · 02-16-2013, 11:23 AM

Hi Mike:

Ok let me explain you.
I will have VlLookup table to populate the Qty and Price in Column K and L.

Is there a way to have VLOOKUP formula in VB code itself.
I will be using following formula...

=VLOOKUP($I3,LCData!$A$1:$H$50,3,0) in column K and same formula for each doctor in column I
so next formula in column K will have $I4, $I5 and so on

=VLOOKUP($I3,LCData!$A$1:$H$50,4,0) in column L and same formula for each doctor in column I
so next formula in column K will have $I4, $I5 and so on

Thanks and let me know if any questions.

**mike7952** · 02-16-2013, 11:31 AM

Something like this maybe

Option Explicit

Sub abc()
 Dim aArr As Variant
 Dim ptr As Long, i As Long, ii As Long
 
 
 With Worksheets("Sourse")
    aArr = .Range("a1").CurrentRegion
 End With
 
 ptr = 2
 With Worksheets("Destination")
    For i = 2 To UBound(aArr, 1)
        If Not IsEmpty(aArr(i, 2)) Then
            .Cells(ptr, "a").Resize(, 19) = Array("Yes", Date, "Invoice", , aArr(i, 1), , , , , _
                                                  "Supna Customer Invoices", "=VLOOKUP($I" & ptr & ",LCData!$A$1:$H$50,3,0)", _
                                                  "=VLOOKUP($I" & ptr & ",LCData!$A$1:$H$50,4,0)", "?", "N", "N", , _
                                                  "Mail Check : ABC Corp, 123 Street, VA", , "Transcription")
            For ii = 2 To UBound(aArr, 2)
                If IsEmpty(aArr(i, ii)) Then Exit For
                ptr = ptr + 1
                .Cells(ptr, "i").Resize(, 7) = Array(aArr(i, ii), , "=VLOOKUP($I" & ptr & ",LCData!$A$1:$H$50,3,0)", _
                                                                    "=VLOOKUP($I" & ptr & ",LCData!$A$1:$H$50,4,0)", "?", "N", "N")
            Next
            .Cells(ptr, "h").Offset(1).Resize(4) = Application.Transpose(Array(".", "Thank you for your Business..", _
                                                                     ".", "Any Query: please forward to rizmomin@supna.com"))
            ptr = ptr + 5
        End If
    Next
 End With
End Sub

**rizmomin** · 02-16-2013, 11:50 AM

Hi Mike:

It seems to work but i see the formula pasting in "Supna Customer Invoices" row also. This should not happen.
The formula should only be pasted if there is value in column I.

Thanks and please update.

Riz

**mike7952** · 02-16-2013, 12:01 PM

Just remove the vlookup from this line

            .Cells(ptr, "a").Resize(, 19) = Array("Yes", Date, "Invoice", , aArr(i, 1), , , , , _
                                                  "Supna Customer Invoices", "=VLOOKUP($I" & ptr & ",LCData!$A$1:$H$50,3,0)", _
                                                  "=VLOOKUP($I" & ptr & ",LCData!$A$1:$H$50,4,0)", "?", "N", "N", , _
                                                  "Mail Check : ABC Corp, 123 Street, VA", , "Transcription")

            .Cells(ptr, "a").Resize(, 19) = Array("Yes", Date, "Invoice", , aArr(i, 1), , , , , _
                                                  "Supna Customer Invoices", , , "?", "N", "N", , _
                                                  "Mail Check : ABC Corp, 123 Street, VA", , "Transcription")

**rizmomin** · 02-16-2013, 12:13 PM

Hi Mike:

That's charming..
Thank you so much.
But I am not yet complete with teh whole thing yet.
I will try to configure some data myself and will need you if i am lost.

Thank you again for all your help.

Riz

**rizmomin** · 02-16-2013, 12:26 PM

Hi Mike :

Just realized something.
I will enter date is Cell B2 and thats the date which will need to go
I will enter the Start Inv # in Cell D2 and Invoice # needs to increment by one for each Invoice.

Thanks
Riz

**mike7952** · 02-16-2013, 12:37 PM

This should do it

Option Explicit

Sub abc()
 Dim aArr As Variant
 Dim ptr As Long, i As Long, ii As Long
 Dim sDate As String, iInvoiceNum As Long
 
 With Worksheets("Sourse")
    aArr = .Range("a1").CurrentRegion
 End With
 
 ptr = 2
 With Worksheets("Destination")
    sDate = .Range("b2").Value
    iInvoiceNum = .Range("d2").Value
    If Not IsNumeric(iInvoiceNum) Then Exit Sub
    For i = 2 To UBound(aArr, 1)
        If Not IsEmpty(aArr(i, 2)) Then
            .Cells(ptr, "a").Resize(, 19) = Array("Yes", sDate, "Invoice", iInvoiceNum, aArr(i, 1), , , , , _
                                                  "Supna Customer Invoices", , , "?", "N", "N", , _
                                                  "Mail Check : ABC Corp, 123 Street, VA", , "Transcription")
            For ii = 2 To UBound(aArr, 2)
                If IsEmpty(aArr(i, ii)) Then Exit For
                ptr = ptr + 1
                .Cells(ptr, "i").Resize(, 7) = Array(aArr(i, ii), , "=VLOOKUP($I" & ptr & ",LCData!$A$1:$H$50,3,0)", _
                                                                    "=VLOOKUP($I" & ptr & ",LCData!$A$1:$H$50,4,0)", "?", "N", "N")
            Next
            .Cells(ptr, "h").Offset(1).Resize(4) = Application.Transpose(Array(".", "Thank you for your Business..", _
                                                                               ".", "Any Query: please forward to rizmomin@supna.com"))
            ptr = ptr + 5
            iInvoiceNum = iInvoiceNum + 1
        End If
    Next
 End With
End Sub

**rizmomin** · 02-16-2013, 01:44 PM

Hi Mike:

Yes this will work but is it possible to add formula to increment Invoice #.
Also, would it be possible to highlight (Red) the row which starts with new invoice.

Thanks a lot

Riz

**mike7952** · 02-16-2013, 01:50 PM

This will make the row red. I dont understand what you mean by formula for Invoice? If your adding the formula in cell D2 the code will add 1 to each invoice.

Option Explicit

Sub abc()
 Dim aArr As Variant
 Dim ptr As Long, i As Long, ii As Long
 Dim sDate As String, iInvoiceNum As Long
 
 With Worksheets("Sourse")
    aArr = .Range("a1").CurrentRegion
 End With
 
 ptr = 2
 With Worksheets("Destination")
    sDate = .Range("b2").Value
    iInvoiceNum = .Range("d2").Value
    If Not IsNumeric(iInvoiceNum) Then Exit Sub
    For i = 2 To UBound(aArr, 1)
        If Not IsEmpty(aArr(i, 2)) Then
            With .Cells(ptr, "a").Resize(, 19)
                .Value = Array("Yes", sDate, "Invoice", iInvoiceNum, aArr(i, 1), , , , , _
                               "Supna Customer Invoices", , , "?", "N", "N", , _
                               "Mail Check : ABC Corp, 123 Street, VA", , "Transcription")
                .Interior.Color = vbRed
            End With
            For ii = 2 To UBound(aArr, 2)
                If IsEmpty(aArr(i, ii)) Then Exit For
                ptr = ptr + 1
                .Cells(ptr, "i").Resize(, 7) = Array(aArr(i, ii), , "=VLOOKUP($I" & ptr & ",LCData!$A$1:$H$50,3,0)", _
                                                                    "=VLOOKUP($I" & ptr & ",LCData!$A$1:$H$50,4,0)", "?", "N", "N")
            Next
            .Cells(ptr, "h").Offset(1).Resize(4) = Application.Transpose(Array(".", "Thank you for your Business..", _
                                                                               ".", "Any Query: please forward to rizmomin@supna.com"))
            ptr = ptr + 5
            iInvoiceNum = iInvoiceNum + 1
        End If
    Next
 End With
End Sub

**rizmomin** · 02-16-2013, 01:56 PM

Hi Mike:

I will add Start Invoice # in Cell D2.
Need the formula in each clinic to increment Invoice # by 1 Example D15 = D2+1, D22 = D15+1 and so on..

Thanks

Riz

**mike7952** · 02-16-2013, 02:23 PM

Ok try this

Option Explicit

Sub abc()
 Dim aArr As Variant
 Dim ptr As Long, i As Long, ii As Long
 Dim sDate As String, iInvoiceNum As String
 
 With Worksheets("Sourse")
    aArr = .Range("a1").CurrentRegion
 End With
 
 ptr = 2
 With Worksheets("Destination")
    sDate = .Range("b2").Value
    iInvoiceNum = .Range("d2").Value
    For i = 2 To UBound(aArr, 1)
        If Not IsEmpty(aArr(i, 2)) Then
            With .Cells(ptr, "a").Resize(, 19)
                .Value = Array("Yes", sDate, "Invoice", iInvoiceNum, aArr(i, 1), , , , , _
                               "Supna Customer Invoices", , , "?", "N", "N", , _
                               "Mail Check : ABC Corp, 123 Street, VA", , "Transcription")
                .Interior.Color = vbRed
            End With
            iInvoiceNum = "=$d$" & ptr & "+1"
            For ii = 2 To UBound(aArr, 2)
                If IsEmpty(aArr(i, ii)) Then Exit For
                ptr = ptr + 1
                .Cells(ptr, "i").Resize(, 7) = Array(aArr(i, ii), , "=VLOOKUP($I" & ptr & ",'LCData'!$A$1:$H$50,3,0)", _
                                                                    "=VLOOKUP($I" & ptr & ",'LCData'!$A$1:$H$50,4,0)", "?", "N", "N")
            Next
            .Cells(ptr, "h").Offset(1).Resize(4) = Application.Transpose(Array(".", "Thank you for your Business..", _
                                                                               ".", "Any Query: please forward to rizmomin@supna.com"))
            ptr = ptr + 5
        End If
    Next
 End With
End Sub

**rizmomin** · 02-16-2013, 03:44 PM

Hi Mike:

YES that works great..
I really appreciate you for all your help and i hope you will help me in the future for any similar help.
I am sorry but I am not a VB programmer but i get some very professional help from people like you on this forum.

Riz

**rizmomin** · 02-17-2013, 07:30 PM

Hi Mike:

I need to make a small change with your help.

Populate data from Source To Destination only for clinics which are active.
Column A in Source tab shows active/inactive clinic.
Active = 1, Inactive = 0.

I hope you would be able to help with this.

VB Macro you have written is already in the file.

Please let me know if any questions.

Thank you.

Riz

**mike7952** · 02-18-2013, 09:41 AM

This should do it

Option Explicit
Sub abc_v6()
 Const shDest As String = "Destination"
 Const shSource As String = "Source"
 Const shLCData As String = "LCData"
 Dim aArr As Variant
 Dim ptr As Long, i As Long, ii As Long
 Dim sDate As String, iInvoiceNum As String
 
 With Worksheets(shSource)
    aArr = .Range("a2", .Cells(Rows.Count, "b").End(xlUp).Offset(, 12))
 End With
 
 ptr = 2
 With Worksheets(shDest)
    sDate = .Range("b2").Value
    iInvoiceNum = .Range("d2").Value
    For i = 1 To UBound(aArr, 1)
        If aArr(i, 1) = 1 Then
            With .Cells(ptr, "a").Resize(, 19)
                .Value = Array("Yes", sDate, "Invoice", iInvoiceNum, aArr(i, 2), , , , , _
                               "Supna Customer Invoices", , , "?", "N", "N", , _
                               "Mail Check : ABC Corp, 123 Street, VA", "Y", "Transcription")
                .Interior.Color = vbGreen
            End With
            iInvoiceNum = "=$d$" & ptr & "+1"
            For ii = 3 To UBound(aArr, 2)
                If IsEmpty(aArr(i, ii)) Then Exit For
                ptr = ptr + 1
                .Cells(ptr, "i").Resize(, 7) = Array(aArr(i, ii), , "=VLOOKUP($I" & ptr & ",'" & shLCData & "'!$A$1:$H$50,3,0)", _
                                                                    "=VLOOKUP($I" & ptr & ",'" & shLCData & "'!$A$1:$H$50,4,0)", "?", "N", "N")
            Next
            .Cells(ptr, "h").Offset(1).Resize(4) = Application.Transpose(Array(" ", "Thank you for your Business.", _
                                                                               " ", "Email billing questions to rizmomin@supna.com"))
            ptr = ptr + 5
        End If
    Next
 End With
End Sub

**rizmomin** · 02-18-2013, 10:46 AM

Hi Mike:

This works excellent except that we need a VB Code at the beginning to clear the data including the highlighted rows.

Run as with the current code and then change 2-3 clinics to inactive and run again.
The data will overlap.

Please have a look and once again thank you for your most professional help.

Riz

**mike7952** · 02-18-2013, 11:06 AM

This will do it

Option Explicit
Sub abc_v6()
 Const shDest As String = "Destination"
 Const shSource As String = "Source"
 Const shLCData As String = "LCData"
 Dim aArr As Variant
 Dim ptr As Long, i As Long, ii As Long
 Dim sDate As String, iInvoiceNum As String
 
 With Worksheets(shSource)
    aArr = .Range("a2", .Cells(Rows.Count, "b").End(xlUp).Offset(, 12))
 End With
 
 ptr = 2
 With Worksheets(shDest)
    sDate = .Range("b2").Value
    iInvoiceNum = .Range("d2").Value
    With .Range("a2", .Cells(Rows.Count, "h").End(xlUp).Offset(, 11))
        .Clear
    End With
    For i = 1 To UBound(aArr, 1)
        If aArr(i, 1) = 1 Then
            With .Cells(ptr, "a").Resize(, 19)
                .Value = Array("Yes", sDate, "Invoice", iInvoiceNum, aArr(i, 2), , , , , _
                               "Supna Customer Invoices", , , "?", "N", "N", , _
                               "Mail Check : ABC Corp, 123 Street, VA", "Y", "Transcription")
                .Interior.Color = vbGreen
            End With
            iInvoiceNum = "=$d$" & ptr & "+1"
            For ii = 3 To UBound(aArr, 2)
                If IsEmpty(aArr(i, ii)) Then Exit For
                ptr = ptr + 1
                .Cells(ptr, "i").Resize(, 7) = Array(aArr(i, ii), , "=VLOOKUP($I" & ptr & ",'" & shLCData & "'!$A$1:$H$50,3,0)", _
                                                                    "=VLOOKUP($I" & ptr & ",'" & shLCData & "'!$A$1:$H$50,4,0)", "?", "N", "N")
            Next
            .Cells(ptr, "h").Offset(1).Resize(4) = Application.Transpose(Array(" ", "Thank you for your Business.", _
                                                                               " ", "Email billing questions to rizmomin@supna.com"))
            ptr = ptr + 5
        End If
    Next
 End With
End Sub

**rizmomin** · 02-18-2013, 11:11 AM

Hi Mike:

That's charming, works great.
Thank so so muchhhhhhhhhhhhhhhh...

Riz

**rizmomin** · 02-18-2013, 11:12 AM

Hi Mike:

That's charming, works great.
Will contact you if needed any further...

Thank so so muchhhhhhhhhhhhhhhh...

Riz

**rizmomin** · 02-18-2013, 11:02 PM

Hello Mike:

Once again sorry to bother you.
Here is something i would like your help.

First i need add a line above "Thank you for your Business." to say "Service Period: XXX where the value of XXX would be at LCData!B1

So Basically it would show

Service period : Jan13 ( Jan13 would be on LCData!B1

Thank you for your Business.

Email billing questions to rizmomin@supna.com

Please let me know if any questions.

Thanks

Riz

**mike7952** · 02-18-2013, 11:17 PM

See if this will do it

Option Explicit
Sub abc_v6()
 Const shDest As String = "Destination"
 Const shSource As String = "Source"
 Const shLCData As String = "LCData"
 Dim aArr As Variant
 Dim ptr As Long, i As Long, ii As Long
 Dim sDate As String, iInvoiceNum As String
 Dim sServicePeriod As String
 
 With Worksheets(shSource)
    aArr = .Range("a2", .Cells(Rows.Count, "b").End(xlUp).Offset(, 12))
 End With
 With Worksheets(shLCData)
    sServicePeriod = "Service period :" & .Range("b1").Value
 End With
 ptr = 2
 With Worksheets(shDest)
    sDate = .Range("b2").Value
    iInvoiceNum = .Range("d2").Value
    With .Range("a2", .Cells(Rows.Count, "h").End(xlUp).Offset(1, 11))
        .Clear
    End With
    For i = 1 To UBound(aArr, 1)
        If aArr(i, 1) = 1 Then
            With .Cells(ptr, "a").Resize(, 19)
                .Value = Array("Yes", sDate, "Invoice", iInvoiceNum, aArr(i, 2), , , , , _
                               "Supna Customer Invoices", , , "?", "N", "N", , _
                               "Mail Check : ABC Corp, 123 Street, VA", "Y", "Transcription")
                .Interior.Color = vbGreen
            End With
            iInvoiceNum = "=$d$" & ptr & "+1"
            For ii = 3 To UBound(aArr, 2)
                If IsEmpty(aArr(i, ii)) Then Exit For
                ptr = ptr + 1
                .Cells(ptr, "i").Resize(, 7) = Array(aArr(i, ii), , "=VLOOKUP($I" & ptr & ",'" & shLCData & "'!$A$1:$H$50,3,0)", _
                                                                    "=VLOOKUP($I" & ptr & ",'" & shLCData & "'!$A$1:$H$50,4,0)", "?", "N", "N")
            Next
            .Cells(ptr, "h").Offset(1).Resize(4) = Application.Transpose(Array(sServicePeriod, "Thank you for your Business.", _
                                                                               " ", "Email billing questions to rizmomin@supna.com"))
            ptr = ptr + 5
        End If
    Next
 End With
End Sub

**rizmomin** · 02-18-2013, 11:52 PM

Hi Mike:

Once again thank you so much.
One small problem...the date on Cell b1 i typed up is Jan 2013.
It is showing as Jan-13 on cell b1
But it is coming as 1/1/2013 Ex Service Period : 1/1/2013

I wanted Service Period : Jan 2013

Riz

**rizmomin** · 02-19-2013, 12:23 AM

Hi Mike:

Once again thank you so much.
One small problem...the date on Cell b1 i typed up is Jan 2013.
It is showing as Jan-13 on cell b1
But it is coming as 1/1/2013 Ex Service Period : 1/1/2013

I wanted Service Period : Jan 2013

Riz

**mike7952** · 02-19-2013, 12:46 AM

replace

 With Worksheets(shLCData)
    sServicePeriod = "Service period :" & .Range("b1").Value
 End With

with

 With Worksheets(shLCData)
    sServicePeriod = "Service period :" & .Range("b1").Text
 End With

**rizmomin** · 02-19-2013, 09:51 AM

Hi Mike:

Yup this works, how would i add a blank row before "Service Period".

Thank you..

Riz

**mike7952** · 02-19-2013, 10:02 AM

Like this

Option Explicit
Sub abc_v6()
 Const shDest As String = "Destination"
 Const shSource As String = "Source"
 Const shLCData As String = "LCData"
 Dim aArr As Variant
 Dim ptr As Long, i As Long, ii As Long
 Dim sDate As String, iInvoiceNum As String
 Dim sServicePeriod As String
 
 With Worksheets(shSource)
    aArr = .Range("a2", .Cells(Rows.Count, "b").End(xlUp).Offset(, 12))
 End With
 With Worksheets(shLCData)
    sServicePeriod = "Service period :" & .Range("b1").Text
 End With
 ptr = 2
 With Worksheets(shDest)
    sDate = .Range("b2").Value
    iInvoiceNum = .Range("d2").Value
    With .Range("a2", .Cells(Rows.Count, "h").End(xlUp).Offset(1, 11))
        .Clear
    End With
    For i = 1 To UBound(aArr, 1)
        If aArr(i, 1) = 1 Then
            With .Cells(ptr, "a").Resize(, 19)
                .Value = Array("Yes", sDate, "Invoice", iInvoiceNum, aArr(i, 2), , , , , _
                               "Supna Customer Invoices", , , "?", "N", "N", , _
                               "Mail Check : ABC Corp, 123 Street, VA", "Y", "Transcription")
                .Interior.Color = vbGreen
            End With
            iInvoiceNum = "=$d$" & ptr & "+1"
            For ii = 3 To UBound(aArr, 2)
                If IsEmpty(aArr(i, ii)) Then Exit For
                ptr = ptr + 1
                .Cells(ptr, "i").Resize(, 7) = Array(aArr(i, ii), , "=VLOOKUP($I" & ptr & ",'" & shLCData & "'!$A$1:$H$50,3,0)", _
                                                                    "=VLOOKUP($I" & ptr & ",'" & shLCData & "'!$A$1:$H$50,4,0)", "?", "N", "N")
            Next
            .Cells(ptr, "h").Offset(1).Resize(5) = Application.Transpose(Array(" ", sServicePeriod, "Thank you for your Business.", _
                                                                               " ", "Email billing questions to rizmomin@supna.com"))
            ptr = ptr + 6
        End If
    Next
 End With
End Sub

**rizmomin** · 02-19-2013, 10:27 AM

Hi Mike:

This will work for me.

Thank you

Riz

**rizmomin** · 02-19-2013, 05:57 PM

Hello Mike:

From the same file, now i am ready to create invoice for each client.

Please refer to attached pdf file and excel file.

I need to create 1 pdf file for all invoices data which are in sheet "Destination".
Assume I have 10 invoices, the need to create 1 pdf with 10 invoices.

I would need to enter Start Invoice # in Cell J5 and End Invoice # in Cell J6.

Refer to attached 2 invoices i generated individually/manually.

Some fields are populated in Invoice Sheet by using VLOOKUP functiion.

Please let me know if any questions.

Thanks

Riz

**mike7952** · 02-20-2013, 01:02 PM

I need to create 1 pdf file for all invoices data which are in sheet "Destination".
Assume I have 10 invoices, the need to create 1 pdf with 10 invoices.

Im confused? Is that 1 pdf file per invoice or 1 pdf file for all 10 invoices?

**rizmomin** · 02-20-2013, 03:17 PM

Hi Mike:

Yes i wanted 2 options
Option 1 : 1 pdf file per Invoice , File name would be Date_CustomerName_Inv#

Option 1 : 1 pdf file per all invoices, File name would be Date_XXXX where XXXX = Customer Invoices.

pdf file will be saved at C:\Customer Invoices\

I hope this is clear and please let me know if any further questions.

Thanks

Riz

**rizmomin** · 02-20-2013, 09:08 PM

Hello Mike:

Sorry to bother you.
I have completed the task of producing pdf invoice.
Please have a look at attached 4 pdf invoice file and 1 excel file.

Have a look at "Invoice" Sheet.
Here the yellow highlighted field has formula in cells to populate the data from sheet "Destination".
Formula is dragged down in row 18 thru 34.
Doing this is adding other customers invoice data from "Destination" sheet.

Please open up the pdf invoices and you will see what i am talking about.

I dont know the solution to this.

please help resolve this issue.

One way is to add a command to copy particular invoice data within the Macro named "Sub SaveAsPDFExample()" which is in the file.

Let me know if you have any questions

Thanks

Riz

**mike7952** · 02-21-2013, 12:00 PM

I'll take alook tonight.

**rizmomin** · 02-21-2013, 07:01 PM

Hi Mike:

I have resolve the above problem.
I have also been able to create individual pdf invoices.
So thats 1 pdf per invoice.

I still like a way to create multiple invoice in 1 pdf file..

Please let me know.

Thanks

Riz

**mike7952** · 02-22-2013, 10:39 AM

What should the multiple PDF look like?

**rizmomin** · 02-22-2013, 10:55 AM

Hi Mike :

If you open Test1.xlsm excel file, and go to "Invoice" sheet.
Here enter Start Inv # 7000 and end Inv # 7003 in cell P3 and P4.

Click "Click to create Invoice in pdf". You have need to change the location where to create the pdf file and thats in the macro file.
(Each invoice # information is populated by VLOOKUP function when going thru this process)

This macro will print each invoice file in pdf format (dont worry about mixed up customer in Invoice, i have taken care of that).

So 7000 to 7003 will create 4 pdf file each for Inv # 7000,7001,7002 and 7003.

I need an option so that all these 4 invoices are merged in 1 pdf file.
Basically Date_Invoices.pdf (02_22_2013_Invoices.pdf would need to have 4 pages, each page being Inv # 7000,7001,7002 and 7003).

I hope this is clear.

Let me know if any questions.

**mike7952** · 02-22-2013, 11:13 AM

You would need to create a template like the one for 1 pdf file for multipule page pdf files. Would there be a max number of page. I just created a temp, doesnt show accurate data yet. Click macro button. See attatched.

**rizmomin** · 02-22-2013, 11:18 AM

Hi Mike:

Oh i see, ok let me look into this.
I got your point.
I will contact you after i put all VLOOKUP etc...

Thanks a bunch!!!!

Riz

**mike7952** · 02-22-2013, 11:26 AM

can I ask why you want to list 7000 thru 7003? wouldnt you want to just create an Invoice for everything on Dest sheet?

**rizmomin** · 02-22-2013, 12:33 PM

Hi Mike:

Yes would need to create Invoice for everything on Dest sheet...i was just giving example.

(Yes, i create IIF file, which will imported into Quickbooks the accounting software.
I do not like the Invoice layout what Quickbooks offer.
Hence i like to generate the invoice thru Excel.
So basically Start Invoice # would be the the number from Quickbooks)

Riz

**rizmomin** · 02-22-2013, 01:23 PM

Hi Mike:

Is it possible to have a macro which will create the template like you did manually.
The number of pages would be he differance between the high and low invoice # in Destination sheet.
Data we can work on after completing this task.

Thanks

Riz

**mike7952** · 02-22-2013, 05:39 PM

Ok give this a try. I create a hidden sheet to load the invoices to so we can print to pdf as 1 file. Only vlookup forumals are for the address. Invoice and Multi-Invoice sheets will clear itself when running the macro.

**rizmomin** · 02-22-2013, 06:06 PM

Hi Mike:

Where is the hidden Sheet?

I ran the macro but not sure what happened?

Riz

**rizmomin** · 02-22-2013, 06:09 PM

Hello:

Riz

**mike7952** · 02-22-2013, 06:10 PM

Its in the workbook. Its veryhidden so users can not right click on the sheets and unhide sheet. If you want to unhide it goto the vbe editor and click on the sheet and in the properties window you can set the visible property to xlvisible.

**rizmomin** · 02-22-2013, 06:17 PM

Hi Mike:

Yes I see it.
I really admire your work.
You are genious.
I will try out and let you know if any issues or concerns.

Thanks a lot

Riz

Populate Data from Source to Destination as shown

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Re: Populate Data from Source to Destination as shown

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions