Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Goto a cell from a macro

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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, NateO

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

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •