Jpernice
10-29-2020, 01:52 PM
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
10-29-2020, 03:57 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.