Goto a cell from a macro

[stemby]

Please help....it's driving me crazy!

I have 3 worksheets: Input, Sheet1 and Sheet2.

On the input sheet I have in cell A1, a sheetname. In cell A2, a ROW number. In cell A3, a COLUMN number. I.E. A1=Sheet2, A2=49, A3=C. Also on the input worksheet I have a range of numbers (for example in a5-a10).

How can I write a macro, which I can assign to a button which will copy the data from the input sheet, move to the correct sheet, and then paste in the data.

Any help any of you experts can give me would be GRATEFULLY received. Thanks

[NateO]

You could try something like the following:

Sub sel()
Dim col As String
Dim rw As String
Dim myRng As String
cur = ActiveCell.Address
cursht = ActiveSheet.Name
Application.ScreenUpdating = False
Range("a5:a10").Copy
sht = Range("a1").Value
col = Range("c1").Value
rw = Range("b1").Value
myRng = col & rw
Worksheets(sht).Select
shtcl = ActiveCell.Address
Range(myRng).Select
ActiveSheet.Paste
Range(shtcl).Select
Worksheets(cursht).Select
Range(cur).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Make a button from the "forms" toolbar and assign the macro. After assigning the macro, you may want to rename it to "private sub sel()" This will keep the salesman from seeing the macro in run macro dialog.

Also, you may want to use data validation to make sure the references in a1:c1 are appropriate.

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-03-17 15:11 ]

[stemby]

Thanks for that, I'll give it a go tonight.
Cheers

[Dave Hawley]

Hi Stemby

No real need to select any Objects in Excel, so you could just use:

Sub DoIt()
Dim strSheet As String
strSheet = Range("A1")
Range("B1:B5").Copy Destination:= _
Sheets(strSheet).Range(Range("A3") & Range("A2"))
End Sub

[stemby]

cheers. I'll give it a whirl...

[stemby]

Works a treat, but is there any way I can adapt it so that it only pastes the cell values?

As it stands it pastes the cells 'format' as well, which is what I don't want as is it mucks up the formats of my destination cells.

Thanks.

[Dave Hawley]

Certainly Stemby

Sub DoIt()
Dim strSheet As String
strSheet = Range("A1")
Range("B1:B5").Copy
Sheets(strSheet).Range(Range("A3") & Range("A2")) _
.PasteSpecial xlValues
Application.CutCopyMode = False
End Sub

[stemby]

Thanks a million....you're a star!