How to replace cells by (predefined) ranges

F

firsttimer

Hello,
I have defined several ranges with formula's in sheet2 eg:
range1=A1:D5 (5rows),
range2=A10:D20 (10rows),
range3=A30:D37 (7rows)

In sheet1:column A I have cell values
range2
range2
range1
range3,
etc

How can I insert the matching range defined in sheet2 into sheet1,
 
F

firsttimer

I wrote next Sub,

Sub InsertRange()

Dim cellToReturnTo As Range
Set cellToReturnTo = ActiveCell
Dim sSearchValue As String
sSearchValue = ActiveCell.Text
Cells.Find(What:=sSearchValue, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("MacroData").Select
Range(sSearchValue).Select
Selection.Copy
Application.GoTo cellToReturnTo
Selection.Insert Shift:=xlDown
End Sub

wich works fine if the cell values in sheet 1 are "plain" text,
except they are Excel formula's,

I think I have to replace "sSearchValue = ActiveCell.Text"
into something else BUt I don't know what
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top