Database to Template - Ideas welcomed

[Domroy]

I am putting together a database that I'd like to be able to have a macro assigned to a button that will take the information I have, and add it to a table that will continuously update. I'll build an invoice (or pre-invoice), and I'd like the following data to populate from the invoice, to the next blank row in a table.

In my current template, I'd like to pull:
Preinvoice Number (TBD where that will be)
Date (IN B9 of the template)
Show (in B10 of the template)
Episode (In B11 of the template)
Amount (In H32, but can we have the macro look for "TOTAL:" and then pull the data one cell to the right?)
Due Date - have it calculate to 30 days from Date

Any other thoughts welcome. Thanks for your help!!

Judi

 

[dmt32]

Hi,
You can use an array to take data from non-contiguous cells in your template and apply the data from them to a range in another sheet.

Place following code in standard module

Sub TemplateToDatabase()
Dim DataEntryRange As Range, DatabaseRange As Range
Dim Item As Variant, Data() As Variant
Dim i As Integer, InputCellCount As Integer


'template data entry cell ranges
    Set DataEntryRange = ThisWorkbook.Worksheets(1).Range("B4,B9:B11,H32")
         
'count No Input Cells
    InputCellCount = DataEntryRange.Cells.Count + 1
    
'size array
    ReDim Data(1 To InputCellCount)
    
    For Each Item In DataEntryRange.Cells
'array index
        i = i + 1
'add cell data to array
        Data(i) = Item.Value
    Next Item
    
'add date to last array element
    Data(i + 1) = Date + 30
        
'Next empty range in database
        With ThisWorkbook.Worksheets(2)
            Set DatabaseRange = .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1)
        End With


'output array to database range
        DatabaseRange.Resize(1, InputCellCount).Value = Data
'inform user
        MsgBox "Entry Complete", 48, "Entry Complete"
        
'clear template entry
        DataEntryRange.ClearContents
        
End Sub

For this example code I have assumed your Template is in sheet 1 in your workbook & the database sheet 2 but you should change as required.
The Template Cells input range is shown in RED and again, you will need to change as required.

The code places each cell value from your template in to the array – the Date + 30 days is also added to the last element of the array. The output from array is then placed to the next blank row in your database sheet.

The array is dynamic which means you can if needed, add other cells to your Data Entry Range.

Hope Helpful

Dave

 

[Domroy]

What if the database is another file on our system? Let's say it's in Dropbox?

 

[Domroy]

Ok - so I added this macro, but it's deleting the information from the invoice after it moves it over to the database. How can we avoid that?

Thank you!

 

[dmt32]

Ok - so I added this macro, but it's deleting the information from the invoice after it moves it over to the database. How can we avoid that?

Thank you!

Hi,
delete this line

'clear template entry
        DataEntryRange.ClearContents

Dave

 

[Domroy]

That's great. Can I have it push to a file that is in a new location?

 

[dmt32]

That's great. Can I have it push to a file that is in a new location?

What you require is doable

have a look at these articles for ideas

https://www.excelcampus.com/vba/copy-paste-another-workbook/

https://www.rondebruin.nl/win/s3/win001.htm

The second link you need to scroll down the page to find article "What if the Database sheet is in another workbook"

Dave

 

[Domroy]

Great! I'll take a look and get back to you. Thank you so much!!