Advanced Basic Excel Project

kojak43

Active Member
Joined
Feb 23, 2002
Messages
270
I would like to have a worksheet to hold data. The fields would run from A:Q.
Once data is entered in the initial worksheet, I would like certain fields from that data worksheet, to populate a second and a third worksheet. Those next two sheets would be formatted to produce a report and a pivot table.

I can do much of the formatting for sheets 2 and 3, but do not have the ability to write the necessary code to fill the 2nd and 3rd sheets.

The field headers in sheets 2/3 would mirror header in sheet 1.

I tried to search previous posts to get some direction for this, but could not figure out a successful term to ge the search to work.

I am reasonably confident this can be done, I'm just not experienced enough to do this.

Any help is appreciated.
K
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
hi kojak43:
Let us say you have an entry kojak in cell A1 of Worksheet Sheet1
Now if you want to have the same entry in cell A1 of Worksheet Sheet2
Then in cell a1 of Sheet2 you will enter

=Sheet1!A1

I hope I have understood you right and not trivialized you request.

Please post back if this is what you intended ... otherwise explain a little further and let us take it from there!
 
Upvote 0
A few questions:

- must you have sub sheets?
* do advantages outweigh the disadvantages?

- is it necessary to replicate the data?

- have your considered scenarios, hiding columns, pivot tables, data filters ...?
It may be easier to prepare the following from one main sheet of information
* prepare summary reports
* prepare custom reports
 
Upvote 0
Hi


On the sheet you wish to enter your data, right click on the sheet name tab, select "view Code" and paste in this


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:Q2000")) Is Nothing Then
Sheets("Sheet2").Range(Target.Address) = Target
Sheets("Sheet3").Range(Target.Address) = Target
End If
End Sub


From now on whenever you enter any data on this sheet it within the range "A2:Q1000" it will be automatically mirrored on sheet2 and 3
 
Upvote 0
Thanks for your answers:
Yogi Anand:
I assumed I would have to something reasonably work intensive to accomplish what I wanted. And your response was what I thought I would have to use.
However, both Dave's answers might provide a different way.
Dave Patton:
I am not sure I can answer your questions as I do not know what Summary Reports or Custom Reports are. They could be the answer. I tried to find out what they were in a search of MrExcel - no joy. I even went as far as M.S. Excel (shutter) Help. - no joy.
If I can learn to create the reports, and then just turn it over to the user that inputs data, and have her press a key and the report appears,that would work. However, if she has to do anything more, it will not work. (it is sort of like the blind leading the halt around here.)
Dave Hawley:
The reason I wanted to use different worksheets was I assumed I could format the subsequent worksheets into the two reports I wanted.
Your code would work if I needed to use contiguous cells. Worksheet 2 might use A,B,C,F,H,& Q. Worksheet 3 might use A,B,C,D,E,G,I,K,J,L & M
That jumping around cells leaves me, and my Excel talent, out in the cold.
 
Upvote 0
Hello!

Was just wondering if the info. on sheets 2 and 3 is a filtered sub-set of the first sheet? If so, record a macro to filter the first sheet based on whatever criteria you need, and then copy and paste the filtered records to the other two sheets. Or create a macro that uses Advanced Filter to filter the records and then copy them to the other sheets as needed.

HTH's or gets you started in the right direction!
D.
 
Upvote 0
Dave Patton:
I am not sure I can answer your questions as I do not know what Summary or Custom Reports are. They could be the answer. I tried to find out what they were in a search of MrExcel - no joy. I even went as far as M.S. Excel (shutter) Help. - no help.
If I can learn to create the reports, and then just turn it over to the user that inputs data, and have her press a key and the report appears,that would work. However, if she has to do anything more, it will not work. (it is sort of like the blind (me) leading the halt (her) around here.)
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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