Creating database records from an invoice

csenor

Board Regular
Joined
Apr 10, 2013
Messages
159
Office Version
  1. 365
Platform
  1. Windows
This is my invoice. I want to be able to create a database record of every transaction. In the end I want to be able to export this data into Access, but it needs to be in sequential rows and columns. I want to be able to list the cells on Sheet 1 that have Data in them in the configuration on Sheet 2 that I have listed. What I need to keep in mind is that someone might have 1 item in a transaction and others might have 5. So I need the ShopID and Customer information to appear in front of every item listed in rows 20-27.

A
B
C
D
E
F
G
H
1
ShopID Data
2
3
4
5
6
First Name
Data
Last Name
Data
Invoice #
Data
7
Address
Data
Date/Time
Data
8
City
Data
9
State
Data
Zip
Data
10
D/L #
Data
11
D/L State
Data
12
Phone #
Data
13
14
15
16
17
18
19
Quantity
Code
Description
Description
Description
Special
Unit Price
Total
20
Data
Data
Data
Data
Data
Data
21
Data
22
Data
23
Data
24
Data
25
Data
26
Data
27
Data

<tbody>
</tbody>



<tbody></tbody>

Sheet 2
ShopID
Invoice
Date
First
Last
Addr
City
State
Zip
Phone
D/L
D/L State
Quant
Code
Descr
Special

















































<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

csenor

Board Regular
Joined
Apr 10, 2013
Messages
159
Office Version
  1. 365
Platform
  1. Windows
Lesson 30 of LiveLessons DVD provided this code that I am posting below. I think this code will grab any data in the transaction area and paste it to the 13th column of the "Data" sheet. The only thing is with If WSI.Cells(i, 1) = "" Then. What do I need to write in between the quotes to make it say if anything exists in this cell then...

I still need some sort of code to put the invoice information and customer information in front of the transaction data.

Sub DataReport()
Dim WSD As Worksheet ' Data worksheet
Dim WSI As Worksheet ' Invoice

Set WSD = Worksheets("Data")
Set WSI = Worksheets("Invoice")

'Loop through all records on WSD
For i = 20 To 27
If WSI.Cells(i, 1) = "" Then
'copy this record to the next row on WSD
WSI.Cells(i, 1).Resize(1, 6).Copy Destination:=WSD.Cells(NextRow, 13)
NextRow = NextRow + 1
End If
Next i

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,956
Members
414,351
Latest member
james27

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top