Use a Command Button to Create a User Defined Worksheet With Specific Data

  • 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.

  • ahh that is why invoice numbers were in Bill as text.


    Change Line 36 of the code from

    Code
                    y(i, 5) = x(i + 2, 1) ' This gets the invoice number from array x

    to

    Code
                    y(i, 5) = "'" & x(i + 2, 1) ' This gets the invoice number from array x

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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

  • you use an additional line of code for example

    Code
    ActiveSheet.Cells(2,1).Resize(104).VerticalAlignment = -4108

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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.

  • Change this bit of code

    Code
            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

    to

    Code
            With .Cells(1).CurrentRegion
                .Columns(4).HorizontalAlignment = -4108
                .Columns(5).HorizontalAlignment = -4108
                .Rows(1).HorizontalAlignment = -4108
                .Columns(4).ColumnWidth = 10
                .Columns(5).ColumnWidth = 16
                .Columns(4).NumberFormat = "#,##0.00"
            End With

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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

    Code
     .Columns(4).NumberFormat = "#,##0.00"
    
    'Should I Write as'
    
     .Columns(4).textFormat = "?"

    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

    Code
    .Columns(4).NumberFormat = "General"

    or is you truly mean you want to convert numbers to text (not recommended as formulas would not work) then use

    Code
    .Columns(4).NumberFormat = "Text"

    As for the second part do you mean you want to save the file as a .csv file using pipe as the delimiter?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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.

    Code
    Format(Now, "dd_mm_yyyy hh_nn") & ".xls", 51 " 51 to 52"


    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?

    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

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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 ".

    Above I give my code. Hope you corrected the code.


    Thank you again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!