Ask a question Report Reply to this topic

Transfer an entire row from one sheet to another [Solved/Closed]

JC - Apr 19, 2016 at 08:36 PM - Latest reply: vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen

- Apr 20, 2016 at 05:08 PM

Excel Ask a question Report Reply to this topic

Good Evening,

I seriously need major help. Please!! Please!! Please!!

I have an inventory of over 600+ items (rows) each row has 12 columns with descriptions of the product-the last column is name buyer(where I put who purchase the item, If know one has bought the item then is blank. I want to be able to transfer all the blank ones to a separate sheet without having to copy and paste. I don't know much about excel but I guess it would be an equation or macro that would transfer the entire row if the buyer column is blank.

Thank you so much

5 replies

Report Respond to vcoolio

vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen - Apr 20, 2016 at 01:48 AM

Helpful

Hello JC,

See if the following code will do the trick for you:-

Sub TransferStuff()

Application.ScreenUpdating = False
Application.DisplayAlerts = False


     Range("L1", Range("L" & Rows.Count).End(xlUp)).AutoFilter 1, ""
     Range("A2", Range("K" & Rows.Count).End(xlUp)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
     Range("A2", Range("K" & Rows.Count).End(xlUp)).Delete
     [L1].AutoFilter
    
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Sheet2.Select

End Sub

The code will filter on Column L for blank cells and will transfer the relevant row of data to sheet2. The code will also delete the "used' data from sheet1.

Following is the link to my test work book for you to peruse:-

https://www.dropbox.com/s/hvptuv6w5ehlaox/JC%28Transfer%20rows%20with%20empty%20cells%20in%20Column%20L%29.xlsm?dl=0

Click on the button to see the code at work

I hope that this helps.

Cheerio,
vcoolio.

Report Respond to JC

JC - Apr 20, 2016 at 10:39 AM

Helpful

Thank you so much, this really really makes my life easier.
One last question and sorry to bother you- instead of deleting the row could I just hide it?
I looked it up and I only saw an option to hide the whole sheet with "visible=false"

Once again thank you so much

Report Respond to vcoolio

vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen - Apr 20, 2016 at 11:04 AM

Helpful

Hello JC,

Remove line 10 from the code above then place the following line of code:-

Columns("L").SpecialCells(4).EntireRow.Hidden = True

just under this line of code:-

[L1].AutoFilter

That should do the trick for you.

Cheerio,
vcoolio.

Report Respond to JC

JC - Apr 20, 2016 at 11:16 AM

Helpful

Thank you so much for helping me out. This really helps with my inventory!

Report Respond to vcoolio

vcoolio 1159 Posts Thursday July 24, 2014Registration dateModeratorStatus March 28, 2018 Last seen - Apr 20, 2016 at 05:08 PM

Helpful

Hello JC,

You're welcome. I'm glad that I was able to help.

Cheerio,
vcoolio.

Ask a question