VBA copy row to last cell containing a value

[wannabeexcelguy]

Hi gurus,

I am using the below code to copy a row in sheets(1) if it contains the value "BALANCE" and paste it in sheets(2).

My issue is that it is copying the entire row, making the sheets(2) very large. I can't figure out how to only select and copy to the last used cell of the row being copied. I can't use

Please Login or Register  to view this content.

because there are some blanks in the row which need to be copied and pasted.

Any help would be much appreciated! Thanks!

Please Login or Register  to view this content.

[xladept]

Without a sample, I'm guessing:

Please Login or Register  to view this content.

[Mumps1]

Try:

Please Login or Register  to view this content.

The way you have designed the code, the first time it finds "Balance" it will copy the data to Sheets(2) in H1 down the column. From then on when it finds "Balance", it will over-write the previous data in Sheets(2). I don't think that is what you want. Is it?

[wannabeexcelguy]

Thanks for the prompt replies xladept and Mumps1! Both of your codes are working perfectly for me with a little tweaking. Really appreciate it.

I know my code is a bit ugly Mumps1 but it does the trick as "Balance" will only ever occur once in the sheet. Thanks for the heads up though and feel free to post how I can tidy it up you like.

[Mumps1]

This code will be a little faster:

Please Login or Register  to view this content.

[xladept]

You're welcome and thanks for the rep!

[Mumps1]

Glad it worked out.