larinda4
Board Regular
- Joined
- Nov 15, 2021
- Messages
- 73
- Office Version
- 365
- Platform
- 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.)
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.)