VBA: Copying formula from one worksheet to another

[hydz1213]

Hello! I really need answers from the experts here....

I've been trying to solve this problem but nothing worked!

What i basically want is to have this formula to be copied into a cell in "Consolidated Report" where the SheetName varies.

=SheetName!$B$35

What i'm trying to do is create new worksheet & rename the sheet name base from inputbox value. Then insert row in the "Consolidated Report" worksheet where it shows all sheet names in "A" column and total figures from each worksheet in "B" column. I managed to create worksheet, rename the sheet name, and insert a row in "Consolidated Report" with the new sheet name in "A" column.

But what I can't do is copy the formula in "B" column that shows total figures from the referenced sheet. So that when user adds details, the total for that particular sheet AND in "Consolidated Report" sheet will also be updated.

I badly need enlightment on this. Thanks a lot!
-hydz1213

Please Login or Register  to view this content.

[codeslizer]

Well, I can give you some light.. You can use INDIRECT function in column B to create the range from text available in A column.

Please Login or Register  to view this content.

Tip: To further flexibility, you can define "!B2" as "!B" & ROW() or similar to get the right cell for your needs

Now, we have the function, but I want it to get populated everytime, I feed sheet names in column A. So, write the following code in the Worksheet_Change() event to capture the changes in column A and put the formula to column B.

Please Login or Register  to view this content.

This should do the trick. Let me know for if this isn't what you need. It would be great if you could upload a sample work file removing all your sensitive data if any. Am attaching a sample implementation of what I said.

[hydz1213]

Thank you so much codeslizer for the help! It does allow me to reference a range of specific worksheet. Good job!