Macro to move data between sheets on condition

[Closed]
Report
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
Hello,

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 replies

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
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
             Sheet2.Columns.AutoFit
         End If
Sheet1.[L5].AutoFilter

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.

Cheerio,
vcoolio.
Hi,

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

Any ideas?
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Carrie,

Its probably best if you upload a sample of your work book so that we can try and sort it out for you.

Upload a sample to a free file sharing site such as DropBox or SpeedyShare and then post the link to your file back here.

We'll take a look then.

Cheerio,
vcoolio.