Try this version
Use a Command Button to Create a User Defined Worksheet With Specific Data
-
mamun.ges -
July 1, 2021 at 4:54 PM -
Thread is marked as Resolved.
-
-
-
Kjbox,
My invoice number starts with 0 (Like 00026) but after the worksheet generate the invoice number cell trim 0 before value (like 26)
How to resolve this.
Thank You.
-
-
Hi Kjbox,
I have learned so many things and It's a time severe for me. I don't know how to thank you because you deserve more than thanks.
How to: This for "HorizontalAlignment = -4108" what will be the code for Vertically Alignment= center and Horizontal Alignment= Center
-
-
-
Dear Kjbox,
The Number I said earlier is not as the number it's an amount. So I have to convert it amount (Like 50.00 instead of 50)
So what will be the code and where I have to make the change.
Thanks.
-
-
Dear Kjbox
Thank you so much for the support you gave.
Wish you all the best.
-
Hi, Dear Kjbox,
Hope you are fine. I have some questions and some help need.
What will be code for text format data Like this one
I have to save the excel file in compatibility mode Xls file then what will be the code.
and I have also saved the file in text with the pipe-delimited format. What will be the code for generating the text file for the above conditions?
Thanks in Advance.
-
To answer the first part, use
or is you truly mean you want to convert numbers to text (not recommended as formulas would not work) then use
As for the second part do you mean you want to save the file as a .csv file using pipe as the delimiter?
-
-
Thank you Kjbox
In the code
Code' Load all data in Bill sheet B column into array x x = Sheets("Data Sheet").[a2].CurrentRegion.Columns(2)
Now there is another header added name "batch number" which data will be copied from the "data sheet" column 21" to column 6.
how to do this. and
A text file same as the xls file ( condition ) will be created with the same name in the same path with pipe pipe-delimited format.
The xls file should be saved worksheet as a compatibility mode, I change 51 to 52. Is it ok, or I have to change something.
Thank you again.
-
Dear KjBox,
Hope you are fine. I have been using your below code which works fine (thanks to you). But is there any way that it only selects filter data.
All the conditions are the same and generate a file using that filter data?
Code
Display MoreOption 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
The filter data column will be the "Invoice Number".
Hope you help me to understand the code.
Thanks and best regards
-
Can you attach your workbook as I no longer have it on my system
-
Thanks, Kjbox
Hope you are well.
Sorry for the late response.
Here is the New Workbook.xlsm I attached for your support.
-
KjBox,
As per your request, I uploaded the attached file.
Hope you check the file and help me.
Thanks in advance.
-
-
Try the attached
-
Thanks for your kind support.
-
You're welcome
-
Dear KjBox,
In the sample worksheet, the invoice number start from row 3 but in my worksheet invoice number start from row 5.
So when I filter data and save it, It picks all the data from the sheet, not the filter visible data.
I tried to change it for my worksheet But failed to do the correct sequence.
Here, "Name" is the filter column" C ".
Code
Display MoreDim x, y, z, Hdrs, wbk As Excel.Workbook, i As Long, ii As Long Dim dNum As Double, d As Double Const sPath As String = "E:\Upload Folder\" Dim sName As String sName = Range("J2") ' Change this to suit your actual headers Hdrs = Array("SL", "Invoice", "Name", "District", "TSL", "TRA_Date", "Amount", "Amount in Word", "Register Note", "Narration") ' Load all data in Bill sheet B column into array x x = Sheets("All Data").[b4].CurrentRegion.Columns(4) 'Invoice' g = Sheets("All Data").[c4].CurrentRegion.Columns(7) 'Name' w = Sheets("All Data").[D4].CurrentRegion.Columns(8) 'District' p = Sheets("All Data").[e4].CurrentRegion.Columns(5) 'TSL' q = Sheets("All Data").[f4].CurrentRegion.Columns(3) 'Date' R = Sheets("All Data").[g4].CurrentRegion.Columns(6) 'Amount' s = Sheets("All Data").[h4].CurrentRegion.Columns(9) 'Amount in Word' t = Sheets("All Data").[i4].CurrentRegion.Columns(12) 'Register SL' v = Sheets("All Data").[j4].CurrentRegion.Columns(10) 'Narration' ' Size and load array z with only visible Invoice Numbers ReDim z(1 To 1) For i = 1 To UBound(x, 1) If Not Sheets("All Data").Rows(i).Hidden Then ii = ii + 1 ReDim Preserve z(1 To ii) z(ii) = x(i, 1) End If Next ' Redimension array y to suit size of array x ReDim y(1 To UBound(z, 1) - 1, 1 To 10)
Above I give my code. Hope you corrected the code.
Thank you again.
-
Please Kjbox, help needed.
-
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!