Save Userform data in a new sheet (every day) and merging data

eddyrcabrera79 · Nov 30, 2015

I have a Userform that is being used to enter data in to a sheet in an excel document. I would like to be able to do the following (Please if for some reason there are better inputs, Please let me know)

1 – I would like that each day that I open the document all the info enter using the Userform is being saved in a new sheet called “Data” , Please note that I will open this document several times a day and I only want that this new sheet be created only once a day when I open the document for first time.

2 – I was thinking in the userform to create a new Comand button called “Merge Data” and I would like that at the end of the day when I click on it that the entries from the sheet “ Data” in being transferred to the sheet “ Master” where I will have the relationship of all my entries.

Please if you see a better way to make this to happen, I’m open to hear about it.

Please see sample of my Excel document

Thank you

eddyrcabrera79 · Nov 30, 2015

I forgot one more thing:

3 - when the info is tranfered from the sheet "Data" to the Sheet " Master". Can the Sheet "Data" clears the entries for next day?

Thank you

chirayu · Nov 30, 2015

If you want an new data sheet to be made daily, the name has to be unique. So format I have used is Data DD-MMM-YYYY. You will need to add your code that pastes from form to sheet. Which you already have in your code.

Code:

Sub CreateSheet()

Dim wsname, wscheck As String

wsname = "Data " & Format(Date, "DD-MMM-YYYY")
wscheck = False

'Check if DATA sheet exists
For i = 1 To Application.ActiveWorkbook.Sheets.Count
    If Application.ActiveWorkbook.Sheets(i).Name = wsname Then
        wscheck = True
        Exit For
    End If
Next

'Create sheet doesn't exist or choose that sheet
If wscheck = False Then
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = wsname
    'Add your paste code here
Else
    Worksheets(wsname).Select
    'Add your paste code here
End If

End Sub

Code:

Sub MergeData()

Dim wsname As String
wsname = "Data " & Format(Date, "DD-MMM-YYYY")

Worksheets(wsname).Select
Range("A2:J" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
Worksheets("Master").Select
Range("A2:J" & Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False

MsgBox "Data Merged to Master", vbInformation, ""

End Sub

eddyrcabrera79 · Nov 30, 2015

Hi Chirayu,

I put the codes in the document but nothing happen. would you mind checking the document?
Is it possible to copy unlimited column not just untill column J

chirayu · Dec 1, 2015

Try this. For some odd reason your form isn't working for me. Stops at the Format(Time code in your Userform Initialize code.

I added Call CreateSheet bit to the bit where u activate sheet1 to paste your code in Submit button. You might wanna check your code to change sheet1 references to the data sheet with DD-MMM-YYYY

eddyrcabrera79 · Dec 1, 2015

Hi Chirayu,

I downloaded the file and nothing happened. the new sheet it doesn't get created and when I click on the Merge Data button, I receive the error message "Run-time 9, Subscript out of range" and from there it takes me to the Merge Data Button Code and highlight the below

Code:

Worksheets(wsname).Select

Thank you

chirayu · Dec 1, 2015

You need to click Submit on Userform to create the sheet. It gave the error because sheet wasn't created so it couldn't merge anything. I did however clean up the merge data code while checking the file so use this instead.

eddyrcabrera79 · Dec 1, 2015

Hi Chirayu,

It works fine, I just need one more thing.

I should merge the info at the end of the day. but in case I have to do it before for some reason and then enter new data using the userform in the same day. How can I make the sheet Data to clear values once the button Merge is pressed.

Thank you

chirayu · Dec 1, 2015

Add this at bottom of the merge data code before the MsgBox bit

Code:

Worksheets(wsname).Select
Selection.ClearContents
Range("A1").Select

eddyrcabrera79 · Dec 1, 2015

I pasted it as you recommended but it doesn't clear the Data Sheet

Code:

' Merge Data
Sub MergeDataCommandButton_Click()
Dim wsname As String
wsname = "Data " & Format(Date, "DD-MMM-YYYY")

Worksheets(wsname).Select
Range("A2:J" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
Worksheets("Master").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Worksheets(wsname).Select
Selection.ClearContents
Range("A1").Select

MsgBox "Data Merged to Master", vbInformation, "Confirmation"

End Sub

chirayu · Dec 2, 2015

Oh damn lol sorry. I forgot I did a direct copy as opposed to select the copy. Here u go. Changed t to select then copy. should work now

Code:

' Merge Data
Sub MergeDataCommandButton_Click()
Dim wsname As String
wsname = "Data " & Format(Date, "DD-MMM-YYYY")

Worksheets(wsname).Select
Range("A2:J" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.Copy
Worksheets("Master").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Worksheets(wsname).Select
Selection.ClearContents
Range("A1").Select

MsgBox "Data Merged to Master", vbInformation, "Confirmation"

End Sub

eddyrcabrera79 · Dec 2, 2015

awesome it works really good. thanks so much

Deepak · Dec 2, 2015

Irrespective of selection [which should to avoided]

Code:

Sub MergeDataCommandButton_Click()
Dim wsname As String, rng As Range
wsname = "Data " & Format(Date, "DD-MMM-YYYY")

With Worksheets(wsname)
  Set rng = .Range("A2:J" & .Cells(.Rows.Count, "A").End(xlUp).Row)  '.Select
End With

With Worksheets("Master")
    rng.Copy
    .Cells(.Rows.Count, "A").End(xlUp)(2).PasteSpecial -4104
End With

Application.CutCopyMode = False

rng.ClearContents

MsgBox "Data Merged to Master", vbInformation, "Confirmation"
End Sub

eddyrcabrera79 · Dec 2, 2015

Thanks Deepak. It works good too

eddyrcabrera79 · Dec 2, 2015

Hi Chirayu and Deepak,

I added a few things to the code for the new sheet that it will be created everyday.
- For the first row the text will be bold
- For all columns from A to W the text will be certer

How can I make the info entered to autofit automatically to the cells

Code:

'Open new sheet everyday
Sub CreateSheet()

Dim wsname, wscheck As String

wsname = "Data " & Format(Date, "DD-MMM-YYYY")
wscheck = False

'Check if DATA sheet exists
For I = 1 To Application.ActiveWorkbook.Sheets.Count
    If Application.ActiveWorkbook.Sheets(I).Name = wsname Then
        wscheck = True
        Exit For
    End If
Next

'Create sheet doesn't exist or choose that sheet
If wscheck = False Then
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = wsname
    Range("A1").Value = "Name"
    Range("B1").Value = "Phone Number"
    Range("C1").Value = "City Preference"
    Range("D1").Value = "Dinner Preference"
    Range("E1").Value = "Date"
    Range("F1").Value = "Do you have a car?"
    Range("G1").Value = "Maximun to spend"
    Range("H1").Value = "Calendar"
    Range("I1").Value = "Current Time"
    Range("J1").Value = "Start Time"
Else
    Worksheets(wsname).Select
End If

'Make text bold
Range("A1, B1, C1, D1, E1, F1, G1, H1, I1, J1").Font.Bold = True
'Center columns
Columns("A:W").HorizontalAlignment = xlCenter

End Sub

chirayu · Dec 2, 2015

Depends what you mean by that. Expand Column size to fit cells:

Code:

Cells.EntireColumn.Autofit

Wrap the text within existing cell size:

Code:

Cells.EntireColumn.WrapText = TRUE

For the WrapText you can change Cells.EntireColumn bit to the bit that is used to select the range if you only want to WrapText thos specific cells

eddyrcabrera79 · Dec 2, 2015

I was looking for number one, adjust the cell to the text automatically.

In my form something is happening.

by the time I enter for 3rd time all the information and I transfer it to the Data Sheet its when the cells gets adjust to the text automatically.

Could you please check the attachment. maybe I'm doing something wrong or I'm not including something.

Thanks

chirayu · Dec 3, 2015

What do you mean by something? I just entered the data using the form & didn't see the issue.

I believe if you're talking about autofit - it isn't working as its in the wrong section. You put it in CreateSheet but it should also be in SubmitButton_Click. So it runs each time something new is entered.

Have made that change. Also changed the horrible bright blue colour of the calendar to white.

eddyrcabrera79 · Dec 3, 2015

That what it was, I didn't put it on the SubmitButton_Click button. Now its working fine. I also like the color in the Calendar.
Thank you lots

Save Userform data in a new sheet (every day) and merging data

Member

Attachments

Member

Well-Known Member

Member

Attachments

Well-Known Member

Attachments

Member

Well-Known Member

Attachments

Member

Well-Known Member

Member

Well-Known Member

Member

Excel Ninja

Member

Member

Well-Known Member

Member

Attachments

Well-Known Member

Attachments

Member