Transfering data

kennethbrandon · Mar 17th 2017

Greetings Sir,

I have a simple attandence sheet which I could not find the correct formula to use.
I need to transfer The New Month Data to Sheet 2 by leaving the Cells empty.
Attached here with my worksheet. Please Help me, Thank You.

**alansidman** · Mar 19th 2017

Re: Transfering data

try this vba solution

Code

Option Explicit




Sub NewMonth()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim i As Long
    Dim lr As Long, lr2 As Long
    lr = s1.Range("B" & Rows.Count).End(xlUp).Row
    Dim CurrMonth As Date
    CurrMonth = InputBox("What is the last date of lastmonth?  Enter as mm/dd/yyyy")
    Application.ScreenUpdating = False
    For i = 4 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        If s1.Range("D" & i) > CurrMonth Then
            s1.Range("B" & i & ":D" & i).Cut s2.Range("A" & lr2 + 1)
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True




End Sub

Display More

kennethbrandon · Mar 19th 2017

Re: Transfering data

Hello Sir,
Thank you very much, sir. It works great! Your formula is very fantastic!
Could you please share with me how to copy another next new months to sheet 3,
sheet 4 an so forth . Honestly, I am very new to VBA . Your help is very much appreciated, Sir. Thank you.

**alansidman** · Mar 20th 2017

Re: Transfering data

Try this modification:

Code

Sub Foo()




    Dim sname As String
    sname = "Sheet"
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = sname & Sheets.Count
    
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets(sname & Sheets.Count - 1)
    Set s2 = Sheets(sname & Sheets.Count)
    Dim i As Long
    Dim lr As Long, lr2 As Long
    lr = s1.Range("B" & Rows.Count).End(xlUp).Row
    Dim CurrMonth As Date
    CurrMonth = InputBox("What is the last date of lastmonth?  Enter as mm/dd/yyyy")
    Application.ScreenUpdating = False
    For i = 4 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        If s1.Range("D" & i) > CurrMonth Then
            s1.Range("B" & i & ":D" & i).Cut s2.Range("A" & lr2 + 1)
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    




End Sub

Display More

Transfering data

Participate now!

Share