VBA

[Jpernice]

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

[xSkyscraper]

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.


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