IF [find value in a column] THEN [copy values from multiple different columns] with VBA

[bobbyj1234]

Hi,

I am new to this Forum and very new to using VBA in excel, so please excuse my lack of proper vocabulary or knowledge.

I am a film editor and we have EDLs that show us all of the cuts in a film with time codes in and out - an excel workbook of the movie. We have a separate document called an ALE, which shows all of the info for each clip - without the time codes in and out. More of an excel key. When an EDL is created we are limited to an 8 character identifier, therefore we create 6-8 character codes to represent clips. These clips are used in multiple locations and can be present in the worksheet in 20 - 30 different rows if the clip is used multiple times in the film:

ES_2232M

ES_2235M

ES_2240M

ES_2309

ES_2241

ES_2034 These codes then need to be replaced by the clip's full name: ES_2232M_NationalGeographic_118799_19351111.jpg ES_2235M_NationalGeographic_530520_19351111.jpg ES_2240M_NationalGeographic_581958_19351111.jpg ES_2309_NationalGeographic_259913_19351111.jpg ES_2241_NationalGeographic_1002867_19351111.jpg ES_2034_NationalGeographic_259913_19351111.jpg I have used this VBA to copy over the clip's full names correctly: (does require making two columns with the code and file name) Attribute VB_Name = "Module1"Sub MultiFindNReplace() 'Update 20140722 Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8) Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng In ReplaceRng.Columns(1).Cells InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value Next Application.ScreenUpdating = True End Sub <tbody> </tbody>

<tbody>
</tbody>

In addition to the clip name, there are 17 columns of data in the ALE. Is there any way to copy the 17 columns of data while replacing the clip identifier?

I am open to having 2 different VBA as well. I would assume that this is very similar to a VLookup but I am looking for something a little more automated. Any help is much appreciated. And once again, excuse my lack of knowledge on this subject, and happy to try to explain in more detail.

Thanks,
Bobby J

 

[JLGWhiz]

Don't know if this helps but, to copy from one workbook to another:

Workbooks(2).Sheets(1).Range("A1:Q1").Copy Workbooks(1).Range("A1")

Would copy 17 columns on row 1 of workbook 2 to the first row of workbook 1 starting in column A. You only need the anchor cell in the destination workbook when using this copy method. Also the workbook and sheet names should be used instead of the index numbers, since the index numbers are subject to variation depending on several scenarios. But the point is that you could add a line of code to your current macro to copy the data from one workbook to the other.