Copy Paste Automation

[yanex4412]

Hi all, i'm not very good with VBA. Will it be possible to copy and paste as values data from 1 workbook to another?

Made a table as an example, both Workbook have 8 identical Sheets except for the data. Workbook1 contains actual data(with formula) while Workbook 2 contains projection(values).
What I want to do is copy the values from Workbook1 to Workbook2 if the specific week is already updated with ACTUAL values.
There are also columns with that need to be skipped, just like Column D.

Workbook1 - Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]WEEK 1[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]WEEK 2[/TD]
[TD="align: center"]1900[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]WEEK 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


Workbook2 - Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]WEEK 1[/TD]
[TD="align: center"]1800[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]WEEK 2[/TD]
[TD="align: center"]2100[/TD]
[TD="align: center"]91[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]WEEK 3[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[/TR]
</tbody>[/TABLE]

 

[footoo]

What about WB1 cell E1?
Is it to be treated as updated and if so, how is it determined whether a week is updated or not?
Presumably the cells in WB1 that are shown as blank contain "" as a result of formulas?

 

[yanex4412]

Yes, its either 0 or error value. Let say i'll manually indicate in cell Z1 what week needs to be pasted,

 

[footoo]

Let say i'll manually indicate in cell Z1 what week needs to be pasted,

Sub FT()
Dim wb1 As Worksheet, wb2 As Worksheet, wk As Range, wkNum$
Set wb1 = Workbooks("Source.xlsm").Sheets("Sheet1")
Set wb2 = Workbooks("Destination.xlsm").Sheets("Sheet1")
wkNum = "WEEK " & wb1.[Z1]
Set wk = wb1.[A:A].Find(wkNum)
wb2.Cells(wk.Row, 2).Resize(, 2) = wk(1, 2).Resize(, 2).Value
wb2.Cells(wk.Row, 5).Resize(, 2) = wk(1, 5).Resize(, 2).Value
End Sub

 

[yanex4412]

thanks! it worked. also what if i will add another column to be pasted. what part of the script shall i change or add?

 

[footoo]

If you want to paste column M (i.e. column 13) :

wb2.Cells(wk.Row, "M") = wk(1, 13).Value

 

[yanex4412]

If you want to paste column M (i.e. column 13) :

wb2.Cells(wk.Row, "M") = wk(1, 13).Value

Thank you so much. sorry for the late reply