Hi All,
I want to copy a number of rows from an 'Archive' spreadsheet to a 'Previous_Sites_Visits' spreadsheet based on a drop down selection in the 'Input' spreadsheet.
I have a site visit workbook where site data can be entered in a form on the 'Input' spreadsheet. Once the relevant data has been filled in, a submit button is pressed which copies the data to a 'Data' spreadsheet and clears the 'Input' form ready for the next site details to be entered. Once a number of sites have been visited and the data is ready to be put into our database a "Generate .zrxp" button is pressed on the 'Data' spreadsheet which creates a data file compatible with our database, copies the data to the 'Archive' spreadsheet, and clears the entries in the 'Data' spreadsheet.
I would like to be able to interrogate the 'Archive' spreadsheet so that when a site is selected in the drop down menu in 'Input!B2' all rows containing that site name in the 'Archive' spreadsheet are automatically copied into the 'Previous_Site_Visits' spreadsheet in date order, with the last visit at the top.
I am not very good with VBA so I have tried to record a macro to help me, but I think it is not working because the reference cell is a drop down box and I can't seem to fix the value when copying and pasting rows. I have also tried Vlookups but the Archive table is not organised by the unique reference (site name) but by the date of the visit. It also looks like Vlookups can only return one value from an array rather than a number of columns. I tried looking at a solution with 3 helper columns that assigned row numbers etc, but I couldn't make it work because the 'Archive' sheet is dynamic and rows will continuously be added to it.
Any help would be much appreciated.
Cheers.
I want to copy a number of rows from an 'Archive' spreadsheet to a 'Previous_Sites_Visits' spreadsheet based on a drop down selection in the 'Input' spreadsheet.
I have a site visit workbook where site data can be entered in a form on the 'Input' spreadsheet. Once the relevant data has been filled in, a submit button is pressed which copies the data to a 'Data' spreadsheet and clears the 'Input' form ready for the next site details to be entered. Once a number of sites have been visited and the data is ready to be put into our database a "Generate .zrxp" button is pressed on the 'Data' spreadsheet which creates a data file compatible with our database, copies the data to the 'Archive' spreadsheet, and clears the entries in the 'Data' spreadsheet.
I would like to be able to interrogate the 'Archive' spreadsheet so that when a site is selected in the drop down menu in 'Input!B2' all rows containing that site name in the 'Archive' spreadsheet are automatically copied into the 'Previous_Site_Visits' spreadsheet in date order, with the last visit at the top.
I am not very good with VBA so I have tried to record a macro to help me, but I think it is not working because the reference cell is a drop down box and I can't seem to fix the value when copying and pasting rows. I have also tried Vlookups but the Archive table is not organised by the unique reference (site name) but by the date of the visit. It also looks like Vlookups can only return one value from an array rather than a number of columns. I tried looking at a solution with 3 helper columns that assigned row numbers etc, but I couldn't make it work because the 'Archive' sheet is dynamic and rows will continuously be added to it.
Any help would be much appreciated.
Cheers.
