-
VBA
Hello
I have quick question to ask i am getting stuck trying to do this in vba.
I have Column A that has many cell that different amount. I am trying duplicated this in vba with loop
Thanks
Loan |
Amount |
|
Loan |
Amount |
1 |
12000 |
|
1 |
9999.99 |
2 |
50000 |
|
1 |
2000.01 |
3 |
35000 |
|
2 |
9999.99 |
|
4 8500 |
|
2 |
9999.99 |
|
|
|
2 |
9999.99 |
|
|
|
2 |
9999.99 |
|
|
|
2 |
9999.99 |
|
|
|
2 |
0.05 |
|
|
|
3 |
9999.99 |
|
|
|
3 |
9999.99 |
|
|
|
3 |
9999.99 |
|
|
|
3 |
5000.03 |
|
|
|
4 |
8500 |
-
This problem statement is a bit vague. But, I'll take a stab anyway.
This code will create a new table from an autofiltered data field.
Code:
Private Sub cmdFilerCriteriaToNewXLTable_Click()
Dim Excel As Object
Dim excelSheet As Object
Dim excelSheet2 As Object
Dim Range As Object
Set Excel = GetObject(, "Excel.Application")
Set excelSheet = Excel.ActiveWorkbook.Sheets("YOUR_TABLE")
Excel.Visible = True
'from an autofiltered column on this table, can be made "Sheet1", etc.
Excel.ActiveWorkbook.Sheets("YOUR_TABLE").Select
strFILT = YOUR_TARGET_FILTER 'like 9999.99
'be on sheet "YOUR_TABLE"
'can delete this if autofilter is already on
Excel.application.Range("A1:Z1").Select
'Excel.application.Range("YOUR_RANGE_ONECOLUMN").Select
'============
'added this to set autofilter field val automaically
strRange = "YOUR_RANGE_ONECOLUMN"
strFC = Excel.application.Range(strRange).Column
'SET FIELD TO AUTOFILTER COLUMN (EXPLICIT=10) OR NO DATA RETURNED WITH NO ERROR
'if problem make field explicit to column loc rather than strFC var
'Excel.application.Selection.AutoFilter Field:=strFC, Criteria1:=strFILT
Excel.ActiveWorkbook.Sheets("Table_Complete").Select
Excel.application.Selection.AutoFilter Field:=10, Criteria1:=strFILT
'GETS ALL CELLS WITH DATA
excelSheet.Range("A1", excelSheet.Range("A1").End(xlToRight).End(xlDown)).Cells(1, 1).Select
With Excel.application.Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
'NAME RANGE
STRPLANTFILTER = Replace(strFILT, " ", "_") 'YOU MAY HAVE TO
'PREFIX YOUR CRITERIA TO AN ALLOWABLE XL TABLE NAME
excelSheet.Range("A1", excelSheet.Range("A1").End(xlToRight).End(xlDown)).Select
'COPIES THE AUTOFILTERED SHEET DATA
Excel.application.Selection.Copy
'ADD SHEET TO MAKE NEW TABLE FROM THE ABOVE COPY
Excel.Sheets.Add
'NAME SHEET - GRAB THE FIRST "NEW"
Excel.Sheets(i + 1).Name = STRPLANTFILTER
'SET NEW SHEET
Set excelSheet2 = Excel.ActiveWorkbook.Sheets(STRPLANTFILTER)
'SELECT NEW SHEET
Excel.application.Sheets(STRPLANTFILTER).Select
'SELECT THE FIRST CELL FOR PASTE
Excel.ActiveWorkbook.Sheets(STRPLANTFILTER).Range("A1").Select
'PASTE INTO CURRENT SHEET
Excel.ActiveSheet.Paste
'THAT MAKES A NEW TABLE FROM YOUR TARGET AUTOFILTERED DATA
End Sub