Creating database

[ct1]

Hi,

I need some serious help here.
Let me outline, what I try to achieve. I’m in fine art business. Certain paintings in the gallery are on consignment from different artists. I’ve created separate sheet per artist in one workbook. Each spreadsheet has a number of columns: code, size, medium, cost price, etc, where one row is responsible for one painting (each cell has a different value, either numerical or text). One column is named “Status” where I manually enter word “Sold”.
Now, I want to create one sheet a months (let’s call it Total), which will ‘keep scanning’ all the sheets of artists (at this moment 36 and let’s call them sheet1, sheet2, etc.) and every time word “sold” is detected in the “Status” column, it will copy the whole row from that sheet to Total sheet and time stamp it.
It will help me immensely with the payments for artwork sold, because I’ll only have to check one sheet.

Can this be achieved?

Thx.

[Carim]

Hi,

The easiest is to have a Data Pivot Table making the consolidation for you, and allowing you to play around with all the different criteria ...

A good explanation is provided by Debra

http://www.contextures.com/xlPivot08.html

HTH
Carim

[davesexcel]

If somebody could add to this Code so that sheet2 doesn't get searched then this code would work

Please Login or Register  to view this content.

This code will copy the row that has the word Sold in column H and paste it to the first empty row in Sheet 2 columnA

[VBA Noob]

Hi,

Not tested. Assumes you loop through sheet 1 and sheet 3

Please Login or Register  to view this content.

VBA Noob

[davesexcel]

Hi VBA,
It sounds like the # of sheets are unknown,
how about a counter such as
set counter 2 to worksheet.count
would there be a way to do this?
then sheet(1) could be the sheet to paste to

[VBA Noob]

Hi,

How about

Please Login or Register  to view this content.

VBA Noob

[ct1]

Hi

Thank you for prompt replay.

Yes, pivot table could be the option, but that is not exactly what I’m looking for. Every time I’d like to check progress on sale during the months I’d have to create pivot table and that could become a little cumbersome.

I copy and paste the whole code to VB and set up a little test, but had little success.
As you can see from attached file, I’ve crated 2 spreadsheets and when I put word “Sold” (and run Macro); the code copies this same row twice from one sheet, but doesn’t do anything to second sheet. Have I put this code in the wrong place?
Second thing, is there a way to time stamp (day and months) the row when is copied, so I can see when the individual painting was sold?

I’ve attached the Test file, so I’d appreciate if someone could check it and tell me what I’m doing wrong.
Please remember I’m complete n00b and will probably require step by step instructions.

Thx.

[davesexcel]

Here This should workfor you

Here's the code, You now have a sheet call Monthly Total, this is the Summary sheet, change the name as you require

Please Login or Register  to view this content.

[davesexcel]

Change the Name "Test" to whatever the workbook name is

For Each Ws In Workbooks("Test").Worksheets

[davesexcel]

Second thing, is there a way to time stamp (day and months) the row when is copied, so I can see when the individual painting was sold?


Thx.

If Anyone knows how to could get this code not to work in sheets("Monthly Total") then it could work

Please Login or Register  to view this content.

[davesexcel]

Got it,

Just need the Application.enableevents=false
set to true at the end of the macro

Please Login or Register  to view this content.

this code goes into the workbook module, right click on the excel icon at the top of the screen beside the file menu, view code, past the below code there

Please Login or Register  to view this content.

[davesexcel]

darn did it again, this needs to be changed to

Please Login or Register  to view this content.

There is also a right click event in the sheet, right click in column H to input the word Sold
right click again and the word clears

[ct1]

I really appreciate your effort.

This macro works like a dream, but as soon as I update to the new one with date, I get runtime error. I’ve included zip file.
Just one more question. I’d like to create sheet like that every months. Now, items sold are not deleted from main data base and I wouldn’t want them too. However, when I create new sheet every month items sold few months back will be included. There only way to avoid this is to include date. An item sold let’s say in July will stay in July Month Total and won’t be copied in August, September, etc. I just understood how important this is.
Is there an easy way to modify this existing code? Or everything will have to be written form scratch?

Thank you in advance.

[ct1]

I’m sorry, I was typing the message while your already sort out the runtime error. Thank you.
One more question, can I add any number of sheets and not to worry about breaking the code? Is there specific way I have to name them and if I want to change the names, what would I have to do?

Thx.

[davesexcel]

shift F11 creates new sheets, or you can right click on a sheet tab and select insert, right click on a tab to rename, or checlk out these posts


http://www.excelforum.com//showthread.php?t=581759
http://www.excelforum.com/showthread.php?t=581783

Here is a site on sorting worksheets
http://www.cpearson.com/excel/sortws.htm

[ct1]

Thank you very much for all your help.

Two thumbs up!