0

I'm trying to create a Gantt chart with different colors based on the type of data entered, so first I must copy the data of certain type into a separate chart to assist with formatting. But I'm getting errors when I do so. The current error I"m getting is "Overflow". My goal is to copy the row of the data in which "PROD" appears in a cell into another sheet called "Gantt Calc" dynamically (since prior data may already be there). Here's the code:

 Sub test()
'
' test Macro
'


Dim i As Integer
Dim lastRow As Long

' Dim lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'sorting by start date oldest-newest code block
Range("A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Data Entry Page").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data Entry Page").AutoFilter.Sort.SortFields.Add _
        Key:=Range("D4:D8"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data Entry Page").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

For i = 4 To Rows.Count 'May need to calibrate lastRow to make sense
    If ActiveSheet.Cells(i, "B").Value = "PROD" Then Range(i, "A:F").Copy (Sheets(2).Range(i))

Next i

End Sub
camelCaseCowboy
  • 946
  • 1
  • 10
  • 26
  • 5
    Try changing i to a long instead of an integer. – Scott Craner May 02 '16 at 18:14
  • 1
    I highly recommend reading through [How to avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). This is a best practice and can really help you learn VBA better, as you can work directly with the data. – BruceWayne May 02 '16 at 18:37

0 Answers0