copy rows of data depending on value in 1 column

[wardga]

--------------------------------------------------------------------------------

Hi all

Never used macros or VBA before. have searched long and hard but cant get this right.

I want to copy a row of data dependant on the value in column W. I have 7 sheets that I want to copy from. All the data will be copied to one sheet called 'pilot'.

The 7 sheets are Named: 'Engalnd & Wales', 'Scotland', 'North', 'south', 'England and Wales (remote', 'Scotland (Remote)', 'other'.

I want the entire row to copy to the 'pilot' sheet if the value in column W is 'P'. Currently column W either has a value of P or is blank. I can change the blanks to have a value is necessary (e.g. B).

the data needs to be copied to the next available row in sheet 'pilot'. all sheets have headings in row 1.

I can't upload the sheet as data is sensitive.

any help appreciated.

[Domski]

Try this:

Please Login or Register  to view this content.

Dom

[wardga]

Cheers Dom but that doesn't seem to work. I get an Error 400 message?

[Domski]

Where do you get that?

I assumed that each sheet that you want to copy the information from has a contiguous block of data on it with headings in Row 1 and columns starting in Column A across. Is this not the case?

Dom

[wardga]

yeah that is correct. 9 sheets (tabs) overall. 7 I want to pull the data from, 1 results sheet (pilot) and 1 un-related sheet.

Several rows of data with headers in row 1.

I want it to copy the entire row where a 'P' is present in column W (the last column).

Maybe I'm putting the formula in the wrong place. I open the workbook, go to tools, macros the open VBA. Do I copy the formula into the sheet 1 window or all of the sheets? Or do I just copy the formula into the 'this workbook' window?

The error appeared when I tried to run the macro. It was copied into the 'this workbook' window.

cheers

[Domski]

The code should really sit within a module. You need to add these yourself by right clicking on the project in the explorer and select Insert...module.

You will need to tell the code to exclude the other sheet you don't want it to process like this:

Please Login or Register  to view this content.

Dom