I am trying to write a macro for excel where i need to copy data from one
worksheet to another based upon a filtered result from the sheet which has
data. The macro should repeat the filter criteria with changed setting and
paste into different cells. To make myself more clear I am pasting 3
instances of the macro I need for the loop.

'Selects Cell A2 in the blank sheet (this cell will change everytime by
20, because there can be max of 20 results from the filter
criteria)
Range("A2").Select
Sheets("Unarranged Data").Select
Application.CutCopyMode = False
'Criteria1 changes everytime from 1:1 to 1:40, then 2:1 to 2:40, then
3:1 to 3:40 and so on till 9:1 to 9:40
Selection.AutoFilter Field:=6, Criteria1:="1:1"
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Arranged Data1").Select
ActiveSheet.Paste

I need to know whether a loop for this kind of thing can be made if so
please help, otherwise do I need to write the code 360 times or what?

I did come up with a change in the above code but I need to know he errors I
am making and probably some solution to that error


Dim Sectx As Integer
Dim Secty As Integer
Sectx = 1
Do
Secty = 1
Do

'I am getting an error on this line, what this code does is that it
changes
'selection of cell from A2, A22,A42,A82,A102....so on
Set curcell = Worksheets("Arranged Data1").Cells((((Sectx - 1) * 40)
+ ((sety - 1) * 20) + 2), 1)
Sheets("Unarranged Data").Select
Application.CutCopyMode = False
'Filters using conditions 1:1, 1:2...1:40,
2:1,2:2...2:40,...,9:1....9:40
Selection.AutoFilter Field:=6, Criteria1:=(Sectx & ":" & Secty)
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Arranged Data1").Select
ActiveSheet.Paste

Secty = Secty + 1
Loop While Secty = 40

Sectx = Sectx + 1
Loop While Sectx = 9

Addy wrote:

I did come up with a change in the above code but I need to know he errors I
am making and probably some solution to that error


Dim Sectx As Integer
Dim Secty As Integer
Sectx = 1
Do
Secty = 1
Do

'I am getting an error on this line, what this code does is that it
changes
'selection of cell from A2, A22,A42,A82,A102....so on
Set curcell = Worksheets("Arranged Data1").Cells((((Sectx - 1) * 40)
+ ((sety - 1) * 20) + 2), 1)
Sheets("Unarranged Data").Select
Application.CutCopyMode = False
'Filters using conditions 1:1, 1:2...1:40,
2:1,2:2...2:40,...,9:1....9:40
Selection.AutoFilter Field:=6, Criteria1:=(Sectx & ":" & Secty)
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Arranged Data1").Select
ActiveSheet.Paste

Secty = Secty + 1
Loop While Secty = 40

Sectx = Sectx + 1
Loop While Sectx = 9


Hi Addy

I am not quite sure if this is what you want to do
But hope it helps

Private Sub CommandButton1_Click()
Dim a
Dim b
Dim c
a = 1
b = 1
c = 2 'refers to A2
Do Until c 100 ' your highest cell number
For i = a To 9
For s = b To 40
If Cells(c, 1).Text = i & ":" & s Then 'this will go through the
criterias
'do your selection and copy in this part
End If
Next s
Next i
c = c + 20
Loop

End Sub

I tried to preserve most of your existing code. Try something like this:

Dim intX as Integer
Dim intY As Integer
Dim lngRow As Long

lngRow = 2
For intX = 1 To 9
For intY = 1 To 40
Range("A1").Offset(lngRow - 1, 0).Select
Sheets("Unarranged Data").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=6, Criteria1:=intX & ":" & intY
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Arranged Data1").Select
ActiveSheet.Paste
lngRow = lngRow + 20
Next
Next

I presumed that you started from the "blank sheet" because you selected cell
A2 and then went to the "Unarranged Data" sheet. If that is not the case, I
would make this change:

Move the line:
Range("A1").Offset(lngRow - 1, 0).Select
After:
Sheets("Arranged Data1").Select


"Addy" wrote:

I am trying to write a macro for excel where i need to copy data from one
worksheet to another based upon a filtered result from the sheet which has
data. The macro should repeat the filter criteria with changed setting and
paste into different cells. To make myself more clear I am pasting 3
instances of the macro I need for the loop.

'Selects Cell A2 in the blank sheet (this cell will change everytime by
20, because there can be max of 20 results from the filter
criteria)
Range("A2").Select
Sheets("Unarranged Data").Select
Application.CutCopyMode = False
'Criteria1 changes everytime from 1:1 to 1:40, then 2:1 to 2:40, then
3:1 to 3:40 and so on till 9:1 to 9:40
Selection.AutoFilter Field:=6, Criteria1:="1:1"
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Arranged Data1").Select
ActiveSheet.Paste

I need to know whether a loop for this kind of thing can be made if so
please help, otherwise do I need to write the code 360 times or what?