Macro to copy and paste based on criteria to 11 separate sheets

**msmith7113** · 04-10-2013, 09:57 PM

I am starting with an excel sheet with a data range of A1:CJ2500
The separating criteria is in Column C. The criteria is numerical: 1,2,3,4,5,6,7,8,9,10,11. Eleven is the last number criteria needed to separate.
The number of rows will vary for each numerical criteria
Each separate set of data needs to be pasted to A1 in sheet 1, sheet 2, sheet 3, etc to a max of 11 sheets.

There is an attachment for a visual. In the example rows 1 thru 21 need to go to sheet 1. Rows 22 through 105 need to go to sheet 2(pasted to A1)

I have included some code which will copy a range to sheet 1. However I don't know how to separate the data for separate sheets.

Sub Macro2()
'
Dim Rng1 As Range
Dim Sht As Worksheet
Set Rng1 = Range("A1:CI2000")
Set Sht = Sheets("Sheet1")
Select Case UCase(Range("C1").Value)
Case "1"
Sht.Range("A1:CI2000").Value = Rng1.Value
End Select
End Sub

Thank you for any help.

**rylo** · 04-10-2013, 10:12 PM

Hi

One way. Does assume that all 11 output sheets exist

Sub aaa()
  startrow = 1
  thisrow = 0
  For i = 1 To 11
    Do
      thisrow = thisrow + 1
    Loop Until Cells(thisrow, 3) <> i
    lastrow = thisrow - 1
    Range("A" & startrow & ":CI" & lastrow).Copy Destination:=Sheets("Sheet" & i).Range("A1")
    startrow = thisrow
  
  Next i
End Sub

rylo