Need help extracting certain cells from one sheet and placing them in rows on another sheet

csenor

Board Regular
Joined
Apr 10, 2013
Messages
168
Office Version
  1. 365
Platform
  1. Windows
I am creating an invoice that will have the business' information at the top, Invoice #, Date/Time of sale, customer information in the middle, and the purchase items at the bottom. What I want to be able to do is create a database to query lots of data later on by searching for a customer name or type of item. So in order to use Microsoft Access, I need to be able to put the data into individual records. So I imagine if the customer purchases/trades 3 items, I would need to have each item in its own row, but also include the invoice #, Date/Time, Customer information to the first cells in the row. So I am getting stuck, because the number of items will change with every customer. How do I proceed with this?

What I am thinking is that I have to create an object variable to classify the information that will be the same (Invoice #, Date/Time, Customer Info, Business info) - Let's call this WSC worksheet customer

and another variable for the item information. - call this WSI worksheet item

I need to type some sort of if statement to search the 8 possible rows a20:f27 on the invoice for data. If WSI is found, place that data in row ?, column ? then place the WSC in the cells prior.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Not totally clear what you want. If the invoice is an Excel worksheet, and items purchased have to go in a definite location, then you will be able to count the number of items. A simple macro could copy customer details once, and paste them (ie duplicate them) the correct number of times. Items purchased would be pasted in their own column - presumably you would then export this to access. (I have always found excel fine for "querying" data using sumproduct)
 
Upvote 0
I have customer information organized in different cells along the top section of the worksheet (b6:b12, e6, e9, h6 h7). I want this data to appear in a row on a second worksheet every time an item appears on the invoice. 3 items means 3 rows of data. 5 items means 5 rows of data. So if we go with your suggestion and just copy/paste the data in the item area (a20:e27), we need to make it copy/paste the customer information in the preceeding columns. So if there are 12 columns of customer data, the invoice data must start in column m of that row.
 
Upvote 0
=concatenate(b6,b7,e6,e9,h6,h7) will grab the data and put it in one cell (that could be searched for key words. If they have to be in separate cells on the same row it is more tricky. In a macro cells(1,1) = cells(4,2) copies B4 into A1 Could we use that sort of approach?
 
Upvote 0
Customer data is in (a1, b6:b12, e6, e9, h6, h7). How do we refer to this? Object variable? WSC = (a1, b6:b12, e6, e9, h6, h7)?


possible Invoice data is in a20:f27


So if we go with your suggestion of copy/paste, then how do we write the code to display the customer information only in the rows that also has invoice data?


I have the livelessons DVD with the Excel VBA and I'm looking at lesson 33 Combining Workbooks.
Bill Jelen uses object variables to define the invoice sheet, the data sheet, the final row of invoice, and last row of data sheet
 
Upvote 0
Customer buys say 3 items and you create an invoice. In a day a 100 customers buy stuff. Do you add this data to your database at the end of the day?
 
Upvote 0
I propose a macro that puts customer data plus ITEM purchased in a single row in a sheet that we might call DAILYSALES. If 3 items are purchased there would be 3 rows. (or it could be located below the invoice outside the invoice print area)

Once the 3 rows are complete, a second macro triggered by a handy button would copy and paste these 3 rows into your database (or put them with all the other customer data for the day ready for transfer to database)

I will make a test sheet later this morning.
 
Upvote 0
What I want to do is in line with Mr Excel livelessons lesson 33 combining workbooks. In that scenario, he gathers reports from several departments and wants to combine everything into one worksheet. All of the reports are the same types of formatting. Everything is in the same columns/rows. So in lesson 32 he created a macro that would look at a particular folder on your computer and identify all of the .xlsx files and list that in a worksheet. In the next lesson, he uses that list, and on a second sheet in the workbook creates a macro that will look at each of these files and extract the data. He did this with I think 19 reports in about 10 seconds. This is on the lines of what I'm trying to do. A shop invoice where the data is in the same cells each time. The one difference is that my data is not always in the neighboring cells (customer data, invoice #) Ultimately, I would be collecting these invoices from several different shops that would be using this same invoice layout except the shop logo would be different. I took classes at my local college to learn Microsoft Access and I wanted to be able to import the Excel data into the Access database.
 
Last edited:
Upvote 0
I did produce a spreadsheet that automatically takes the invoice data and stores it as a row of data on a second sheet. If a new invoice is produced, the data from that will appear on the next row down, and so on. At the end of the day, that data can all be exported to a database.

I did not realise you needed a particular way to do the task.
 
Upvote 0
"I did produce a spreadsheet that automatically takes the invoice data and stores it as a row of data on a second sheet."

How did you do that, oldbrewer?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top