Macro to paste data
 

I have a spreadsheet that is updated each Friday and I am trying to automate
it as much as possible but have got stuck with a macro to paste one column
into another depending on the result of a formula.

I have a range C3:BB3 that represent each Friday in the year. In C4:BB4 I
have put in a formula that returns "Yes" if the date above represents the
current week or "No" otherwise. I want to paste data from B8:B10 into the
cells below the column that has "Yes" in it. E.g. If F4 = "Yes", then copy
B8:B10 into F8:F10.

The idea being that the next Friday the next column along would be updated
and the data isthen be used in a chart to review performance.

Your help would be much appreciated.

Macro to paste data
 

pjd33,

Give the range B8:B10 the name "Indata" (Insert - Name - Define).

Sub CopyData()
Dim Item As Range
For Each Item In Range("C4:BB4")
If Item.Value = "Yes" Then
Range("Indata").Copy Destination:=Cells(8, Item.Column)
Exit For
End If
Next Item
End Sub
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"pjd33" wrote in message
...
I have a spreadsheet that is updated each Friday and I am trying to
automate
it as much as possible but have got stuck with a macro to paste one column
into another depending on the result of a formula.

I have a range C3:BB3 that represent each Friday in the year. In C4:BB4 I
have put in a formula that returns "Yes" if the date above represents the
current week or "No" otherwise. I want to paste data from B8:B10 into the
cells below the column that has "Yes" in it. E.g. If F4 = "Yes", then copy
B8:B10 into F8:F10.

The idea being that the next Friday the next column along would be updated
and the data isthen be used in a chart to review performance.

Your help would be much appreciated.

Macro to paste data
 

Thanks for this.
It worked fine the first time but now having played about, it now copies the
formula over from the Indata range. How do I make it just paste the values? I
tried and screwed it up!

"Earl Kiosterud" wrote:

pjd33,

Give the range B8:B10 the name "Indata" (Insert - Name - Define).

Sub CopyData()
Dim Item As Range
For Each Item In Range("C4:BB4")
If Item.Value = "Yes" Then
Range("Indata").Copy Destination:=Cells(8, Item.Column)
Exit For
End If
Next Item
End Sub
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"pjd33" wrote in message
...
I have a spreadsheet that is updated each Friday and I am trying to
automate
it as much as possible but have got stuck with a macro to paste one column
into another depending on the result of a formula.

I have a range C3:BB3 that represent each Friday in the year. In C4:BB4 I
have put in a formula that returns "Yes" if the date above represents the
current week or "No" otherwise. I want to paste data from B8:B10 into the
cells below the column that has "Yes" in it. E.g. If F4 = "Yes", then copy
B8:B10 into F8:F10.

The idea being that the next Friday the next column along would be updated
and the data isthen be used in a chart to review performance.

Your help would be much appreciated.

Macro to paste data
 

OK, then let's do this:

Sub CopyData()
Dim Item As Range
For Each Item In Range("C4:BB4")
If Item.Value = "Yes" Then
Range("Indata").Copy
Cells(8, Item.Column).PasteSpecial Paste:=xlPasteValues
Exit For
End If
Next Item
End Sub
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"pjd33" wrote in message
...
Thanks for this.
It worked fine the first time but now having played about, it now copies
the
formula over from the Indata range. How do I make it just paste the
values? I
tried and screwed it up!

"Earl Kiosterud" wrote:

pjd33,

Give the range B8:B10 the name "Indata" (Insert - Name - Define).

Sub CopyData()
Dim Item As Range
For Each Item In Range("C4:BB4")
If Item.Value = "Yes" Then
Range("Indata").Copy Destination:=Cells(8, Item.Column)
Exit For
End If
Next Item
End Sub
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"pjd33" wrote in message
...
I have a spreadsheet that is updated each Friday and I am trying to
automate
it as much as possible but have got stuck with a macro to paste one
column
into another depending on the result of a formula.

I have a range C3:BB3 that represent each Friday in the year. In C4:BB4
I
have put in a formula that returns "Yes" if the date above represents
the
current week or "No" otherwise. I want to paste data from B8:B10 into
the
cells below the column that has "Yes" in it. E.g. If F4 = "Yes", then
copy
B8:B10 into F8:F10.

The idea being that the next Friday the next column along would be
updated
and the data isthen be used in a chart to review performance.

Your help would be much appreciated.

Macro to paste data
 

Thanks very much - that does exactly what I wanted.

"Earl Kiosterud" wrote:

OK, then let's do this:

Sub CopyData()
Dim Item As Range
For Each Item In Range("C4:BB4")
If Item.Value = "Yes" Then
Range("Indata").Copy
Cells(8, Item.Column).PasteSpecial Paste:=xlPasteValues
Exit For
End If
Next Item
End Sub
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"pjd33" wrote in message
...
Thanks for this.
It worked fine the first time but now having played about, it now copies
the
formula over from the Indata range. How do I make it just paste the
values? I
tried and screwed it up!

"Earl Kiosterud" wrote:

pjd33,

Give the range B8:B10 the name "Indata" (Insert - Name - Define).

Sub CopyData()
Dim Item As Range
For Each Item In Range("C4:BB4")
If Item.Value = "Yes" Then
Range("Indata").Copy Destination:=Cells(8, Item.Column)
Exit For
End If
Next Item
End Sub
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"pjd33" wrote in message
...
I have a spreadsheet that is updated each Friday and I am trying to
automate
it as much as possible but have got stuck with a macro to paste one
column
into another depending on the result of a formula.

I have a range C3:BB3 that represent each Friday in the year. In C4:BB4
I
have put in a formula that returns "Yes" if the date above represents
the
current week or "No" otherwise. I want to paste data from B8:B10 into
the
cells below the column that has "Yes" in it. E.g. If F4 = "Yes", then
copy
B8:B10 into F8:F10.

The idea being that the next Friday the next column along would be
updated
and the data isthen be used in a chart to review performance.

Your help would be much appreciated.