1

I'm just learning VBA and I wanted to know cause i made some code for copying down from excel sheet that have been filtered but are still not able to do it

  Sub Compile_SKU_Inventory_Summary()

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False


 Dim Vfile As Variant
 Dim wbto As Workbook
 Dim ws, wsfromwhs1, WScompSKU, WSpoJKT, WSpoSBY, WSpoMDN, WSpoBPN, WSpoMKS, WSpoSMG, WSpoPLB,                                                 WSpoDPR, WSpoPBR As Worksheet
 Dim lrowSBY2, lrowSKU, lrowJKT, lrowSBY, lrowMDN, lrowBPN, lrowMKS, lrowSMG, lrowPLB, lrowDPR,    lrowPBR, lrowt4 As Long
 Dim lrowpotab, lrow1, lrow2, lrowws, lrowMDN2, lrowBPN2, lrowMKS2, lrowSMG2, lrowPLB2, lrowDPR2, lrowPBR2, lrowcom, lrowcom1, lrowpotab1, lrow7, lrow8 As Long
 Dim h, i, j, k, z, a As Integer
 Dim sheetname As String
 Dim headercolumn As String

   Set wbto = ActiveWorkbook
   Set WScompSKU = wbto.Worksheets("Compile_SKU")


  'Delete all compile sku
   WScompSKU.Activate

  Range("A2:B2").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.ClearContents

  Set wsfromwhs = wbto.Worksheets("WH_Split1")


  wsfromwhs.Activate


  h = 2

  Do

      flag = False

      sheetname = "PO_Tab_" & wsfromwhs.Cells(1, h).Value
      j = 1

      Do
      For Each ws In Worksheets
          If ws.Name = sheetname Then flag = True
          'lrowpotab = Sheets(sheetname).Cells(Rows.Count, 1).End(xlUp).Row

       If flag = True Then
       Set wsfromwhs = Sheets(sheetname)
       wsfromwhs.Calculate
       lrowpotab = Sheets(sheetname).Cells(Rows.Count, 1).End(xlUp).Row
       lrowcom = WScompSKU.Cells(Rows.Count, 2).End(xlUp).Row
       lrowcom1 = lrowcom + 2
       lrowpotab1 = lrowpotab + 1
        
              Sheets(sheetname).Range("A3:AM" & lrowpotab).AutoFilter field:=39, Criteria1:=">0",         Operator:=xlFilterValues
        
                lrow7 = Cells(Rows.Count, 1).End(xlUp).Row

      If lrow7 = 1 Then GoTo Skip1:


      lrow8 = Cells(Rows.Count, 1).End(xlUp).Row
      Worksheets(sheetname).Activate
      With ActiveSheet.Range(Cells(3, 1), Cells(lrow8, 1)).Select
      Selection.Copy

      WScompSKU.Range("B2:B" & lrowcom).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,       SkipBlanks:=False, Transpose:=False


  Skip1:
  Worksheets("Compile_SKU").Activate
  With ActiveSheet
      .AutoFilterMode = False

    End If
  h = h + 1
   Loop While wsfromwhs.Cells(1, h).Value <> ""

  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  End Sub

I encounter that everytime i try to run it, the vba code says that there is no if for end if, or no "for" for the Loop while. even that i have not been able to copy all the filtered value as well

melearning123
  • 11
  • 2
  • 1
    Does this answer your question? [Copy filtered data to another sheet using VBA](https://stackoverflow.com/questions/39121960/copy-filtered-data-to-another-sheet-using-vba) – braX Feb 17 '23 at 08:57
  • 1
    You have this: `For Each ws In Worksheets` but you have no matching `Next`. Looks like you are trying to mix it in with a `Do...Loop`?? Is that intentional? You can also use that same `If` statement instead of your `GoTo` - `GoTo` is considered bad practice. – braX Feb 17 '23 at 09:08
  • You also have `With` statements, but no `End With` statements..? – CLR Feb 17 '23 at 09:29
  • 1
    In VBA *every variable* needs a type specified: if you write (eg) `Dim A, B, C As Long` then `A` and `B` default to `Variant` and only `C` is a `Long` – Tim Williams Feb 17 '23 at 17:11

0 Answers0