|
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! |
newbie macro question
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).C opy 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" wrote in message ... 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! |
newbie macro question
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).C opy 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" wrote in message ... 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! |
copying visible cells from a filtered worksheet in a repetitive lo
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).C opy 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).C opy 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" wrote in message ... 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! |
copying visible cells from a filtered worksheet in a repetitive lo
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).C opy 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).C opy 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" wrote in message ... 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! |
| All times are GMT +1. The time now is 03:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com