Macro help

[funkyfido]

I have a large spreadsheet where all the data comes out in the one go. I want to do the following:-



Find CY and copy all the data (columns A-L) up to DL but not including DL and put it on a new sheet named CY
Find DL and copy all the data up to EU but not including EU and put it on a new sheet named CY
Find EU and copy remaing data into a new sheet named EU.

How would I do this? I am fairly new to macros so any help would be appreciated. I have attached a test copy of my data.

[macropod]

Try:

Sub Demo()
Application.ScreenUpdating = False
Dim lRow As Long, lCol As Long, i As Long, j As Long
Dim xlWkBk As Workbook, xlSht As Worksheet, StrSht As String
Set xlWkBk = ThisWorkbook: StrSht = ""
Set xlSht = xlWkBk.Worksheets("Sheet1")
With xlWkBk.Worksheets("Sheet1").UsedRange
  lRow = .Range("A" & .Cells.SpecialCells(xlCellTypeLastCell).Row).End(xlUp).Row
  lCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
  For i = 1 To lRow
    Select Case .Cells(i, 1).Text
      Case "CY"
        StrSht = "CY": j = 0
        Set xlSht = xlWkBk.Sheets.Add(After:=xlSht)
        xlSht.Name = StrSht
      Case "DL"
        StrSht = "DL": j = 0
        Set xlSht = xlWkBk.Sheets.Add(After:=xlSht)
        xlSht.Name = StrSht
      Case "EU"
        StrSht = "EU": j = 0
        Set xlSht = xlWkBk.Sheets.Add(After:=xlSht)
        xlSht.Name = StrSht
    End Select
    If StrSht <> "" Then
      j = j + 1
      .Range(.Cells(i, 1), .Cells(i, lCol)).Copy
      xlSht.Paste Destination:=xlSht.Cells(j, 1)
    End If
  Next
End With
Application.ScreenUpdating = True
End Sub

[funkyfido]

This gave me some errors but I managed another workaround

[macropod]

Unless your data and/or the worksheet itself are different to what you posted, I can't see how that's possible. The macro was coded, tested and confirmed as working correctly with the worksheet you posted.

Finally, merely saying "This gave me some errors" without saying what they were robs yourself of any chance of getting the problem solved, so all you're left with is a 'workaround'.