Pasting a value into multiple cell addresses

[hmws]

I have a range of cells (a3,c3,e3,a12,c12 etc) containing
variable cell references.

I would like to be able to go to/lookup a Target
Worksheet and enter the date in that cell.

The formula below returns now() in my target sheet.

sub enterdate()

Application.Goto Worksheets("Target") _
.Range(Worksheets("Source").Range("a3").Value), True
ActiveCell = Now()

End sub

Is there a way to speed this up by doing the other cells
in the source worksheet (ie c3,e3,a12,c12 etc) at the
same time?

Thank you

 

[Don Guillett]

try this without even going to the worksheet

Worksheets("Source").Range("a3,c3,e3") = Now()

 

[Don Guillett]

OR,
Sub fillinranges()
With Worksheets("Sheet23").Range("a3,c3,e3")
..Value = Now()
..NumberFormat = "mm/dd/yyyy"
End With
End Sub

 

[hmws]

Thanks Don for your incredibly quick response but I think
I may have phrased the question poorly.

The contents of cell "a3" return a variable cell address
depending on selection. I want to lookup/goto the result
in a3 in this case it is returning $m$19 and enter the
date in cell M19 in the target worksheet (cell A3 is in
the source worksheet).

The code I have written does this ok but one cell only.

Your code overwrites the original formulas with the date
in cells a3,c3,e3.

I'm sure it is possible to do but I am struggling.

However once again thanks for your response.

 

[hmws]

Once again many thanks, but I can see the code isn't
going to do what I want.

The user selects a name from a drop down list in the
Source Worksheet. On selecting that name the formula in
cell A3 returns a reference to a cell address, in this
case M7. (If they select a different name it could return
anything from M1:M500)

I want to then go to cell M7 in worksheet (Target) and
enter now(). The code I entered in my original question
works perfectly for the contents of cell A3.

I can repeat it 21 times and know that would do exactly
what I want but I just think there must be an easier way.

Thanks very much for your time.

 

[Bernie Deitrick]

hmws,

Sub EnterDate2()
Dim myRange As Range
Dim myCell As Range

Set myRange = Worksheets("Source").Range("a3,c3,e3,a12,c12")

For Each myCell In myRange
Worksheets("Target").Range(myCell.Value).Value = Now()
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

Something like this????

Option Explicit
Sub enterDate()

Dim testRng As Range
Dim myVal As Variant

myVal = Worksheets("source").Range("a3").Value

Set testRng = Nothing
On Error Resume Next
Set testRng = Worksheets("target").Range(myVal)
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
With testRng(1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End Sub

 

[Guest]

Bernie

Thanks, exactly what I was looking for - it would have
taken me years to come up with that!

Once again thanks a lot

 

[Bernie Deitrick]

You're welcome. That's why we're here!