Copy Paste Automation

yanex4412

New Member
Joined
Jun 14, 2016
Messages
12
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
ABCDEF
1WEEK 1200010050010
2WEEK 219009001510
3WEEK 3

<tbody>
</tbody>


Workbook2 - Sheet1
ABCDEF
1WEEK 1180011050058
2WEEK 221009130207
3WEEK 3200030201515

<tbody>
</tbody>
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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?
 
Last edited:
Upvote 0
Yes, its either 0 or error value. Let say i'll manually indicate in cell Z1 what week needs to be pasted,
 
Upvote 0
Let say i'll manually indicate in cell Z1 what week needs to be pasted,

Code:
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
 
Last edited:
Upvote 0
thanks! it worked. also what if i will add another column to be pasted. what part of the script shall i change or add?
 
Upvote 0
If you want to paste column M (i.e. column 13) :

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

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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