VBA to copy first row in named range to whole named range.

[tttommy2]

Hello

I am looking for VBA to copy the first row in a named range and paste it to whole of the named range.

I am sure its very easy but I cant find anything.

Thank you

tttommy2

 

[JoeMo]

Hello

I am looking for VBA to copy the first row in a named range and paste it to whole of the named range.

I am sure its very easy but I cant find anything.

Thank you

tttommy2

Welcome to the board.
Try this after you replace myRng with the name of your range.

Sub PasteToWholeNamedRange()
Dim R As Range
Set R = Range("myRng")  ' <-- put the name of your range between the quote marks
R.Rows(1).Copy R
End Sub

 

[tttommy2]

Thank you Joe - that was quick.

I should have been more precise. I would like to copy paste formulae from first row of named range to whole of the named range. I don't want to copy the formats.

tttommy2

 

[JoeMo]

Thank you Joe - that was quick.

I should have been more precise. I would like to copy paste formulae from first row of named range to whole of the named range. I don't want to copy the formats.

tttommy2

This pastes formulas only - retaining the initial format of each row in the named range.

Sub PasteToWholeNamedRange()
Dim R As Range
Set R = Range("myRng")  ' <-- put the name of your range between the quote marks
R.Rows(1).Copy
R.PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
End Sub

 

[tttommy2]

Sub PasteToWholeNamedRange()
Dim R As Range
Set R = Range("myRng") ' <-- put the name of your range between the quote marks
R.Rows(1).Copy
R.PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
End Sub

Hi Joe

Thank you for your reply.

I am having a problem with your last VBA code above. The first row of my named range contains a single cell array formula and your VBA code does not seem to work with it. When I take out the array formula your VBA code works a treat.

My array formula is single cell formula and is not part of an array. When I do an ordinary copy paste, it copies fine.

Any ideas?

Thank you for your help.

tttommy2

 

[JoeMo]

Sub PasteToWholeNamedRange()
Dim R As Range
Set R = Range("myRng") ' <-- put the name of your range between the quote marks
R.Rows(1).Copy
R.PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
End Sub

Hi Joe

Thank you for your reply.

I am having a problem with your last VBA code above. The first row of my named range contains a single cell array formula and your VBA code does not seem to work with it. When I take out the array formula your VBA code works a treat.

My array formula is single cell formula and is not part of an array. When I do an ordinary copy paste, it copies fine.

Any ideas?

Thank you for your help.

tttommy2

What exactly "does not seem to work" - do you get an error or ....? Can you tell me what the layout of your named range is? And can you post the single-cell array formula?

 

[tttommy2]

Its a very large and complex sheet so I have simplified it down to better explain the issue. I attach a jpg of the sheet showing formulas only.

 

[tttommy2]

Its a very large and complex sheet so I have simplified it down to better explain the issue.

	A	B	C	D	E
1	Expiry	Symbol	Expiry	Symbol	Min Expiry
2	42048	aa	=B3	=C3	{=MIN(IF(E3=INDEX(aData,,2),INDEX(aData,,1)))}
3	42077	aa
4	42109	bb
5	42051	bb

<tbody>
</tbody>

Range name = "aData" and refers to $C$2:$E$5

The array formula is like a MinIf formula. It shows the min expiry for each symbol. So for first 2 aa enties min expiry is 42048 and for 2 bb entries its 42051.

When I run your code I get Microsoft Visual Basic "Runtime error '1004'. You cannoot change part of an array."

However when I do simple copy paste of C2:E2 to C2:E5 it copies fine and everything is correct.There are 4 seperate array formulas in E2:E5 and they are all correct.

I hope I explain myself well

Thank you

tttommy2

I am using Excel 2010.


Sub PasteToWholeNamedRange()
Dim R As Range
Set R = Range("aData")
R.Rows(1).Copy
R.PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
End Sub

 

[JoeMo]

Not sure why you get that error, but maybe easier to try another approach. If I understand correctly, the first code I posted was OK except it transferred the formatting of the first row and you didn't want that.

Assuming I understand correctly, see if this works for you:

Sub PasteToWholeNamedRange()
Dim R As Range
Set R = Range("aData")  
R.Rows(1).Copy R
'Add some code to format rows of aData after the first as desired
'Maybe
R.Rows("2:" & R.Rows.Count).ClearFormats
End Sub