Optimizing Code

[tqn626]

I have this code i'm using to extract multiple tabs into one. Is there a better way to run this? Any help would be appreciated.

Sub PullData()
Dim i As Double
Dim wscount As Double
Dim imaxrow As String
Dim Datastart As String
Dim lowrow As String
Dim uprow As String
Dim x As Double
Dim Ship As Double
Dim ShipCount As String




wscount = ActiveWorkbook.Worksheets.Count


'Clear Current Data
Worksheets("Raw Data").Range("A2:R5000").Clear
'Range("a2:h20000").Select
'Selection.Clear
'Range("a2").Select


'Starting Worksheet to End
Datastart = Worksheets("Raw Data").Range("u6").Value


For i = Datastart To wscount


    'Rows of data to be extracted
    lowrow = Worksheets("raw data").Range("u7").Value
    uprow = Worksheets("raw data").Range("u8").Value
    'Use to offset columns to next dataset
        For Each e In Array(117, 120, 123, 126, 129, 132, 135, 138, 141, 144, 147, 150)
        
        'Starting row to copy data
            For irow = lowrow To uprow
                If Worksheets(i).Visible Then
                
                    If Worksheets(i).Cells(irow, 1).Value = "" Or Worksheets(i).Cells(irow, 1).Value = 0 Then
                        'do nothing
                        
                        Else
                        If Worksheets(i).Cells(irow, 3).Find("Total") Is Nothing Then
                 
                            With Worksheets("Raw Data")
                     
                        
                             'Team
                            .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(1, 6).Address
                            'Sales Lead
                            .Cells(Rows.Count, "b").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(1, 3).Address
                            'Customer
                            .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(2, 3).Address
                            'Price Code
                            .Cells(Rows.Count, "d").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(2, 6).Address
                            'Classification
                            .Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(irow, 6).Address
                              'Category
                            .Cells(Rows.Count, "f").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(irow, 2).Address
                              'Product Code
                            .Cells(Rows.Count, "g").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(irow, 3).Address
    
                            '# Stores
                            .Cells(Rows.Count, "l").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(irow, e + 36).Address
                             '2020 Forecast
                            .Cells(Rows.Count, "m").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(irow, e + -77).Address
                            '2019 Cases
                            .Cells(Rows.Count, "n").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(irow, e + -78).Address
                               '2020 AOP $
                            .Cells(Rows.Count, "o").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(irow, e + -40).Address
                               '2019 $
                            .Cells(Rows.Count, "p").End(xlUp).Offset(1, 0) = "='" & Worksheets(i).Name & "'!" & Worksheets(i).Cells(irow, e + -41).Address
                            
                            
                             End With
                             'Next Ship
                             'Else
                        End If
                    End If
                'Worksheet not visble so do nothing
                
                End If
            Next irow
            
        Next e
    Next i
End Sub

 

[tqn626]

I tried doing the cell value. But because of the offset, if a value is missing it throws all the numbers off.

                            .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Worksheets(i).Cells(1, 6).Value