Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: I want a Macro to do my work
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is very tedious so I would like a macro to do the following.

    I have the following data and I need it to be inserted in its related worksheet in a particular cell.

    Column A, Column B
    030, 2500
    031, 3600
    032, 4500
    040, 1500
    045, 8900
    etc., etc

    Column A is the name of the worksheet where I want the data in Column B inserted.

    example. 030, 2500 I would go to worksheet 030 and in cell F12 enter 2500.

    I know there is a way to automate this, but I don't know how, can someone help me.

    Thanks

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One question first: Do those commas actually appear in your data? If they do then get rid of them. Try the following code:

    Dim rng As Range
    Set rng = Intersect(ActiveSheet.UsedRange, Columns("A"))
    For Each cell In rng
    Worksheets(cell.Value).Range("F12").Value = ActiveSheet.Range("B" & cell.Row)
    Next

    Edit as needed.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Essex, England
    Posts
    459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think this does what you want. Give it a go and let me know:

    Sub Macro1()


    myRow = 1

    Do Until Cells(myrow, 1) = ""
    myName = Cells(myrow, 1)
    Sheets("sheet" & myName).Cells(12, 6) = Cells(myRow, 2)
    myRow = myRow + 1
    Loop

    '
    End Sub



    HTH

    GaryB

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey guys thanks for the help, I tried both macros and I got the following error message for both.

    Run Time Error '9'
    Subscript out of range.

    Please advice. Thanks

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try Gary B's macro but modify the follwing line to excelude "sheet" & from:

    Sheets("sheet" & myName).Cells(12, 6) = Cells(myRow, 2)

    to:

    Sheets(myName).Cells(12, 6) = Cells(myRow, 2)


    It's never too late to learn something new.

    Ricky

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ricky, I tried your suggestion and I got the same error. Do you have any ideas why? I can't figure it out.

    My vb knowledge is limited and I don't really understand the code. I know what it can do and is capable of.

    Thanks

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following code just worked for me.

    Sub Macro1()
    myRow = 1
    Do Until Cells(myRow, 1) = ""
    myName = Cells(myRow, 1)
    Sheets(myName).Cells(12, 6) = Cells(myRow, 2)
    myRow = myRow + 1
    Loop
    End Sub

    It's never too late to learn something new.

    Ricky

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ricky I got the same message???? I don't know why??

    I have the same data as I do in the first message and then I have the data in "Sheet 1" and then from left to right I have sheets 30, 31, 33, 40 and 45. This is actually a summary of the 85 sheets there really are, I am doing this on a test work book.

    Any suggestions? Or info I can provide?

    Thanks

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    See below for info in error message...

    http://www.mrexcel.com/board/viewtop...c=4841&forum=2

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al Chara,

    What does Intersect do?

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
  •