Automatically cut paste data rows from an Excel worksheet into another


How to automatically cut paste data rows from an Excel worksheet into another based on date criteria using VBA. First we loop through the data. We then identify the cells with the dates data. Next we assign a date value from a cell to a variable. We compare the variable with two dates – a beginning date and an end date. When our variable date lies between these two dates, we cut the entire row. Next we activate the other sheet, find the next blank row for data placing and paste the data in this row. Once all the data has been transferred we are left with gaps or blank rows in the original worksheet as shown in the image below:

Worksheet after specific dates data has been cut

Worksheet after specific dates data has been cut


Now we need to delete the blank rows in the original worksheet so that the data is in continuous rows. Here also we automate the process of deleting the blank rows using a looping process. In case we have lots of data we can cut and paste specific cell ranges from one sheet to another to make the process faster.

Watch the training video and then study the VBA code to automatically cut paste data rows from an Excel worksheet into another based on date:


Watch the video on YouTube.

Complete macro or VBA code:
Module1:
Sub Delete_Data()
Dim i As Long, LastRow As Long, mydate As Date, erow As Long
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
Application.ScreenUpdating = False
For i = LastRow To 2 Step -1
mydate = Cells(i, “B”)
If mydate <= DateValue("December 31, 2014") And _ mydate >= DateValue(“January 01, 2014”) Then
‘Cells(i, “B”).EntireRow.Delete
Cells(i, “B”).EntireRow.Cut
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row
ActiveSheet.Paste Destination:=Worksheets(“Sheet2”).Rows(erow)
End If

Next i
delete_blank_rows

End Sub

Module2:
Sub delete_blank_rows()
Dim row As Long
LastRow = ThisWorkbook.Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).row
row = 2
For row = row To LastRow
If Cells(row, 1) = “” Then
Cells(row, 1).EntireRow.Delete
End If
Next row
End Sub

Caution: If you have two continuous blank rows then the second blank row will not be deleted. You’ll have to run the macro again. To overcome this problem run the ‘for loop’ in a reverse manner as shown below:
Sub delete_blank_rows()
Dim row As Long
LastRow = ThisWorkbook.Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).row
row = 2
For row = LastRow To row Step -1
If Cells(row, 1) = “” Then
Cells(row, 1).EntireRow.Delete
End If
Next row
End Sub

Download a sample file:


6 thoughts on “Automatically cut paste data rows from an Excel worksheet into another

  1. Sayeed

    Hi Dinesh,

    *** URGENT REQUEST****
    I am using your formula for “Automatically cut paste data rows from an Excel worksheet into another” and I have done a like for like copy to test the macro but.. unfortunately
    If mydate = DateValue(“January 01, 2014″) Then
    ActiveSheet.Paste Destination:=Worksheets(“Sheet2″).Rows(erow)
    formula are highlighted in red, when I run the Macro I get an error message ” !Compile error: Syntax error” Also the first row Sub Delete_Data() is highlighted in yellow.

    Please help as this is my first time using VBA, and the other thing is can/ how can I connect the Macro to a command button to allow the transfer of row(s) to sheet two at a click of a button? but note: once I click the command button I would like to automatically generate todays date which needs to reflect on sheet 2 after the last comment.

    Your urgent attention and response would be greatly appreciated.
    Please e-mail me the revised VBA code.

    Best regards

    Sayeed

    Reply
    1. Mark

      It seems the quotation marks (“), when copied need to be replaced with your own keyboards Quotation marks.
      I had this problem as the Quotation marks are slightly different.

      Reply
  2. Pingback: Delete Blank Rows from Excel Worksheet Using Reverse For Next Loop | Excel VBA Training Videos

  3. Jeff

    How are you able to do this if you want to copy info based on dates into separate worksheets. Each worksheet would be labeled by month and from the main worksheet the info would be copied into the corresponding worksheet based on the date

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *