Dynamic macro's

E

Ekser

Thank you so much!

I was sure there must be a way to do this dinamically, what's the us
of programming otherwise?!

Still, it seems pretty unclear to me, since I don't have that muc
experience with VBA.

According to the 'help', first I need to declare a Private Sub
something like this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim appWD As Word.Application

End Sub

Now, if my understanding us good, this should remind the selected ro
number, and put it into Target

My question is: how do I transfer that value into my macro?

I have tried something like this:

Sub Test()

Dim RowNo As Range
RowNo = Target.Address
Sheets("Data").Select
Range("A" & RowNo).Copy
Sheets("Template").Select
Range("B3").Select

But the program returns some rude message!

Whata...?


Cheers
 
D

Dave Peterson

You've got a few problems with your code:

Sub Test()

Dim RowNo As Range
RowNo = Target.Address
Sheets("Data").Select
Range("A" & RowNo).Copy
Sheets("Template").Select
Range("B3").Select
'... rest of code
end sub

First, you declared RowNo as a range, but used it as a string (in the
target.address line).

And the target.address will look like: $a$1 if you changed a single cell.

So
range("A" & "$A$1").copy
looks funny.

and selecting sheets and selecting cells in code located in a worksheet module
is going to be a problem when you get closer:


I'm not sure what you're doing, but this might get you closer:


Sub Test2()

worksheets("data").range("A" & target.row).copy _
destination:=worksheets("template").range("b3")
'... rest of code
end sub

You can do lots of stuff without selecting.
 
Top