Copy data Dynamically to another workbook.

[saprater]

Hello all,
I have a situation where i have multiple plants sending me files that i copy and paste into a master workbook with all the plants info. I have found ways to copy from workbook 1 to workbook 2, but only with specific ranges or an entire sheet (sort of hard coded). What i want to do is take a file and copy the certain cell ranges based on the Row header in Column A. Below is an example of a sheet i would get from a plant. The line items are in the 100's, and not always in the same order. So what i want to do, is take Column A, and read maintenance and then copy the cell data in b3 to f3 to another workbook. The target workbook may have maintenance in Row 6 instead of row 3, so it would find Maintenance in the target sheet and then paste the data in that row.
I have built some user forms with VB, and researched the copy features, but do not understand how to dynamically place the Data into the target sheet. Can anyone point me in a direction, if this is even possible?

Thanks in Advance.

	Monday	Tuesday	Wednesday	Thursday	Friday
Maintenance	65	98	205	208	365
Supplies	55	605	80	805	95
Labor	5200	6300	3600	3600	3600
Materials	800	800	600	600	900

<colgroup><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Coding4Fun]

Here is an example, there were a few unknowns in your question so this will need to be adjusted to work for you but should be a good start.

Assuming you have 2 files, one named "CopyFrom.xlsm" and one named "CopyTo.xlsm" and you have data in the Copy From File. Your CopyTo file should have the headers but no data, if it has data it will be overwritten.

Copy From

	Mon	Tues	Wed	Thur	Fri
Maintenance	55	65	75	85	95
Supplies	32	22	10	15	16
Labor	23	27	29	53	12
Materials	10	19	50	49	65

<tbody>
</tbody>

Copy To

	Mon	Tues	Wed	Thur	Fri
Supplies
Matrerials
Labor
Maintenance

<tbody>
</tbody>

Sub CopyData()


Dim CopyF As Workbook   ' Copy From Workbook
Dim CopyT As Workbook   ' Copy To Workbook


Dim myRange1 As String  ' Copy Range
Dim myRange2 As String  ' Paste Range


Dim CheckVal1 As String ' Store Value to compare 1
Dim CheckVal2 As String ' Store Value to compare 2


Set CopyF = Workbooks("CopyFrom.xlsm") ' Set Varialble
Set CopyT = Workbooks("CopyTo.xlsm")    ' Set Varialble


myRange1 = CopyF.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row ' Identify Range to check from
myRange2 = CopyT.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row  ' Identify Range to check to


' Run through the options in the copy from list
For i = 2 To myRange1
' Capture the name from the Copy From sheet, used to compare and determine where to paste
CheckVal1 = CopyF.Sheets("Sheet1").Range("A" & i).Value


' Internal loop that will run through the options in the copy to sheet until it find a match
' or hits the end of the list
For e = 2 To myRange2


' Capturing the value to compare with
CheckVal2 = CopyT.Sheets("Sheet1").Range("A" & e).Value
' If it finds a match then copy Columns B to F and move on to looking for the next item.
If CheckVal1 = CheckVal2 Then
CopyT.Sheets("Sheet1").Range("B" & e & ":F" & e).Value = CopyF.Sheets("Sheet1").Range("B" & i & ":F" & i).Value
Exit For
End If


Next e


Next i


Set CopyF = Nothing
Set CopyT = Nothing


End Sub

 

[Fluff]

Another option

Sub CopyData()
   Dim Fnd As Range
   Dim Ary As Variant
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim i As Long
   
   Set Ws1 = Workbooks("[COLOR=#ff0000]Book1.xlsm[/COLOR]").Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   Set Ws2 = Workbooks("[COLOR=#0000ff]Master.xlsm[/COLOR]").Sheets("[COLOR=#0000ff]Sheet1[/COLOR]")
   Ary = Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
   For i = 1 To UBound(Ary)
      Set Fnd = Ws2.Range("A:A").Find(Ary(i, 1), , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then Ws1.Range("B" & i + 1).Resize(, 5).Copy Fnd.Offset(, 1)
      Set Fnd = Nothing
   Next i
End Sub

Change the values in red to match the workbook/sheet you are copying from & the values in blue to the workbook/sheet you want to copy to.

 

[saprater]

WOW that was nice, I just set that up and it worked perfect.

Many Thanks!!

Here is an example, there were a few unknowns in your question so this will need to be adjusted to work for you but should be a good start.

Assuming you have 2 files, one named "CopyFrom.xlsm" and one named "CopyTo.xlsm" and you have data in the Copy From File. Your CopyTo file should have the headers but no data, if it has data it will be overwritten.

Copy From

	Mon	Tues	Wed	Thur	Fri
Maintenance	55	65	75	85	95
Supplies	32	22	10	15	16
Labor	23	27	29	53	12
Materials	10	19	50	49	65

<tbody>
</tbody>

Copy To

	Mon	Tues	Wed	Thur	Fri
Supplies
Matrerials
Labor
Maintenance

<tbody>
</tbody>

Sub CopyData()


Dim CopyF As Workbook   ' Copy From Workbook
Dim CopyT As Workbook   ' Copy To Workbook


Dim myRange1 As String  ' Copy Range
Dim myRange2 As String  ' Paste Range


Dim CheckVal1 As String ' Store Value to compare 1
Dim CheckVal2 As String ' Store Value to compare 2


Set CopyF = Workbooks("CopyFrom.xlsm") ' Set Varialble
Set CopyT = Workbooks("CopyTo.xlsm")    ' Set Varialble


myRange1 = CopyF.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row ' Identify Range to check from
myRange2 = CopyT.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row  ' Identify Range to check to


' Run through the options in the copy from list
For i = 2 To myRange1
' Capture the name from the Copy From sheet, used to compare and determine where to paste
CheckVal1 = CopyF.Sheets("Sheet1").Range("A" & i).Value


' Internal loop that will run through the options in the copy to sheet until it find a match
' or hits the end of the list
For e = 2 To myRange2


' Capturing the value to compare with
CheckVal2 = CopyT.Sheets("Sheet1").Range("A" & e).Value
' If it finds a match then copy Columns B to F and move on to looking for the next item.
If CheckVal1 = CheckVal2 Then
CopyT.Sheets("Sheet1").Range("B" & e & ":F" & e).Value = CopyF.Sheets("Sheet1").Range("B" & i & ":F" & i).Value
Exit For
End If


Next e


Next i


Set CopyF = Nothing
Set CopyT = Nothing


End Sub

 

[Coding4Fun]

Glad that worked out for you