Help making a Do-While loop... Loop

[vulcanfury]

Hi all,

I'm having trouble making this loop iterate:

Do While Cells(96, DtlNxtCol + 2).Value > 0
    Sheets("Detail_Sheet").Select
    Cells(10, DtlNxtCol).Copy
    Sheets("Summary").Select
    Cells(SumNxtRow, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("Detail_Sheet").Select
    Cells(5, DtlNxtCol).Copy
    Sheets("Summary").Select
    Cells(SumNxtRow, 2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Detail_Sheet").Select
    Cells(6, DtlNxtCol).Copy
    Sheets("Summary").Select
    Cells(SumNxtRow, 3).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("Detail_Sheet").Select
    Cells(7, DtlNxtCol).Copy
    Sheets("Summary").Select
    Cells(SumNxtRow, 4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("Detail_Sheet").Select
    Cells(8, DtlNxtCol).Copy
    Sheets("Summary").Select
    Cells(SumNxtRow, 5).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("Detail_Sheet").Select
    Cells(9, DtlNxtCol).Copy
    Sheets("Summary").Select
    Cells(SumNxtRow, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("Detail_Sheet").Select
    Cells(96, DtlNxtCol).Copy
    Sheets("Summary").Select
    Cells(SumNxtRow, 7).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
     DtlNxtCol = DtlNxtCol + 2
     SumNxtRow = SumNxtRow + 1
Loop

Basically, it copies some of the values in the columns in one sheet (Detail_Sheet), then pastes those values one by one in rows to another sheet (Summary). It's similar to transposing a column.

What I want to do is to ensure that this keeps happening until I encounter a blank column in Detail_Sheet. I try and I try and I try but it just keeps going over the loop once. I tried using a For loop, just to see if I can make it print multiple rows, and it does, but to do what I want, I need to do a Do-While Loop. Please help!

EDIT:
I figured it out. It seems all I needed to do was to select Detail_Sheet again at the end of the loop.

 

[AlphaFrog]

Just a quick tip; no need to select sheets or cells to copy\paste.

    Sheets("Detail_Sheet").Cells(10, DtlNxtCol).Copy

    Sheets("Summary").Cells(SumNxtRow, 1).PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[Peter_SSs]

Further to AlphaFrog's comments, note that all the selecting of sheets/cells slows your code.
Also, you can transfer values directly and possibly deal with multiple cells at once.
See how this goes.

Do While Sheets("Detail_Sheet").Cells(96, DtlNxtCol + 2).Value > 0
  Sheets("Summary").Cells(SumNxtRow, 1).Value = Sheets("Detail_Sheet").Cells(10, DtlNxtCol).Value
  Sheets("Summary").Cells(SumNxtRow, 2).Resize(, 5).Value = Application.Transpose(Sheets("Detail_Sheet").Cells(5, DtlNxtCol).Resize(5).Value)
  Sheets("Summary").Cells(SumNxtRow, 7).Value = Sheets("Detail_Sheet").Cells(96, DtlNxtCol).Value
  
  DtlNxtCol = DtlNxtCol + 2
  SumNxtRow = SumNxtRow + 1
Loop

 

[Peter_SSs]

... and just for the record, if you wanted to get fancy you can do a whole column to row at once, though I'm not necessarily suggesting you do this.

Dim aRws As Variant

aRws = Application.Transpose(Array(10, 5, 6, 7, 8, 9, 96))

Do While Sheets("Detail_Sheet").Cells(96, DtlNxtCol + 2).Value > 0
  Sheets("Summary").Cells(SumNxtRow, 1).Resize(, 7).Value = Application.Transpose(Application.Index(Sheets("Detail_Sheet").Cells, aRws, DtlNxtCol))
  
  DtlNxtCol = DtlNxtCol + 2
  SumNxtRow = SumNxtRow + 1
Loop

 

[Rick Rothstein]

Further to AlphaFrog's comments, note that all the selecting of sheets/cells slows your code.
Also, you can transfer values directly and possibly deal with multiple cells at once.
See how this goes.

Do While Sheets("Detail_Sheet").Cells(96, DtlNxtCol + 2).Value > 0
  Sheets("Summary").Cells(SumNxtRow, 1).Value = Sheets("Detail_Sheet").Cells(10, DtlNxtCol).Value
  Sheets("Summary").Cells(SumNxtRow, 2).Resize(, 5).Value = Application.Transpose(Sheets("Detail_Sheet").Cells(5, DtlNxtCol).Resize(5).Value)
  Sheets("Summary").Cells(SumNxtRow, 7).Value = Sheets("Detail_Sheet").Cells(96, DtlNxtCol).Value
  
  DtlNxtCol = DtlNxtCol + 2
  SumNxtRow = SumNxtRow + 1
Loop

This comment is for 'vulcanfury', but I am going to use Peter's code because it is more compact making it easier to highlight the parts of the code I want to refer to. I just want to point out that the red highlighted part of your code looks "strange" to me. At the bottom of your loop in the green highlighted code line, you increment DtlNxtCol by 2, then after doing that, you check to see if the column at Row 96 two over from it is empty... normally I would expect you to check if the cell in the column you just incremented to is empty, but no, you look two more columns over and check that cell to see if it is blank. Doing it that way, the column you were about to process could have data in it but that data won't be looked at because the loop will stop if the column two over from it is empty. Now I can think of data layouts where what you are doing would be fine, but I don't think they are the norm, so I figured I would mention my observation just in case your code is not doing exactly what you intended.

 

[vulcanfury]

Thanks for the comments guys, I'll implement the code to make it more compact. This version was made primarily by recording a macro that does what I wanted to do (I rarely touch VBA).

To Rick:
Thanks for that comment! I almost missed that!

 

[Peter_SSs]

This version was made primarily by recording a macro that does what I wanted to do (I rarely touch VBA).

That's what I assumed and why post #4 is not appropriate for you - (yet )
Even if you did each individual cell with a statement like this it would be shorter, simpler & more efficient than what you have.

Sheets("Summary").Cells(SumNxtRow, 1).Value = Sheets("Detail_Sheet").Cells(10, DtlNxtCol).Value