Macro to move data between sheets on condition

Carrie - Sep 9, 2016 at 08:32 AM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Sep 12, 2016 at 05:48 AM

I am a beginner to using Macros in excel. I understand there are lots of things on here, and the internet in general, telling you how to create a macro to move data conditionally between sheets but I cannot figure out how to adapt it to fit my case. I am using excel 2013.

I have a spreadsheet with two worksheets. Worksheet 1 is called "2016" and Worksheet 2 is called "Closed Cases".

The data on Worksheet 1 ranges between B6:N6 and continues down B7:N7, B8:N8 etc with no limit to number of rows.

I would like to automatically transfer data to the next blank row on Closed Cases if L6: L?? says "Yes".

I would like the row to be deleted from 2016 when it is moved to closed cases.

If anyone could help me with this I would be grateful!

2 responses

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Sep 10, 2016 at 01:32 AM
Hello Carrie,

See if the following code (untested) helps:-

Sub TransferData()

Dim lr As Long

Application.ScreenUpdating = False

Sheet1.Range("L5", Sheet1.Range("L" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Yes", 7
  lr = Sheet1.Range("L" & Rows.Count).End(xlUp).Row
    If lr > 1 Then
      Sheet1.Range("B6", Sheet1.Range("N" & Sheet1.Rows.Count).End(xlUp)).Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
          Sheet1.Range("A6", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp)).EntireRow.Delete
         End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code assumes:-

- Sheet1 is the source sheet and sheet2 is the destination sheet.
- Source sheet headings begin in row 5, data in row 6.
- Headings exist in the destination sheet (row 1?).

The code filters sheet1 Column L for the criteria "Yes" and then transfers the relevant row of data to sheet2. It then deletes the "used data" from sheet1 once transferred.

To implement the code:-

- Press Alt + F11 to open the VBA Editor.
- Up the top, select Insert--->Module. A large white code field should then appear.
- Paste the above code into the big white field.

To run the code, you can assign it to a button or press Alt + F8 which will open the macro dialogue box. In the dialogue box, select the code name (in this case TransferData) and click on "Run".

I hope that this helps.


When I try to run this it comes up with a red x and 400? not sure why?

Any ideas?