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

mamun.ges · Jul 1st 2021

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.

**KjBox** · Jul 1st 2021

Have you resolved the issue with merged cells?

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

**KjBox** · Jul 1st 2021

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

mamun.ges · Jul 1st 2021

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

**KjBox** · Jul 1st 2021

Is Row 2 of your Bill sheet a Header Row?

mamun.ges · Jul 1st 2021

Yes, KjBox.

**KjBox** · Jul 1st 2021

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?

mamun.ges · Jul 1st 2021

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.

**KjBox** · Jul 1st 2021

Quote

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

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

**KjBox** · Jul 1st 2021

Is Column A of the Bill sheet empty?

mamun.ges · Jul 1st 2021

Yes,

"Invoice_Number" as the head will be the right

no the A column has data. I just want to pick data from B column which starts from B3 to below.

Thank You.

mamun.ges · Jul 1st 2021

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.

**KjBox** · Jul 1st 2021

Try the attached sample file.

Code assigned to the button

Code

Option Explicit

Sub NewWorkbook()
    Dim x, y, Hdrs, wbk As Excel.Workbook, i As Long, d As Double
    Const sPath As String = "E:\1. Upload file\"
    
    Hdrs = Array("SL", "Date", "Invoice_Number", "Status", "Number")
    x = Sheets("Bill").[a2].CurrentRegion.Columns(2)
    Set wbk = Workbooks.Add(1)
    ReDim y(1 To UBound(x, 1), 1 To 5)
    
    Application.ScreenUpdating = 0
    With wbk.Sheets(1)
        .Name = "CBS Upload"
        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 + 1, 1))
                y(i, 4) = "Credit": y(i, 5) = 50
                d = d + 50
            Else
                y(i, 4) = "Debit": y(i, 5) = d
            End If
        Next
        For i = 1 To 5
            .Cells(1, i) = Hdrs(i - 1)
        Next
        .Cells(2, 1).Resize(UBound(y, 1), 5) = y
        With .Cells(1).CurrentRegion
            .Columns(3).HorizontalAlignment = -4108
            .Columns(5).HorizontalAlignment = -4108
            .Rows(1).HorizontalAlignment = -4108
            .Columns(3).ColumnWidth = 10
            .Columns(3).ColumnWidth = 16
        End With
        .Parent.SaveAs sPath & "Bill_" & Format(Now, "dd_mm_yyyy hh_nn") & ".xlsx", 51
    End With
    
End Sub

Display More

mamun.ges · Jul 1st 2021

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.

**KjBox** · Jul 1st 2021

Try this version.

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

mamun.ges · Jul 1st 2021

It Says run time error '13':

Type Mismatch

**KjBox** · Jul 1st 2021

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

Change the code to this

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:\1. Upload file\"
    
    Hdrs = Array("SL", "Date", "Invoice_Number", "Status", "Number")
    x = Sheets("Bill").[a2].CurrentRegion.Columns(2)
    ReDim y(1 To UBound(x, 1) - 1, 1 To 5)
    dNum = [d1]
    Set wbk = Workbooks.Add(1)
    
    Application.ScreenUpdating = 0
    With wbk.Sheets(1)
        .Name = "CBS Upload"
        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
        For i = 1 To 5
            .Cells(1, i) = Hdrs(i - 1)
        Next
        .Cells(2, 1).Resize(UBound(y, 1), 5) = y
        With .Cells(1).CurrentRegion
            .Columns(3).HorizontalAlignment = -4108
            .Columns(5).HorizontalAlignment = -4108
            .Rows(1).HorizontalAlignment = -4108
            .Columns(3).ColumnWidth = 10
            .Columns(3).ColumnWidth = 16
        End With
        .Parent.SaveAs sPath & "Bill_" & Format(Now, "dd_mm_yyyy hh_nn") & ".xlsx", 51
    End With
    
End Sub

Display More

mamun.ges · Jul 1st 2021

Kjbox,

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.

**KjBox** · Jul 2nd 2021

So what is the new order for the headers?

Is:

Invoice_Number, Date, Status, Number, SL

correct?

mamun.ges · Jul 2nd 2021

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.

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

Participate now!

Share

Tags