VBA - Setting a paste destination range relative to Active Cell

[AnnR27]

Hi ... I'm new to this forum - thanks in advance for taking the time to help me with this ... I'm creating a Macro to paste a series of formulas from a template in hidden rows above to cells further down in the same worksheet. My code works fine if I set an absolute range as the Destination - here's a portion of it:

Sub TimelineRow150()
Dim TimelineMatch As Integer, ProjectPlan As Object
TimelineMatch = ActiveCell.Offset(0, -1).Value

If TimelineMatch = 26 Then
Range("E26:AQ28").Select
Selection.Copy
Range("E150:AQ152").Select
ActiveSheet.Paste

ElseIf TimelineMatch = 30 Then
Range("E30:AQ32").Select
Selection.Copy
Range("E150:AQ152").Select
ActiveSheet.Paste



I'd like to be able to copy and paste this code to run it multiple times in the same worksheet, (the TimelineMatch variable actually has 26 different options). Is there a way to replace the
[Range("E150:AQ152").Select] portion of the code with a range that is relative to the Active Cell? (I'd instruct users that cell E be the Active cell before running the Macro, and E to AQ is still the range I need the copied data to appear.) I've tried using ActiveCell.Offset, but I keep getting an error of Method 'Range of Object' _Global' failed.

Would love any insights you can provide. Many thanks!

Ann

 

[JoeMo]

Maybe something like this (change the offset to suit):

Sub TimelineRow150()
Dim TimelineMatch As Integer, ProjectPlan As Object
TimelineMatch = ActiveCell.Offset(0, -1).Value
If TimelineMatch = 26 Then
    Range("E26:AQ28").Copy
ElseIf TimelineMatch = 30 Then
    Range("E30:AQ32").Copy
End If
ActiveCell.Offset(124, 0).PasteSpecial Paste:=xlPasteAll
'clear the clip board
Application.CutCopyMode = False
End Sub

 

[AnnR27]

Thanks JoeMo! This solution is sooooo close! (And I've been at this for over a week now, so please understand that you totally just made my week!)

I can set the Offset to get the paste to start in the column to the right of the Active Cell... but I want to start the paste in the Active Cell itself, and for some reason I just can't quite figure out what the code needs to look like to make that happen ... what would I need to do differently?

EDITED POST ...
I JUST FIGURED IT OUT! I must have been making it harder than I needed to ... I just removed the Offset altogether and it worked ... I'd been trying to add weird stuff like Offset(0,0) and or .Address and getting an invalid qualifier.

So to make it work, the code looks like this:

Sub TimelineMaster()
Dim TimelineMatch As Integer, ProjectPlan As Object
TimelineMatch = ActiveCell.Offset(0, -1).Value
If TimelineMatch = 26 Then
Range("E26:AQ28").Copy
ElseIf TimelineMatch = 30 Then
Range("E30:AQ32").Copy
End If
ActiveCell.PasteSpecial Paste:=xlPasteAll
'clear the clip board
Application.CutCopyMode = False
End Sub

JoeMo - you're a genius! Thank you! Thank you! Thank you!!!!


Thanks so much!

Ann

 

[JoeMo]

Thanks JoeMo! This solution is sooooo close! (And I've been at this for over a week now, so please understand that you totally just made my week!)

I can set the Offset to get the paste to start in the column to the right of the Active Cell... but I want to start the paste in the Active Cell itself, and for some reason I just can't quite figure out what the code needs to look like to make that happen ... what would I need to do differently?

EDITED POST ...
I JUST FIGURED IT OUT! I must have been making it harder than I needed to ... I just removed the Offset altogether and it worked ... I'd been trying to add weird stuff like Offset(0,0) and or .Address and getting an invalid qualifier.

So to make it work, the code looks like this:

Sub TimelineMaster()
Dim TimelineMatch As Integer, ProjectPlan As Object
TimelineMatch = ActiveCell.Offset(0, -1).Value
If TimelineMatch = 26 Then
Range("E26:AQ28").Copy
ElseIf TimelineMatch = 30 Then
Range("E30:AQ32").Copy
End If
ActiveCell.PasteSpecial Paste:=xlPasteAll
'clear the clip board
Application.CutCopyMode = False
End Sub

JoeMo - you're a genius! Thank you! Thank you! Thank you!!!!


Thanks so much!

Ann

You are welcome - and congratulations on getting that last bit on your own.