Command Button Programming

[jvaught333]

Good evening all. I am trying to create a command button that will do the following:
There are potentially a large number of rows
There are 9 columns of sortable data
I want to copy each row (the entire row) to a separate sheet as a summary page which has the condition of the Follow up date being any date today or before
How would I write this code?
Thank you for your help with this.

 

[jvaught333]

It would be even better to be able to run this as the workbook opens so that it is an automatic update rather than a button update, but either way will work. This will be done with approximately 6 different worksheets filtering into one

 

[My Aswer Is This]

We need the name of both sheets. The copy from sheet and the paste to sheet.
We need to know what column The Follow up date is in.
We need like column("C") or column ("M")

Should we write over all existing data in copy to sheet or add to this sheet?

And when you say:
This will be done with approximately 6 different worksheets filtering into one

We need to know what sheets. Will it be all sheets except for master sheet where all data will be copied to.

 

[jvaught333]

We need the name of both sheets. The copy from sheet and the paste to sheet.
We need to know what column The Follow up date is in.
We need like column("C") or column ("M")

Should we write over all existing data in copy to sheet or add to this sheet?

The copy from sheet is "Business Processing"
The copy to sheet is "Tracker Items Due Today"
Follow up date is Column F
When the code runs, it should overwrite any existing data

 

[jvaught333]

We need the name of both sheets. The copy from sheet and the paste to sheet.
We need to know what column The Follow up date is in.
We need like column("C") or column ("M")

Should we write over all existing data in copy to sheet or add to this sheet?

And when you say:
This will be done with approximately 6 different worksheets filtering into one

We need to know what sheets. Will it be all sheets except for master sheet where all data will be copied to.

Ok. There are 5 sheets (Business Processing, Group Service Frequency, Future Assets, CMOT, Systematic Payouts) that will all be filtering onto the master sheet (Tracker Items Due Today). I need it to only put items that have a follow up date of today or older on the master sheet. There will be different sections in which each sheet will filter on the master. For instance, the top of the sheet will be Business Processing, then after that it will be Group Service Frequency and so on.

 

[Fazza]

FYI, this can be done without code - create a query & set it to refresh on file open.

 

[My Aswer Is This]

Would you care to explain how to do a "Query". I surely do not know how to do that. We could both learn here.

FYI, this can be done without code - create a query & set it to refresh on file open.

 

[Fazza]

google will help, here is one I just found http://www.exceluser.com/formulas/msquery-excel-relational-data.htm
google will find many more

exact details will be version dependent.
for doing manually, so no VBA, I find simplest is to use old keystrokes ALT-D-D-N & follow the wizard
but if you check excel help for your version there may be an easier to repeat (via ribbon) way without remembering old keystrokes

for this question, I had thought a number of worksheets were going into a single summary table

that can be done with SQL like

SELECT fields
FROM first sheet
WHERE datefield <= TODAY()
UNION ALL
SELECT fields
FROM second sheet
WHERE datefield <= TODAY()
UNION ALL
SELECT fields
FROM third sheet
WHERE datefield <= TODAY()

etc
once created, you can probably right click from the query (will vary by version, this certainly was OK up to Excel 2003) and set its 'refresh on open' property to true

however if each worksheet feeds into its own list on the summary, then it would be multiple individual queries.

cheers

 

[jvaught333]

google will help, here is one I just found http://www.exceluser.com/formulas/msquery-excel-relational-data.htm
google will find many more

exact details will be version dependent.
for doing manually, so no VBA, I find simplest is to use old keystrokes ALT-D-D-N & follow the wizard
but if you check excel help for your version there may be an easier to repeat (via ribbon) way without remembering old keystrokes

for this question, I had thought a number of worksheets were going into a single summary table

that can be done with SQL like

SELECT fields
FROM first sheet
WHERE datefield <= TODAY()
UNION ALL
SELECT fields
FROM second sheet
WHERE datefield <= TODAY()
UNION ALL
SELECT fields
FROM third sheet
WHERE datefield <= TODAY()

etc
once created, you can probably right click from the query (will vary by version, this certainly was OK up to Excel 2003) and set its 'refresh on open' property to true

however if each worksheet feeds into its own list on the summary, then it would be multiple individual queries.

cheers

The only question I have for this is, will it insert new rows for each row that is copied over or will it just copy them into cells and I need to leave a lot of space in between for the different sections?

 

[My Aswer Is This]

Thanks for that information. I'm use to writing Vba scripts but I know others are opposed to using Vba. I will wait on providing a Vba script until user decides. This query thing looks complicated to me but maybe I will look into this.

google will help, here is one I just found http://www.exceluser.com/formulas/msquery-excel-relational-data.htm
google will find many more

exact details will be version dependent.
for doing manually, so no VBA, I find simplest is to use old keystrokes ALT-D-D-N & follow the wizard
but if you check excel help for your version there may be an easier to repeat (via ribbon) way without remembering old keystrokes

for this question, I had thought a number of worksheets were going into a single summary table

that can be done with SQL like

SELECT fields
FROM first sheet
WHERE datefield <= TODAY()
UNION ALL
SELECT fields
FROM second sheet
WHERE datefield <= TODAY()
UNION ALL
SELECT fields
FROM third sheet
WHERE datefield <= TODAY()

etc
once created, you can probably right click from the query (will vary by version, this certainly was OK up to Excel 2003) and set its 'refresh on open' property to true

however if each worksheet feeds into its own list on the summary, then it would be multiple individual queries.

cheers