Copy formula paste value

[Arnesson]

Hi,

I'm pretty new in the VBA World and was hoping somebody could help me with this problem.

I have two worksheets: "worksheet1" containing the current data (e.g. today's progress) I want to copy and "worksheet2" containing historical data (e.g. historical progress) in which I would like to paste the current data.

The data in "worksheet1" is structured (A2:A10) under the actual date (A1). In "worksheet2" I have a calendar (4:4) with all dates from 2013-01-01 to 2014-12-31.

I want to run a macro that reads the actual date (A1) in "worksheet1" and looks for the date in row 4:4 in "worksheet2", and then copy the data from (A2:A10) in "worksheet1" and paste these data on row 5:13 in "worksheet2" in the column in which the cell A1 in "worksheet1" matches the date in row 4:4 in "worksheet2".

Note! I want the copy paste function to be a copy paste value function so that the mathematical formulas from "worksheet1" don't follow to "worksheet2".

 

[Kaizor]

Hi,

I'm pretty new in the VBA World and was hoping somebody could help me with this problem.

I have two worksheets: "worksheet1" containing the current data (e.g. today's progress) I want to copy and "worksheet2" containing historical data (e.g. historical progress) in which I would like to paste the current data.

The data in "worksheet1" is structured (A2:A10) under the actual date (A1). In "worksheet2" I have a calendar (4:4) with all dates from 2013-01-01 to 2014-12-31.

I want to run a macro that reads the actual date (A1) in "worksheet1" and looks for the date in row 4:4 in "worksheet2", and then copy the data from (A2:A10) in "worksheet1" and paste these data on row 5:13 in "worksheet2" in the column in which the cell A1 in "worksheet1" matches the date in row 4:4 in "worksheet2".

Note! I want the copy paste function to be a copy paste value function so that the mathematical formulas from "worksheet1" don't follow to "worksheet2".

Hey, First time on here but hope this helps

Sub CopyPasteValues()
Dim i As Integer
Dim ActualDate As Date
i = 1
ActualDate = Worksheets("worksheet1").Range("A1")

'Finds where the date is
Do Until ActualDate = Worksheets("worksheet2").Cells(4, i)
i = i + 1
Loop

'Copy and pastes the data under the date (Values only)
Worksheets("worksheet1").Range("A2:A10").Copy
Worksheets("worksheet2").Cells(5, i).PasteSpecial Paste:=xlPasteValues
End Sub

This should do exactly as you've asked. If it needs tweeking I'm sure I'll be able to help with that

Good luck!

 

[Arnesson]

Thank you Kaizor, it work's great!

 

[Snakehips]

I see that you are sorted but here is another example......

Sub Send_To_Calendar()With Sheets("Sheet2")
CalCol = Evaluate("=MATCH(Sheet1!A1,Sheet2!A4:ACF4,0)")
.Range(.Cells(5, CalCol), .Cells(13, CalCol)).Value = Range("A2:A10").Value
End With
End Sub