Copy data Dynamically to another workbook.

S

saprater

New Member
Joined
Oct 25, 2017
Messages
5
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.
[TABLE="width: 475"]
<colgroup><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]


[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Maintenance[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]208[/TD]
[TD="align: right"]365[/TD]
[/TR]
[TR]
[TD]Supplies[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]605[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]805[/TD]
[TD="align: right"]95[/TD]
[/TR]
[TR]
[TD]Labor[/TD]
[TD="align: right"]5200[/TD]
[TD="align: right"]6300[/TD]
[TD="align: right"]3600[/TD]
[TD="align: right"]3600[/TD]
[TD="align: right"]3600[/TD]
[/TR]
[TR]
[TD]Materials[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]900[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Mon[/TD]
[TD]Tues[/TD]
[TD]Wed[/TD]
[TD]Thur[/TD]
[TD]Fri[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Maintenance[/TD]
[TD]55[/TD]
[TD]65[/TD]
[TD]75[/TD]
[TD]85[/TD]
[TD]95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplies[/TD]
[TD]32[/TD]
[TD]22[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Labor[/TD]
[TD]23[/TD]
[TD]27[/TD]
[TD]29[/TD]
[TD]53[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Materials[/TD]
[TD]10[/TD]
[TD]19[/TD]
[TD]50[/TD]
[TD]49[/TD]
[TD]65[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Copy To
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Mon[/TD]
[TD]Tues[/TD]
[TD]Wed[/TD]
[TD]Thur[/TD]
[TD]Fri[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplies[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matrerials[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Labor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Maintenance[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Code: 
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
 
Upvote 0
Another option
Code: 
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.
 
Upvote 0
WOW that was nice, I just set that up and it worked perfect.

Many Thanks!!

Coding4Fun said:
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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Mon[/TD]
[TD]Tues[/TD]
[TD]Wed[/TD]
[TD]Thur[/TD]
[TD]Fri[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Maintenance[/TD]
[TD]55[/TD]
[TD]65[/TD]
[TD]75[/TD]
[TD]85[/TD]
[TD]95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplies[/TD]
[TD]32[/TD]
[TD]22[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Labor[/TD]
[TD]23[/TD]
[TD]27[/TD]
[TD]29[/TD]
[TD]53[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Materials[/TD]
[TD]10[/TD]
[TD]19[/TD]
[TD]50[/TD]
[TD]49[/TD]
[TD]65[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Copy To
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Mon[/TD]
[TD]Tues[/TD]
[TD]Wed[/TD]
[TD]Thur[/TD]
[TD]Fri[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplies[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matrerials[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Labor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Maintenance[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Code: 
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
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Copy a worksheet from one workbook to another workbook based on conditional statements
Replies
5
Views
361
HighAndWilder
H
L
  • Question Question
Insert and copy dynamic rows between workbook A and workbook B
Replies
1
Views
218
Anthony47
A
D
  • Question Question
Merge sheets of a workbook macro
2
Replies
19
Views
334
sleek12
sleek12
M
  • Solved
Dynamic Hyperlinking to freshly copied sheets e.g. "Sheet1 (2)"
Replies
3
Views
175
dreid1011
dreid1011
Guna13
  • Question Question
How to Efficient way for create Multiple Workbooks
Replies
1
Views
391
Guna13
Guna13

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top