Morning Chaps,

A tricky one for me, but probably not you guys!

I have a worksheet which contains rows of data, which are transferred onto a form and printed. The trick is there could be anything from 1 to 50 rows and any record with a Y in the end column needs to be copied and pasted, then printed and repeat...

I am trying to use For...Next without much success, can anyone help?

My code I have so far is:

For counter = 1 To 10 'changed to 10 from 50 to reduce time!

If Range("I12") = "Y" Then
Range("L12:O12").Select
Selection.Copy
Sheets("STF (linked)").Select
Range("R5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("STF linked master").Select

End If

Next counter

This sort of works but copies the same row repeatedly, I don't know how to move it onto the next row? Can anyone help?

Ian

Ian,

I don't regularly work with Excel VBA but I think this solution should work for you. The problem is that you are refering to a specific cell and range in the second and third statements. (I12, L12:O12). You need to change those references on each iteration of the loop.

The first cell you want to check appears to be 11 greater than the index counter. So you need to calculate the Range by adding 11 to the counter. Try this:

If Range("I" & counter + 11) = "Y" then
Range ("L" & counter +11 & ":"O" & counter + 11).Select

Obviously, you would have to do a similar calculation to specify the destination range, unless you want to always want to paste to exactly the same destination.

Range("R" & counter + 4).Select

Glenn

Morning Glenn,

This is brilliant, thank you! I had an idea this is where I was going wrong but just couldn't sort it!

Thanks for your help!

Ian

You are very welcome, Ian.

Glenn

Try this.

Hmmm.

I think your code would need to be more like:

to meet Ian's original specification, Norie.

Glenn

Glenn

Oops!

Missed the +11 but I still think the resize is a little less cumbersome than concatenating to create the range to copy.

Thanks both, the original suggestion was much easier and even I can almost understand it!!!

Regards

Ian

Hi guys,

I know the issue has been solved, but we don't need to copy-paste, do we? (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Martin

Why not? :(

I like copying and pasting, it's fun. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

Resizing didn't include the cells the op requires (columns L - O). Resizing would include I - L, wouldn't it?

Glenn

Glenn

That's right, especially without the +11 which I missed.(IMG:http://www.utteraccess.com/forum/style_emoticons/default/dazed.gif)

Oops, and another thing I missed - a little bit of Offset wouldn't do any harm.

That's what you get for writing air code and not testing it.



Edited by: norie on Mon Mar 3 11:31:05 EST 2008.

Hi Glenn,

sorry for bringing even more confusion into this thread, but your question is directed at me? (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

This:

cl.Offset(, 3).Resize(, 4))

will return the range the OP wants, I think... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/dazed.gif) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

No, Martin,

My reply was to Norie.

Your code appears to better Excel VBA than mine.

Glenn

Now I'm really confused!! I think you've solved a problem which didn't exist!!

I'll leave you three to fight out for the resolution in just one line of code!!

Regards

Ian

Well... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/blush.gif)

I just don't like to leave sheet in copy mode and have data on clipboard...

Range2.Value = Range1.Value

is quite fast and elegant way of how to copy values (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

Martin

Hello Ian,

my first contribution to this thread was introduced by "I know the issue has been solved".

What we are discussing is rather "best practice". If the first code supplied by Glenn works for you, rejoice (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

However, you may be interested in alternative coding, as norie and me posted... that's all... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Martin