lyp_sj


Hello,

Below is my code, the lastrowGSVGrowthRate is 101, I expected result is to copy all the "AFFILIATE" items. While with my code, it only copied the first "AFFILIATE" item. I do not know where I did wrong. Thanks so much!

B.regards/Yaping

raffiliate = 1
rowaffiliate = 1

Do While raffiliate <= lastrowGSVGrowthRate
If cells(raffiliate, 1) = "AFFILIATE" Then
MsgBox raffiliate
Range(cells(raffiliate, 1), cells(raffiliate, lastcolumnGSVGrowthRate)).Select
Selection.Copy
worksheets("AFFILIATE").Activate
ActiveSheet.cells(rowaffiliate, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll
rowaffiliate = rowaffiliate + 1
Else
End If
raffiliate = raffiliate + 1
Loop





Re: Why the do while loop does not work?

Navajo


It's because the second time round the loop the code is referencing cells on the active sheet (AFFILIATE), not the original sheet.

Try to use Worksheet objects, then you don't have to keep activating different sheets.
Also the Else clause isn't needed as there's nothing in it.





Re: Why the do while loop does not work?

Shasur

Instead of

Range(cells(raffiliate, 1), cells(raffiliate, lastcolumnGSVGrowthRate)).Select
Selection.Copy
worksheets("AFFILIATE").Activate
ActiveSheet.cells(rowaffiliate, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll

Use

Range(Cells(raffiliate, 1), Cells(raffiliate, lastcolumnGSVGrowthRate)).Copy Destination:=Worksheets("AFFILIATE").Sheets(1).Cells(rowaffiliate, 1)

This will have the control in the activesheet and still copies the contents to Affiliate sheet

Cheers

Shasur







Re: Why the do while loop does not work?

lyp_sj

Got it, thanks so much!