newbie macro question

[Guest]

I created the macro below:

Selection.AutoFilter Field:=72, Criteria1:="4"
Columns("BR:BW").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Range("G26").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("2%").Select
Range("K6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H26").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("2%").Select
Range("I6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Basically I want to create a repetitive loop to increase the "4" in the
first line by one each time to 5, 6, 7.... till 340. Each time it is
increased the K6 and I6 values would also increase by one to K7 I7, K8 I8,
etc. Any help on how to do this? thank you!

 

[Bob Phillips]

Try this

Dim i As Long
Dim startWS As Worksheet
Dim startRange As Range

Set startWS = ActiveSheet
Set startRange = Selection

For i = 4 To 340
startRange.AutoFilter Field:=72, Criteria1:=CStr(i)
Columns("BR:BW").SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Range("G26").Select
Selection.Copy
Sheets("2%").Select
Range("K" & i + 2).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Sheets("Sheet1").Select
Range("H26").Copy
Sheets("2%").Select
Range("I" & i + 2).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Next i
Application.CutCopyMode = False


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you, I think it almost worked. The only problem was when it copy and
pasted the two values from the "sheet1" worksheet to the "2%" worksheet the
values were zero. I think the problem lies with the first copy and paste
from the first worksheet to sheet1. After this occurs, the macro should read
the values from G26 and H26 in sheet1 and it will not be zero unless the
first copy and paste didn't work. The origninal macro did not have this
problem. Any help would be appreciated. thanx again

 

[Guest]

I know the problem lies with copying visible cells from a filtered worksheet
in a repetitive loop. It does it correctly the first iteration but not after
that. During the 2nd iteration and on it selects all the cells in columns
BR:BW that are below the data. Is there something that can be done to the
third line below to fix it?

For i = 4 To 340
startRange.AutoFilter Field:=72, Criteria1:=CStr(i)
Columns("BR:BW").SpecialCells(xlCellTypeVisible).Copy

thanx

 

[Guest]

Nevermind I figured it out! You have to make a selection of a cell in the
origingal worksheet at the end of the loop. thanx for the help!