Posts by MAID1812

Hi @gijsmo

Fantastic!! Thanks so much. You've been amazing.

I didn't realise you were in Aus. I've been there several times to visit family - brother in Forster, nephew and niece in Sydney and another nephew in Adelaide. I love it and I'm overdue another visit - not sure that's happening any time soon

I hope the Covid situation settles down there - although its nowhere near as widespread as here in the UK.

Enjoy your cold one - I'll have one for you too.

Thanks again for your help.

Cheers

David

@gijsmo - Thank you so much, that worked like a dream!!! If I could, I'd gladly buy you a celebratory beer (or beverage of choice!). Really appreciate your help :-).

One question - and I don't want you to spend any time on this at all - is there a simple way to copy the cell's values (rather than the contents),i.e. at the moment, its copying over some conditional formatting and some formulas (In my 'live' worksheet, Owner and Manager name, for example are extracted by a formula which extracts the name from the email address).

Its not a big deal - just wondered if there was a simple change that a novice, i.e. me, could make.

Thanks again for your help. You're a star!!!

Maid1812

Thanks everyone for your help, Much appreciated.

I'm not familar with Power Query at all, so I'm sticking with the traditional route (although I'm clearly not great wityh that either!!). Apologies @alansidman.

@gijsmo - I have taken the code you supplied (v2) incorporating @vcoolio's suggestions, and I've tweaked it to fit with my actual 'live' reporting needs. However, I cannot get it to function properly.

See the attached spreadsheet with the code included and a couple of my comments.

Sheets are:

iGARApplications - the source data - cutdown version only two de-personalised rows included.

Report Generation -criteria input. Only assigned code to the 'Platinum' button for now.

Report - Platinum - Output

Report - Platinum Saved (just a saved copy of the report column headers - to paste back in when I encounter errors

List - a single named range for the report criteria dropdown.

You will see from the code the input columns from the source and the order they need to be reported in.

For the report output, I always need rows 1 - 3 to be there (including the read header row). At the moment these are being deleted (when I can get it to work).

On line 39 of the code, when rSRC range is set to A1 I get runtime error 1004. I've managed to get the report to run setting this to A3 and A4, but whilst it selects the correct ID for the criteria, it only outputs c. 3 fields - and a strange one off to the right. The rest are blank.

Any additional help you can give me to get this working would be appreciated. I've probably done something dim!

Many thanks

Maid1812

Hi

I have a very large worksheet that I would like to apply filters to (using VBA) and copy selected columns to a new sheet - in a different order.

Each time the report is run, I would like the previous content on the Report sheet to be cleared (leaving the headers in place)

I have created a very simplified sample workbook (which should be uploaded).

In the Criteria sheet, the two filters are selected from the dropdowns.

The Data sheet contains the data I wold like to filter and then copy selected columns to the Report sheet.

From looking at the Data & Report sheets, you will see that once the filters are applied, I only want to copy columns A, B, C, D, E and H. In the Report sheet, I need them to appear in the order A, C, D, E, H, B.

This is a very simplified example, the 'live sheet' I am working with contains c. 150 columns and hundreds of rows.

I have tried various combinations of Autofilter and Union, but I can't get it to work.

Is anyone able to help please?

Thanks

Maid1812

Hi

I have dates stored in an Excel Worksheet as follows:

C13:C272

J13:J272

P13:P272

V13:V272

AB13:AB272

I would like to be able to hyperlink to a cell in any of these columns that match today's date. Hyperlink to be in cell B9.

Are you able to help with the formula to achieve this please?

Many thanks

Maid1812

Hi vcoolio - How would I change the code to copy only the VALUES to the target sheet rather than also include formulas, formatting etc?

Thanks

David

Hi Mumps

Thank you - but how do you explain why it works for you, but not for me?

Am I doing something wrong?

Thanks

David

Hi vcoolio . Thanks for sending your solution through. The only thing it wasn't doing was clearing the report content at the start of each run - but I've amended that and it now works perfectly so thank you.

Mumps as vcoolio suggested - I added your code as a standard macro and assigned it to a button (in a separate tab). Unfortunately, I still can't get it to work. It gets a run time error 1004. After the first run, the dates in "Finished" are filtered out, and after a second run the dates in "Finished" are back, but the ones in "Complete" are filtered out and a second header row is added to "Benefit Summary" - but no data is copied. IS it me doing something wrong still?

Attached is a version of the file after two runs.

Many thanks

David

Hi Mumps - thank you for producing this code for me - I really appreciate your help.

Unfortunately, it didn't work.

1. The first time I ran it I had a VB Run Error 1004 (see Att 1)

2. The second time it produced a 400 (see Att 2).

3. At the end of each run, in the "Completed" tab, the content was filtered on Date Completed with the criteria "after 00/01/1900 and less than or equal to 5/7/2020", and.....

4. After the 2nd run, In the "Benefit Summary" sheet, elements of the header from the "Finished" sheet had been appended as an additional header row.

I have attached a copy of the workbook exactly as it was after the 2nd run (Att 3).

Many thanks

David

I have a Workbook with 3 Worksheets as follows:

Worksheet 1 “Finished”

Worksheet 2 “Completed” (there is a difference between the two which I won’t explain now).

Worksheet 3 “Benefit Summary”

I would like to develop a routine that will achieve the following:

• For every row in “Finished” where the date in column C is later than 7 days before TODAY (i.e. within the past week)…..
• Copy the data in that row from following columns:
  • Status (Column A)
  • Number (Column B)
  • Finished Date (Column C)
  • Theme (Column E)
  • Title (Column F)
  • Description of Work (Column H)
  • Benefit (Column K)
• Paste that data in the corresponding columns in next available row (after the header) in “the “Benefit Summary” sheet
• Repeat the same steps for the “Completed” sheet, appending this data to the rows after the data pasted in the previous step.
• So, the target outcome is a single report in “Benefit Summary” containing all row from the “Finished” and “Completed” sheets with a date (column C) between today and one week previously.
• This report could be run multiple times, but as a minimum, daily, so I would like data from the previous run deleted from the “Benefit Summary” sheet each time the routine is run (leaving the header row intact).

Are you able to help?

I've attached a sanitised version of the workbook for info.

Many thanks