Copy variable length data with a twist!

Hi

I have a worksheet (Team List) in an open workbook (Master) that has a list of formula in column AJ. This list of Formula starts with

Please Login or Register  to view this content.

which gives me a line of text. The Code is copied down 101 rows with B2 changing as it goes (B3, B4, B5 etc).

Not all 101 rows return a result every time and there are no Blank cells (just text or formula) up until column B is empty.

I'm copying the information from column AJ into column AZ (Starting at AZ4) in another workbook (named Remote) using a button in the Remote workbook.

I'm using the PasteSpecial xlpasteValues code into AZ and only the text rows appear (say 20 rows) with all the other rows apparently Blank. However, if I write a =COUNTA(AZ:AZ) formula it returns 101, suggesting that there is actually something hidden in the remaining 81 rows. This is then messing up the Code below that is scrolling through the list in AZ to return data from several worksheets (names in AZ).

(The twist is that the Code is looking at the open Master workbook which can have a variable name so is not identified.)

The complete Code in the Remote workbook calls for data from several different places in the worksheets listed at AZ, but it's only this one that causes an issue because I later search the list and the search code crashes because it seems to see something in the Blank cells. (See below)

I've attempted to copy just the rows with values in from AJ using the commented out code on Line 3,

Please Login or Register  to view this content.

without success as this gives a 1004 Application or Object not defined error.

Where am I going wrong please?

The complete Code is as follows;

Please Login or Register  to view this content.

The part of the Code that fails is …..

Please Login or Register  to view this content.

The search code that works down the list in AZ is

Please Login or Register  to view this content.

Many thanks in advance as I should be able to do this by now and it's really bugging me!!

Frankie

I'm using the PasteSpecial xlpasteValues code into AZ and only the text rows appear (say 20 rows) with all the other rows apparently Blank. However, if I write a =COUNTA(AZ:AZ) formula it returns 101, suggesting that there is actually something hidden in the remaining 81 rows.

I think you're right, the formula does return something even the cells look empty.
This article might be useful for you:
https://excel.tips.net/T002814_Retur...ank_Value.html

to deal with the problem, try using ".value = .value" to copy paste values between 2 ranges, something like this:
Replace this:

Please Login or Register  to view this content.

with this:

Please Login or Register  to view this content.

Thanks one and all that's awesome! Apologies if the post was a bit confusing. I probably shows where my brain was at the time!!