newbie macro question

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!

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)

"Knox" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
>
>

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

"Bob Phillips" wrote:

> 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)
>
> "Knox" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
> >
> >
>
>
>

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



"Knox" wrote:

> 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
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "Knox" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!
> > >
> > >
> > >
> >
> >
> >

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!

"Knox" wrote:

> 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
>
>
>
> "Knox" wrote:
>
> > 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
> >
> > "Bob Phillips" wrote:
> >
> > > 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)
> > >
> > > "Knox" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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!
> > > >
> > > >
> > > >
> > >
> > >
> > >