Problem with macro intended to copy cells to a different worksheet based on cell value

Hi there,

Below is part of a macro that I've partly written and partly based off other threads/advice provided here.

It's intention is to identify which row has one of 18 possible values in it's 'H' column and then copy colums A:H of that row into the range A86:H86 in a seperate spreadsheet. A subsequent part of the macro then copies A86:H86 to another part of the spreadsheet, but that is tested and working fine, so I've left it out for the sake of brevity.

The issue I am having is with the line "Then Range(Intersect(Rows(i), Range("A:H"))).Copy Sheets("Lists & Formulas").Range("A86:H86"). When the macro runs it returns an error 'Run-time error '1004': Method 'Range' of object '_Global' failed.

Please Login or Register  to view this content.

You can only use "Then" in an IF statement, like: If 1 < 5 Then Your code does not do that. Try removing the Then and see what happens.

Originally Posted by Crangiopharengoma

It's intention is to identify which row has one of 18 possible values in it's 'H' column and then copy colums A:H of that row into the range A86:H86 in a seperate spreadsheet.

Try something like this...

Please Login or Register  to view this content.