VBA Data Transfer

[JhnSanchez]

Hi everyone,

Just wondering if anyone can help me out here. I've looked into other solutions online but no results matches what I need to happen.

Basically, I have two sheets, one is "Shift Essentials" and the other is "Data Archive". The Shift Essentials sheet will filled out manually as the day progresses and at the end of the day the data will be transferred to the Data Archive sheet. The shift essentials form will not be filled completely since not all people will have a Member Number and Member Tier also the check-in, Call, and Notes may or may not be blank (mostly blank). I need help to set up two macros, one to transfer data from shift essentials sheet to data archive sheet and another macro to clear data on the shift essentials with getting rid of the format and the conditional formatting other columns.

This is a project that me and brothers are trying to figure out since we are starting a new business together after college. This will be a start for us. I'm a complete beginner in VBA and I would like to understand it more but I have no idea what to do and all the referrences and videos I found online was just not so great. So what I currently have right now in VBA is just a simple Select Range which selects the first and last row that has any data on it then manaually paste it on the Data Archive sheet (but that completely defeats the purpose of the assignment).

Thanks.

 

[kevin9999]

How do you want the Date (column B) filled in on the Data Archive sheet?

 

[JhnSanchez]

We're planning on just doing it manually. Since it's definitely going to be a pain for us to figure out. We'll basically, merge the cells on the Date column and add the date. Then enclose the data in with a border just so that tere is a separation of data between different days.

 

[kevin9999]

OK. Based on the images you have provided, try the following 2 subs with a copy of your data. Just a tip - avoid merged cells like the plague.

Option Explicit
Sub Transfer_Data()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lr1 As Long, lr2 As Long
    
    Set ws1 = Worksheets("Shift Essentials")
    Set ws2 = Worksheets("Data Archive")
    lr1 = ws1.Cells.Find("*", , xlFormulas, , 1, 2).Row
    lr2 = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
    
    ws1.Range("B15:L" & lr1).Copy ws2.Range("C" & lr2)

End Sub
Sub Clear_Form()
    Dim ws1 As Worksheet
    Dim lr1 As Long
    Dim Answer As Integer
    Set ws1 = Worksheets("Shift Essentials")
    lr1 = ws1.Cells.Find("*", , xlFormulas, , 1, 2).Row
    
    Answer = MsgBox("You are about to Clear the Shift Essentials sheet" & vbNewLine & vbNewLine _
    & "Do you wish to proceed?", vbQuestion + vbYesNo)
    
    If Answer = vbYes Then
       lr1 = ws1.Cells.Find("*", , xlFormulas, , 1, 2).Row
       ws1.Range("B15:L" & lr1).ClearContents
    Else
        Exit Sub
    End If

End Sub

 

[JhnSanchez]

Thanks a million mate. It would've definitely taken me months to figure this out on my own.

 

[kevin9999]

Thanks a million mate. It would've definitely taken me months to figure this out on my own.

You're welcome, and thanks for the feedback.