I have a datasheet. From this datasheet, the excel file generated specific data. I have been using the below code to generate the excel file.
But it generates the file with all data either is filtered or not. I want, it uses only the visible data to generate the excel. Or the header"invoice" column.
Hope someone helps me to solve the problem.
[/LIST]
But it generates the file with all data either is filtered or not. I want, it uses only the visible data to generate the excel. Or the header"invoice" column.
Hope someone helps me to solve the problem.
VBA Code:
[LIST=1]
[*]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\"
[*]Hdrs = Array("Status", "Date", "SL", "Number", "Invoice_Number")
[*]
[*]x = Sheets("Bill").[a2].CurrentRegion.Columns(2)
[*]
[*]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
[*]
[*]
[*]With wbk.Sheets(1)
[*].Name = "CB Upload"
[*]
[*]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
[*]y(i, 4) = dNum
[*]d = d + y(i, 4)
[*]y(i, 5) = x(i + 2, 1)
[*]Else
[*]y(i, 1) = "Debit"
[*]y(i, 2) = Date
[*]y(i, 3) = i
[*]y(i, 4) = d
[*]End If
[*]Next
[*]
[*].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
[*].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