VBA, Copy and Paste

[mroser]

Hi all,
In advance I like to thank anyone who is able to help me out at all, it's much appreciated. I have a new workbook I am working on "Remaining cmd.xls" There are values from column B2:B300 that i would like to copy then go to another workbook find that value and copy some information from that workbook to "Remaining cmd.xls". I recorded a Macro of this process for two values (see below). I want to know what i need to add to the VBA code to allow this process to run for every entry in "remaining cmd.xls". Also, there are going to be certain values that are not in the second workbook. So, would i need to create a do-while loop?
I was looking through this code and I don't know how to adjust it so that once it completes the copy and paste for cell B2 it immeadiately goes to B3 and finds, copies, and pastes the same information. Here is the code the recorded macro gave me.

Keyboard Shortcut: Ctrl+a
'
Range("B2").Select
Selection.Copy
Windows("NEW TRAVEL TRAILERS.xls").Activate
Range("A1489").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("A2").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
Range("G1489").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("C2").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
Range("L1489").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("D2").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("M1489").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("E2").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
Range("N1489").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("F2").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
Range("O1489").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("G2").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
Range("V1489").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("I2").Select
ActiveSheet.Paste
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("NEW TRAVEL TRAILERS.xls").Activate
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
Columns("K:K").Select
ActiveWindow.ScrollColumn = 1
Range("A1590").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("A3").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
Range("G1590").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("C3").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
Range("L1590").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("D3").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("M1590").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("E3").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
Range("N1590").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("F3").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
Range("O1590").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("G3").Select
ActiveSheet.Paste
Windows("NEW TRAVEL TRAILERS.xls").Activate
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
Range("V1590").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("I3").Select
ActiveSheet.Paste
End Sub

Again, any help is really appreciated. Thanks a million!!

 

[Datsmart]

As a start, I would suggest viewing this Demo about cleaning up recorded macros.
http://www.datapigtechnologies.com/flashfiles/cleaningmacrocode.html

Also, it looks like you are copying a group of cells from row 1489, then a group from row 1590. Can you define the relation or reason you are jumping 101 rows between groups?
Will need to understand this if you want to automate this part of the process.

 

[mroser]

In Book1 I have a list of barcodes in column B (B2:B200). I want to compare the barcodes to a list of barcodes in another workbook ("NEW TRAVEL TRAILERS.xls) which are listed in column K (K2:K1974). When I recorded the macro I selected cell B2 in Book1 and opened the NTT.xls workbook, then highlighted column K and used CTRL+F to find that specific barcode in that workbook. Then I copied certain values from NTT.xls to Book1. In the macro the (CTRL+F) Find function did not show up. I think this can be done using Vlookup but I havent done it right so far.

The row values will change depending upon which row the match is in. There will be only one match per barcode. I hope this isn't too much of a hassle, i know i can't explain things well. I'm currently just copying and pasting every individual barcode. I just thought this would speed the process up. I recorded the macro to get an idea of how the code would look, but i don't know how to make it find a match for a specific cell in two columns that are in different workbooks. And i don't know how to tell it to take information from just the row the match is in...Thank you for the link. I'll make sure to do that before posting all of the useless info that i did before. And thank you for any help!