Results 1 to 10 of 10

Thread: Changeable workbook refrence

  1. #1

    Changeable workbook refrence



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys!

    I am trying to write a macro and I want to copy a cell from another workbook. The problem is that i want this workbook to be changeable. For example, I want to run the Macro with a cell from workbook 1, then I want to run the same macro with the same cell from workbook 2, and so on.
    I thought of copying the macro and just change the workbook, but I have over a 1000 workbooks.

    Can anybody help me? I am using the following code

    Range("C2").Select
    ActiveCell.FormulaR1C1 = "='[workbook1]Sheet1'!R9C2"

    Thanks

  2. #2
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    142
    Articles
    0
    Excel Version
    365
    Likely a Formula and A1 notation would suffice.
    e.g.
    Code:
    Sub Main()  Dim i As Long, v() As Variant, s As String
      v() = Array("Workbook1", "Workbook2", "Workbook3")
      For i = 0 To 2
        '"='[workbook1]Sheet1'!R9C2"
        s = "='[" & v(i) & "]Sheet1'!R9C2"
        Debug.Print s
        Range("C" & 2 + i).FormulaR1C1 = s
      Next i
    End Sub

  3. #3
    Thanks for the reply,

    With that formula i would have to rewrite the macro each time i want to add another workbook, the problem is that i am leaving this macro on a workbook that is going to be operated by a person who doesn't know how to change the macro.
    Isn't there a way that if i write the name of the woorkbook on a a specific cell, it would run the macro with that workbook, and if i write the name of another workbook it would run that one?

    Thanks

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    794
    Articles
    0
    Excel Version
    Excel 2010
    nunofrcds, your objective is not clear.

    Instead telling us you have over 1000 workbooks and showing us 2 lines of code that is putting something from somewhere into cell C2 of some worksheet in some workbook, lets limit the workbooks to 3.
    Call them "Main", "Workbook1" and "Workbook2".
    In words, can you go through the procedure of what you're trying to do and what you expect to happen because of that.

  5. #5
    Sorry if i wasn't clear, i don't have a vast knowledge of vba code.

    I want to run a macro on Main, that copies a cell from workbook 1. Then, i will make a copy of main,let's call it main 2, and i want to run the same macro, but instead of copying a cell from workbook 1, I want it to copy the exact same cell from workbook2, to main 2 . I Know that i could just change the reference on the macro from workbook 1 to workbook 2, however as I explained earlier the person who is operating the main, main2, main3, and so on does not know the first thing about macros. What i was suggesting was to refrence a cell that would contain the name of, for example, workbook 1, and if i changed it to workbook 2, it would automaticly change the line of code from the macro wich contains the reference to workbook 1, to refer workbook 2. Do you think you can help me?

    Thanks for replying

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    794
    Articles
    0
    Excel Version
    Excel 2010
    No, sorry, I don't understand what you're trying to do other than take 1000 workbooks and turn them into 2000 workbooks.

    I think you're telling us how you're trying to achieve something, not what you're trying to achieve.

    Hopefully another member will interpret things differently and be able to help.

  7. #7
    I will try one more time ,

    I want to copie a selection, let's say c2:g500, from workbook 1 to main 1 . Then, i want to copie the selection c2:g500 from workbook2 to main 2.
    What i was trying to do was, create a workbook that would serve as a model for all the mains, where i would only have to change the name of the workbook from which i was trying to copy c2(workbook1,workbook2,workbook3,...).
    So, i want to creat a model for the mains, that the only thing i have to write in it, is the name of the workbook from which i want to copie c2:g500, and then it copies me that.

    Thanks

  8. #8
    Any ideas, I really could use some help in this

    Sorry I am being so annoying about this, but I really need this done

  9. #9
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    142
    Articles
    0
    Excel Version
    365
    I don't understand your goals I suspect. Your first post showed a link. I guessed that was what you wanted changed. Of course changing link references could have been done too.

    Now you say that you want to copy. I guess you want the user to pick what workbooks to copy from and to? If that is the case, you can set cells up to do that or add a dialog to ask which ones. Of course the paths have to be included, or set somewhere. The macro would have to check if the FROM and TO workbooks exists and then if the sheets to copy FROM to exist. You did not not say which sheets.

  10. #10
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    376
    Articles
    0
    Excel Version
    2020
    Quote Originally Posted by nunofrcds View Post
    Any ideas, I really could use some help in this

    Sorry I am being so annoying about this, but I really need this done

    In that case: hire a developer. Your skills clearly do not match your ambitions.

Tags for this Thread

Posting Permissions

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