VBA: Work Around for moving from one sheet to another based on cell value?

larinda4

Board Regular
Joined
Nov 15, 2021
Messages
73
Office Version
  1. 365
Platform
  1. Windows
Hi guys!

You've helped me so much in the past, I'm hoping you can help me again.

I get a large data dump I have to do once a month but I have some formatting and moving around I need to do. (Lots of sorting, moving, resorting, etc.)

I use a vlookup multiple times to pull in data from another sheet (sheet 2 "emails"). If data pulls in, I need to move the whole row (from sheet 1 "no emails") back to the other tab (sheet 2 "emails"). Everything worked good until I hit this part of the macro. I search column H to find the @ sign (it's a column for emails for our contacts). Here's the thing though, the other two vlookups I do bring in email addresses for sure, the third time is like a hit or miss. This time there was no data pulled in so I guess I'm messing up the macro by telling it to look for something that isn't there. But it could be there sometimes if that makes sense. The rest of my macro works good until I get to this part:

'Find First Email
Sheets("No Emails").Select
ActiveCell.Offset(0, 7).Columns("A:A").EntireColumn.Select
Selection.Find(What:="@", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Select
Application.CutCopyMode = False

'Move Contacts with emails to Emails tab
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Sheets("Emails").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlDown).Select
ActiveSheet.Paste
Sheets("No Emails").Select
Range("A1").Select

I sort the data based on emails (blanks go to the top, I found a work around for that one.) So I use the find function to find the first @ symbol, then I cut all the data below it to move to the new tab. If you need me to post more of the macro, let me know!

Is there a work around I can do for this part of the macro? Like an if statement or something? Or a way to check this column and if there's data in column H, move it to sheet 2(emails)? I only want blanks to remain in that column (sheet 1 "no emails") so I can continue cross referencing.

Any help would be greatly appreciated! I know if someone looked at my macro they would think it's pretty silly, but it does work! (It's just the long way, I'm sure.)
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,223,948
Messages
6,175,579
Members
452,653
Latest member
craigje92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top