Looking to Apply Macro to an Entire Column without running every time

[notaprogramer]

Hey this may be a simple question but I am new to the Macro business any help would be greatly appreciated. Basically I am trying to Copy information from one sheet of my excel file which contains analyzed data and then paste that data to the sheet that has thousands of cells with raw data. The Copy would be based on a specific userID and then the paste would have to happen multiple times on the other sheet everywhere that the specific username is in the adjacent cell. I would then want this to run for an entire column. I will post a basic recorded Macro just to give an idea where this is to happen and also provide any information that could be useful:

Sub BusinessMove()
'
' BusinessMove Macro
'
' Keyboard Shortcut: Ctrl+m
'
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Copy
Sheets("businesses").Select
Cells.Find(What:="70149726", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("raw data").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(1, 0).Range("A1").Select


Application.CutCopyMode = False
Selection.Copy
Sheets("businesses").Select
Cells.Find(What:="70008363", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("raw data").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
End Sub

[walruseggman]

This is a great example where recorded code can be both a blessing and a curse. It's helping you do what you want, but man is this terrible code (no fault of your own). You don't need a single one of those .Activate or .Selects, and the repetitive code can be replaced by a simple Do While Loop.

Providing a sample worksheet would help, though, with a before and after. Doesn't need to be a huge data set, just enough to demonstrate the functionality you want.

I ask for an example because some of the code is confusing. Like I don't understand in your first few lines of code why you are copying something, doing nothing with it, switching sheets, then copying something else.

This will likely only end up being ~a dozen lines of code at most, so please don't get discouraged.

Your use of ActiveCell isn't great practice either unless absolutely necessary, so if you can show which cells you're trying to copy, that would help.

[notaprogramer]

Okay, give me a minute or two

[notaprogramer]

Hopefully this makes more sense, basically I have the needed information on the second sheet, I want to copy paste the userID from this page, store it and then also store the “hobby” (the reason I copied and didn’t do anything with it is because I stored it in the search bar of the “Search” function, but as I’m sure you know, that doesn’t work. ) I would then like to take this information to the first sheet and wherever that UserID occurs to paste the hobby in the adjacent cell.

So I guess to sum it up for both you and I:
Copy Unique search criteria (UserID) and “hobby” from page 2
Paste Hobby with corresponding UserIDs on page 1
Repeat x workfile

[walruseggman]

7 lines, not too bad. It's not going to look like it "works" on your example sheet because you used RAND to generate the dummy User IDs. Which is fine, except that it means the IDs don't match across the sheets, so nothing is found that matches, so no hobbies are copied. Once I hard coded the user IDs to match across the sheets, the code worked as-expected. Hopefully it works on your real data, too.

Let me know if you have any questions. The sheet names on the first two lines of code may need to be changed to match your actual workbook, I commented which was which.

Please Login or Register  to view this content.

[notaprogramer]

If I weren't happily married I would propose haha, thank you for your help good sir or maddam

[walruseggman]

Glad to help, thanks for the rep.