Hi, this is a solved post but I need some attention.[xpost]
Use a Command Button to Create a User Defined Worksheet With Specific Data[/xpost]
I am using the code which is created by "KjBox" given below.
In this code, the file created a new worksheet for all the data of Header "Invoice", If I filter the" invoice" then it also generates a file with all data of Header "Invoice"
I want it will only generate a excel and text file based on the filter or visible data only of the Header "Invoice"
Code
Option Explicit
Sub NewWorkbook()
Dim x, y, Hdrs, wbk As Excel.Workbook, i As Long, dNum As Double, d As Double
Const sPath As String = "E:\Upload Folder\"
' Change this to suit your actual headers
Hdrs = Array("Status", "Date", "SL", "Number", "Invoice_Number")
' Load all data in Bill sheet B column into array x
x = Sheets("Bill").[a2].CurrentRegion.Columns(2)
' Redimension array y to suit size of array x
ReDim y(1 To UBound(x, 1) - 1, 1 To 5)
' Get the "Number" from cell D1
dNum = [d1]
' Create a new workbook with one sheet
Set wbk = Workbooks.Add(1)
Application.ScreenUpdating = 0
' Name the new workbook sheet and add the data to it
With wbk.Sheets(1)
.Name = "CBS Upload"
' loop through array y and add relevant data, the last row is different from all the rest
For i = 1 To UBound(y, 1)
If i < UBound(y, 1) Then
y(i, 1) = "Credit"
y(i, 2) = Date
y(i, 3) = i 'This is the SL it increments by 1 for each iteration of the loop
y(i, 4) = dNum ' This is the "Number"
d = d + y(i, 4) ' This keeps a running total of the "Number"
y(i, 5) = x(i + 2, 1) ' This gets the invoice number from array x
Else
y(i, 1) = "Debit"
y(i, 2) = Date
y(i, 3) = i
y(i, 4) = d 'Here the total of all "Numbers" is added
End If
Next
' Write contents of arrays Hdrs & y to the new worksheet
.Cells(1, 1).Resize(, 5) = Hdrs
.Cells(2, 1).Resize(UBound(y, 1), 5) = y
' Set the formatting for the new sheet (-4108 is the enumeration for xlCenter)
With .Cells(1).CurrentRegion
.Columns(4).HorizontalAlignment = -4108
.Columns(5).HorizontalAlignment = -4108
.Rows(1).HorizontalAlignment = -4108
.Columns(4).ColumnWidth = 10
.Columns(5).ColumnWidth = 16
End With
' Freeze the header Row
ActiveWindow.SplitRow = 1
ActiveWindow.FreezePanes = 1
' Save the new workbook with required name
.Parent.SaveAs sPath & "Bill_" & Format(Now, "dd_mm_yyyy hh_nn") & ".xlsx", 51
End With
End Sub
Sub a()
For i = 1 To UBound(y, 1)
y(i, 1) = i: y(i, 2) = Date
If i < UBound(y, 1) Then
y(i, 3) = CLng(x(i + 2, 1))
y(i, 4) = "Credit": y(i, 5) = dNum
d = d + y(i, 5)
Else
y(i, 4) = "Debit": y(i, 5) = d
End If
Next
End Sub
Display More
Hope someone helps me.
Thanks in advance.