VBA Loop Help

[playern07]

Hello,

I am trying to write a code that will loop through a set of data and copy certain cells in each row. It is to paste the certain cells into another spreadsheet in the same workbook.

Ex. If I run a query and it pulls back 20 rows of data into sheet1 the code should cycle through all 20 rows, copy columns C-G (excluding E), and paste them in sheet4.

Here is my code so far (I get an overflow error):

Sub Loop6()
' This loop repeats for a fixed number of times determined by the number of rows
' in the range
Dim j As Integer
For j = 1 To Selection.CurrentRegion.Rows.Count - 1
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Rows(j).Copy Rows(j + 1)

Sheet4.Select
Next j
End Sub

 

[Jonmo1]

Dim j As Long

Integer is limited to 32,767

Any number higher than that results in overflow error.

 

[playern07]

Ok, I used dim i on another module. Does this matter if I call upon it again in a different module?

 

[Jonmo1]

It depends on what you're using the variable for...

If the variable will never hold a value greater than 32,767, then Integer is fine.
But when the variable represents row #s, the potential for larger numbers is present(there are 65536 rows in xl2003, million+ in 2007) so use Long.

 

[playern07]

Thank you, this took care of my overflow error. Now it seems the code does not loop for visible cells only, but rather all cells. How do I address this?

 

[playern07]

I added a few more conditions, but it still selects all cells and not visible cells only. Any Ideas?

Code:
Dim j As Long
For j = 1 To Selection.CurrentRegion.Rows.Count - 1

Range("A1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.EntireRow.Hidden = True
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Rows(j).Copy Rows(j + 1)

Sheet3.Select
Next j
End Sub

 

[playern07]

I have managed to create a code that pastes rows to the destination sheet, but it still does not work 100% like I would like. Ideally I want my could to copy the 1st visible row of a filtered data set and paste it into the destination sheet. IO would like the code to then go to the next visible row and paste it to the destination sheet as well. When there are no more visible cells to paste out the cycle comes to an end.

Dim j As Long
Sheet1.Select
Selection.SpecialCells(xlCellTypeVisible).Select
For j = 2 To Selection.CurrentRegion.Rows.Count - 1
If Cells(Columns("A").Rows.Count, "A").End(xlUp).Row > 2 Then
Range(Selection, Cells(Columns("A").Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).Select

End If
Do
Loop While ActiveCell.EntireRow.Hidden = True

Range("a2:a" & Cells(Rows.Count, "a").End(xlUp).Row).SpecialCells (xlCellTypeVisible)
Rows(j + 1).Copy _
Destination:=Worksheets("sheet4").Cells(Rows.Count, 1).End(xlUp)
Next j

Any help is greatly appreciated.

 

[playern07]

This code copies all visible rows to my destination sheet, but does not cycle row by row. It takes everything at one time.


Dim j As Long
Sheet1.Select
Selection.SpecialCells(xlCellTypeVisible).Select
For j = 2 To Selection.CurrentRegion.Rows.Count - 1
If Cells(Columns("A").Rows.Count, "A").End(xlUp).Row > 2 Then
Range(Selection, Cells(Columns("A").Rows.Count, "H").End(xlUp)).SpecialCells(xlCellTypeVisible).Select
Selection.Copy _
Destination:=Worksheets("sheet4").Cells(Rows.Count, 1).End(xlUp)

End If

 

[playern07]

Bump