Changeable workbook refrence

nunofrcds

New member
Joined
Aug 12, 2016
Messages
14
Reaction score
0
Points
0
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 :)
 
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
 
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:)
 
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.
 
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 :)
 
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.
 
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:)
 
Any ideas, I really could use some help in this

Sorry I am being so annoying about this, but I really need this done
 
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.
 
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.
 
Back
Top