Excel Database and Macros

[Roopesh1978]

Hi I have started to implement a database in Excel. In Sheet1, I have
several fields including Reference Number, First Name, Surname, Area, and
Visit Date. What I want to do is copy the data into sheet2 by using a macro.

I have been provided the following macro:-

dim i
i =1
do until worksheets("sheet1").cells(i,1)=""
worksheets("sheet2").cells(i,1)=worksheets("sheet1").cells(i,1)
i = i + 1
loop

When I run this macro, it only copies the first column from sheet1 into
sheet2.

Does anyone know what I can do to the macro to copy over all the contents of
sheet1 into sheet2?

Also, I wanted to know how I can amend the macro so that when the data is
copied from sheet1 to sheet2 then only the data relating to a specific Area
will be displayed in sheet2, for example, looking at the Area field, sheet2
will show all the visits undertaken in USA.

Finally, i wanted to know how I can amend the macro so that when the data is
copied from sheet1 to sheet2 then only the data relating to the visits
conducted in a specific year are displayed, for example, looking at the Visit
Date field, sheet2 will show all the visits undertaken in 2004.

Any help will be appreciated.

Kind Regards,

Roopesh

 

[Dave Peterson]

First, I would argue against doing this.

If you keep all your data on one worksheet, then you'll be able to do more
things (charts and graphs, sorting, filtering (by student), pivottables, ...

Moving the data to different sheets would make that kind of stuff more difficult
and even worse, you may find people updating the data in the wrong sheets!

But if you have to, I'd still keep all my data in one worksheet and update it
there. Then each time I needed these separate worksheets, I'd run a macro that
would regenerate these sheets. (Keep in mind that those are "report-only"
worksheets. Any changes to them will be lost with the next re-generation.)

If that sounds like an idea you could use, you may want to look at how Ron de
Bruin and Debra Dalgleish approached this kind of thing:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Or:

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

 

[Roopesh1978]

Thanks Dave, Will have a play around with the examples.

Roopesh