Full Version: I hope people work on Sunday...Excel For...Next
ian_john_walker
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
Range("R5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

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
argeedblu
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
ian_john_walker
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
argeedblu
You are very welcome, Ian.

Glenn
norie
Try this.
CODE
For counter = 1 To 10 'changed to 10 from 50 to reduce time!

Set rng =Sheets("STF linked master").Range("I" & counter)
If rng.Value = "Y" Then
rng.Resize(,4).Copy
.Range("R5").PasteSpecial Paste:=xlPasteValues
.PrintOut Copies:=1, Collate:=True
End With
End If

Next counter
argeedblu
Hmmm.

I think your code would need to be more like:
CODE
Dim counter As Integer
Dim rng As Range
For counter = 1 To 10    'changed to 10 from 50 to reduce time!
Set rng = Sheets("STF linked master").Range("I" & counter + 11)
If rng.Value = "Y" Then
'rng.Resize(, 4).Copy
Set rng = Sheets("STF linked master").Range("L" & counter + 11 & ":" & "O" & counter + 11)
rng.Copy
.Range("R5").PasteSpecial Paste:=xlPasteValues
'.PrintOut Copies:=1, Collate:=True
End With
End If
Next counter
End Sub

to meet Ian's original specification, Norie.

Glenn
norie
Glenn

Oops!

Missed the +11 but I still think the resize is a little less cumbersome than concatenating to create the range to copy.
ian_john_walker
Thanks both, the original suggestion was much easier and even I can almost understand it!!!

Regards

Ian
KingMartin
Hi guys,

I know the issue has been solved, but we don't need to copy-paste, do we?

CODE
Const HOW_MANY_ROWS As Long = 10
Dim cl As Range
For Each cl In Sheets("STF linked master").Range("I12").Resize(HOW_MANY_ROWS)
If cl.Value = "Y" Then
Sheets("STF (linked)").Range("R5").Resize(4).Value = _
Application.Transpose(cl.Offset(, 3).Resize(, 4))
'.PrintOut Copies:=1, Collate:=True
End If
Next
norie
Martin

Why not? :(

I like copying and pasting, it's fun.
argeedblu
Resizing didn't include the cells the op requires (columns L - O). Resizing would include I - L, wouldn't it?

Glenn
norie
Glenn

That's right, especially without the +11 which I missed.

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.
KingMartin
Hi Glenn,

sorry for bringing even more confusion into this thread, but your question is directed at me?

This:

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

will return the range the OP wants, I think...
argeedblu
No, Martin,

My reply was to Norie.

Your code appears to better Excel VBA than mine.

Glenn
ian_john_walker
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
KingMartin
QUOTE
Your code appears to better Excel VBA than mine.

Well...

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

Martin
KingMartin
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

However, you may be interested in alternative coding, as norie and me posted... that's all...

Martin