Goto a cell from a macro

stemby

New Member
Joined
Mar 13, 2002
Messages
26
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You could try something like the following:
Code:
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-17 15:11
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top