VBA For Next Loop Selecting Wrong Cell after first loop

TripleBBB · Oct 20th 2021

I have written a script to go through and copy cells, paste them in multiple locations, then repeat the loop which is determined by the row count. The second time it goes through the loop it is supposed to select the same range as the first loop and then offset by an integer of 1 for each loop but it consistently copies the wrong cell on the 2nd loop and beyond.

Code

For x = 1 To NumRows
      
    Sheets("Worksheet").Select
    Range("B2").Activate
    Range("B2:K2").Offset(x, 0).Copy
    'Selection.Copy
    Sheets("PasteHere").Select
    Range("P1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
   Range("P1,t1,y1").Select
    Selection.Copy
    Sheets("Dashboard").Select
    Range("B65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("PasteHere").Select
    
    Range("P1,u1,y1").Select
    Selection.Copy
    Sheets("Dashboard").Select
    Range("B65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("PasteHere").Select
    
    Range("P1,v1,y1").Select
    Selection.Copy
    Sheets("Dashboard").Select
    Range("B65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("PasteHere").Select
    
    Range("P1,w1,y1").Select
    Selection.Copy
    Sheets("Dashboard").Select
    Range("B65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("PasteHere").Select
    
    Range("P1,x1,y1").Select
    Selection.Copy
    Sheets("Dashboard").Select
    Range("B65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("PasteHere").Select
    Range("P1").Select
    Application.CutCopyMode = False
         ' Selects cell down 1 row from active cell.
    Next

Display More

Where it says

Code

 Range("B2:K2").Offset(x, 0).Copy

It works perfectly the 1st time and then the 2nd time and beyond it ends up Copying cell P1 for some reason.

I'm beyond frustrated...please help!

**royUK** · Oct 20th 2021

Attach an example workbook

TripleBBB · Oct 20th 2021

Here is a sample file.

TripleBBB · Oct 20th 2021

I haven't written VBA script in almost 10 years so I am definitely rusty.

**royUK** · Oct 20th 2021

Your code needs a lot of tidying up.

What exactly are you trying to do?

TripleBBB · Oct 20th 2021

I need to grab any entries in Columns F - J on "Worksheet" and then I need to move them into a single column while also grabbing the TimeStamp in Column B.

In the example data I provided, this list would look like this.

ColumnA Column B

B11 F11

B39 F39

B39 G39

And so on.

VBA For Next Loop Selecting Wrong Cell after first loop

Participate now!

Share