Sub CopyCellsDown(StartRange As Range)

 

  Dim targetRange As Range

 

  ' the startRange should be a range object that points to the first cell

  ' this is the only cell that you have to set - everything else is automatic

  ' the call will look like: CopyCellsDown (ActiveSheet.Range("H2"))

   

  ' looks complicated - but only just using offset to specify the start and

  ' end of the range we want to pass to the autofill.

  Set targetRange = Range(StartRange, StartRange.Offset(0, -1).End(xlDown).Offset(0, 1))

 

  'this selects the starting cell

  StartRange.Select

 

  'this copies down the value down to as many rows as the column on the left

  Selection.AutoFill Destination:=targetRange

End Sub

 

Sub refresh_SFTBL2()

'it is find Dec-21 and copy until it

Dim SFPLanValue

 SFPLanValue = InputBox("Type name of plan", "Plan", "SF")

 

 For i = 0 To 100

        Debug.Print ""

    Next i

 

Set wbTh = ThisWorkbook

Set wbSF = GetObject(lastSF22book())

 

Dim rowL

Dim rowU

'rowOfData

Set Rng = wbTh.Worksheets("SF TBL2").ListObjects("Table6").Range

'MsgBox Col_Letter(Rng.Columns(Rng.Columns.Count).Column + 1)

'.Range(Col_Letter(Rng.Columns(Rng.Columns.Count).Column + 1) & ":I").Clear

'MsgBox wbTh.Worksheets("WK TBL").ListObjects("Table3").DataBodyRange.Address

'wbTh.Worksheets("Dashboard").ListObjects("Table5").DataBodyRange.Clear

 

  Dim sArray(4) As String

    Dim element As Variant

    Dim RangeCell

 sArray(0) = "Kit": sArray(1) = "HTS": sArray(2) = "SLU": sArray(3) = "Other": sArray(4) = "P4"

 

 

 With wbTh.Worksheets("SF TBL2")

    .Activate

    Set Macro_ParameterCell = .Range("1:1").Find(What:="Parameter", LookIn:=xlValues)

        If Macro_ParameterCell Is Nothing Then

            MsgBox ("Ooooooopppps")

              End If

 End With

      'Debug.Print Rng.Rows(Rng.Rows.Count).Row + 1

      'rowU = Rng.Rows(Rng.Rows.Count).Row + 1

      'Exit Sub

     'rowU = Macro_ParameterCell.Row + 1

'''''''''''''

    For Each element In sArray

   

    Set Rng = wbTh.Worksheets("SF TBL2").ListObjects("Table6").Range

    rowU = Rng.Rows(Rng.Rows.Count).Row + 1

   

        'If element = "SLU" Then Exit For

   

        Debug.Print element

        Debug.Print "!"

        With wbSF.Worksheets(element)

        .Activate

        If .AutoFilterMode = True Then  'add autofilter if doesn't exist

 

        'Do Nothing

       

        Else

       

        .Range("A2").AutoFilter

       

        End If

       

              On Error Resume Next

               'Cells.AutoFilter

If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData

On Error GoTo 0

 

        Set FindCell = .Range("2:2").Find(What:="Parameter", LookIn:=xlValues)

        If FindCell Is Nothing Then

            MsgBox ("Ooooooopppps")

        End If

       

         

             Set FindCell22 = .Range("2:2").Find(What:="Dec-21", LookIn:=xlValues)  'date Dec-21

        If FindCell22 Is Nothing Then

            MsgBox ("Ooooooopppps")

        End If

               .Columns(4).AutoFilter Field:=FindCell.Column, Criteria1:=Array( _

        "IMS", "Offtakes", "Shipment"), Operator:=xlFilterValues     'Columns(4) stands for nothing

        'Exit Sub

         'Exit Sub

      '   MsgBox FindCell22.Column

          rowL = .Cells(.Rows.Count, FindCell.Column).End(xlUp).Row

         

            Set RangeCell = .Range(.Cells(FindCell.Row + 1, FindCell.Column), .Cells(rowL, FindCell22.Column))

        End With

       

        RangeCell.Copy

        Debug.Print RangeCell.Address

        ''''''''''''''''''''''''''''''''''''''''''''''''

        'wbTh.Worksheets("Dashboard").ListObjects("Table5").Resize Range("A1:AF3")

       

    With wbTh.Worksheets("SF TBL2")

    .Activate

    Set Macro_ParameterCell = .Range("1:1").Find(What:="Parameter", LookIn:=xlValues)

        If Macro_ParameterCell Is Nothing Then

            MsgBox ("Ooooooopppps")

        End If

        RangeCell.Copy

        'MsgBox Macro_ParameterCell.Row '.Cells(Macro_ParameterCell.Row+1, Macro_ParameterCell.Column)

        .Range(.Cells(rowU, Macro_ParameterCell.Column), .Cells(rowU, Macro_ParameterCell.Column)).PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _

        , SkipBlanks:=False, Transpose:=False

        .Range(.Cells(rowU, Macro_ParameterCell.Column), .Cells(rowU, Macro_ParameterCell.Column)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    End With

Application.CutCopyMode = False

 

       

       

       

       

        'Exit Sub

   

''''        With wbTh.Worksheets("Dashboard")

''''

''''            'rowU = rowU + RangeCell.Rows.Count

''''            'rowU + 250

''''            'wbTh.Worksheets("Dashboard").Cells(.Rows.Count, Macro_ParameterCell.Column).End(xlUp).Row + 1

''''

''''        End With

       Debug.Print "rowU"

       Debug.Print rowU

       Debug.Print SFPLanValue

          'Exit Sub

           wbTh.Worksheets("SF TBL2").Range("B" & rowU).Value = "=""" & SFPLanValue & """"

           CopyCellsDown (wbTh.Worksheets("SF TBL2").Range("B" & rowU))

           'wbTh.Worksheets("SF TBL2").Range("B" & rowU).FillDown

    Next element

''''''''''''

    Debug.Print "Done"

With wbTh.Worksheets("SF TBL2")

  Do While True

    Set FindCell3 = .Range("F:F").Find(What:="Total", LookIn:=xlValues)

        If FindCell3 Is Nothing Then

            Exit Do

        End If

     .Rows(FindCell3.Row).Delete

  Loop

End With

 

  Debug.Print "Done 2 "

 

    Exit Sub

 

 

''''     wbTh.Worksheets("Dashboard").Range("B2").Value = "=VLOOKUP([@Category]&[@Version]&[@SKU],Library!A:B,2,0)"

''''     wbTh.Worksheets("Dashboard").Range("C2").Value = "=VLOOKUP([@Parameter],Library!C:D,2,0)"

       

    ' =VLOOKUP(A2,'[" & sbs_name & "]" & sh_wbSbS.Name & "'!$A:$" & Col_Letter(FindCell.Column) & "," & FindCell.Column & ",0)"

'''  '.Range(v_SF_emptyWK_cell.Address).Value = "=VLOOKUP(A2,[" & sbs_name & "]" & sh_wbSbS.Name & "!$A:$" & Col_Letter(FindCell.Column) & "," & FindCell.Column & ",0)"

'''  .Range(v_SF_emptyWK_cell.Address).AutoFill .Range(v_SF_emptyWK_cell, v_SF_emptyWK_fillTo_cell)

'''

 

'''With wbSF.Worksheets("Supply")

'''    rowL = .Cells(.Rows.Count, 4).End(xlUp).Row

'''End With

'''wbSF.Worksheets("Supply").Range("A6:AW" & rowL).Copy

'''

'''wbTh.Worksheets("WK TBL").Range("B2").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _

'''        , SkipBlanks:=False, Transpose:=False

'''    wbTh.Worksheets("WK TBL").Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

'''        :=False, Transpose:=False

'''Application.CutCopyMode = False

''''MsgBox wbSF.Worksheets("Supply").Range("A6:BQ" & rowL).Address

'''Exit Sub

'''wbTh.Worksheets("WK TBL").Range("B2:BR596").Value = wbSF.Worksheets("Supply").Range("A6:BQ600").Value

'''wbTh.Worksheets("WK TBL").Activate

'''wbTh.Worksheets("WK TBL").Range(Col_Letter(Rng.Columns(Rng.Columns.Count).Column + 1) & ":BR").Select

'''    Application.CutCopyMode = False

'''    Selection.Delete Shift:=xlToLeft

''''Exit Sub

'''MsgBox wbSF.Name

End Sub