earthworm
Well-known Member
- Joined
- May 19, 2009
- Messages
- 759
- Office Version
- 2019
- 2016
- Platform
- Windows
I want the below code to repeat 38 times . I have also mapped all variable reference but still its not working
please help what am i doing wrong in this code
please help what am i doing wrong in this code
VBA Code:
Sub Split8()
Windows("Raw Report Under Construction.xlsb").Activate
Sheets("DB").Select
Dim x As Integer
For x = 1 To 38
Dim Filter As String
Filter = Range("G" & x).Value
Dim Filename As String
Filename = Range("H" & x).Value
Dim reference As Integer
reference = Range("I" & x).Value
Sheets("DB").Select
If reference > 0 Then
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AF$26443").AutoFilter Field:=4, Criteria1:=Filter
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.Columns.AutoFit
Columns("A:A").Select
Selection.NumberFormat = "General"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R2C[3]:RC[3],RC[3])"
Range("A2").Copy
Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).PasteSpecial
Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
ActiveWindow.FreezePanes = True
Range("A1").Select
Sheets("Sheet1").name = Filter
ChDir "C:\Users\iiiii\OneDrive\Desktop\Test"
ActiveWorkbook.SaveAs Filename:=Filename, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Windows("Raw Report Under Construction.xlsb").Activate
Range("A1").Select
Selection.AutoFilter
End If
Next x
End Sub