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

  • Hi Everybody / Kjbox,

    Wish Kjbox are there and will help me as before.

    I have been trying to create a solution. I have a worksheet Named (Bill) of a large data file. The worksheet data start from supposing A3.F103. Now I want to use a command button that creates a .xlsx file in a specific location E:\1. Upload file. The worksheet named Bill & with current date & Time.xlsx (Like Bill_01_07_2021 09_11.xlsx)


    The worksheet creates with the following data:

    • The First column “A” (will be named as “SL”) has an incremental number based on the worksheet “Bill” data B3 to Last data + one extra.
    • The Second column “B” (Will be named as “Date”) inputs Today's date (dd /mm/ yyyy) to the last row based on the column “A”
    • The Third Column “C” (will be named as “Invoice”) copied data from B3 to the last data cell (Suppose the data is available now from B3 to B103)
    • The Forth column “D” (will be named as “Status”) input a text “Credit” to the last row based on the worksheet “Bill” data B3 to B103 but on the last row of the new worksheet D105 the text "Debit"
    • The Fifth column “E” (will be named as “Number”) inputs a fixed value “50” to the last row based on the worksheet “Bill” data B3 to B103 but on the last row of the new worksheet D105 will be the summation of above data D2:D104 "

    Can it be done by code? If yes, Please

    (I have attached a file that will be the result file if the code possible)


    Thanks in Advance.

  • Have you resolved the issue with merged cells?


    I don't want merged cells to interfere with any new code!

    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.

  • Do you want the new workbook to be saved and closed after creating and populating it, or just save it with the required name?

    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,

    Yes, I resolve the issue of the Marged Cell, I Remove the merged cell in the worksheet, Then It worked fine.


    For this thread worksheet just save it with the required name.


    Thanks

  • Is Row 2 of your Bill sheet a Header Row?

    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.

  • I think you are confused with the naming!


    What is to be the name of the new workbook?

    What is to be the name of the worksheet in that new workbook?

    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.

  • Ok, The name of the new workbook will be "Bill" save in format like "Bill_01_07_2021 9_11.xlsx (Bill_Date(dd_mm_yyyy & Time), and the Worksheet Name of new workbook will be CBS Upload.

  • Quote

    The Third Column “C” (will be named as “Invoice”)

    Your sample file has "Invoice_Number" as the header, which do you want?

    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.

  • Is Column A of the Bill sheet empty?

    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.

  • Rewrite to remove naming confusion.


    I have a workbook name support.xlsm having a worksheet Named (Bill) of a large data file. The worksheet data start from (supposing) A3.F103. Now I want to use a command button that creates a .xlsx file in a specific location E:\1. Upload file. The workbook named Bill & with current date & Time.xlsx (Like Bill_01_07_2021 09_11.xlsx) in where the worksheet name will be CBS upload.


    The worksheet (Bill_01_07_2021 09_1) creates with the following data:

    • The First column “A” (will be named as “SL”) has an incremental number based on the worksheet “Bill” data B3 to Last data + one extra.
    • The Second column “B” (Will be named as “Date”) inputs Today's date (dd /mm/ yyyy) to the last row based on column “A” of CBS Upload worksheet.
    • The Third Column “C” (will be named as “Invoice_Number”) copied data from (Workbook Support.xlsm and Worksheet Name Bill) B3 to the last data cell (Suppose the data is available now from B3 to B103)
    • The Forth column “D” (will be named as “Status”) input a text “Credit” to the last row based on the worksheet “Bill” data B3 to B103 but on the last row of the new worksheet (Bill_01_07_2021 09_11) D105 the text will be "Debit"
    • The Fifth column “E” (will be named as “Number”) inputs value from (Workbook Support.xlsm and Worksheet Name Bill A5 cell value) Start from E2 to the last row based on the worksheet “Bill” data B3 to B103 but on the last row of the new worksheet (Bill_01_07_2021 09_11) E105 will be the summation of above data E2:E104 "

    Thank You and sorry for the confusing description. Hope you excuse my mistake.

  • Try the attached sample file.


    Code assigned to the button

    Files

    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.

  • Thanks for the sample file.

    It Perfects only one Thing that the number 50 is a variable. So I need to change it sometime.

    Can the value pick from D1 of your data file.

    I mean D1 has the 50 value which will be picked for the "Number" of the new workbook.

    Please.

  • Try this version.


    It uses D1 to hold the "Number" to be used in the new workbook.

    Files

    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.

  • Oops sorry. The CurrentRegion has now changed, it now includes Row 1


    Change the code to this

    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.

  • Kjbox,:thumbup:


    It's absolutely perfect,


    Will you tell me that in your code what line is used for continuous numbering:/.

    I have to change it for some reason,

    That the Invoice_Number comes at first and the Serial Number goes to the Last column.


    Will you tell or teach me what change in the code.


    Thank You.

  • So what is the new order for the headers?


    Is:


    Invoice_Number, Date, Status, Number, SL


    correct?

    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,

    Actually, my Header is very long. I used a short form.

    It is Status, Date, SL, number, Invoice_Number


    I tried to understand your code but it's very complicated and out of reach.

    Please can you describe some narration to understand the code?


    Thanks and best regards.