Interchanging data on different sheets

[mike112]

Hi guys,

I'm confusing myself a little so I'll try and explain my problem the best way I can.

I have multiple sheets, "Design Conditions", and "Template" and I have the letters "LW" placed in cell L7 in "Design Conditions", and in cell L48 in "Template" (which ='Desgin Conditions'!L7)

Basically what I'm trying to do is add something to the ='Desgin Conditions'!L7 of the "Template" where if I change the data in this cell, that it'll also change in the "Desgin Conditions" cell. Is this even possible? I thought I'd have a way to do it but thru all the other sheets I've stumped myself. I have many more sheets, but if I can figure out how to do it in one particular cell, I can base the rest of my changes on that.

Thanks for taking the time to look at this!

[Opv]

Hi guys,

I'm confusing myself a little so I'll try and explain my problem the best way I can.

I have multiple sheets, "Design Conditions", and "Template" and I have the letters "LW" placed in cell L7 in "Design Conditions", and in cell L48 in "Template" (which ='Desgin Conditions'!L7)

Basically what I'm trying to do is add something to the ='Desgin Conditions'!L7 of the "Template" where if I change the data in this cell, that it'll also change in the "Desgin Conditions" cell. Is this even possible? I thought I'd have a way to do it but thru all the other sheets I've stumped myself. I have many more sheets, but if I can figure out how to do it in one particular cell, I can base the rest of my changes on that.

Thanks for taking the time to look at this!

What about something like this in the "Design Conditions" Worksheet module?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L7")) Is Nothing Then
    Sheets("Template").Range("L48").Value = Target.Value
End If
End Sub

[mike112]

What about something like this in the "Design Conditions" Worksheet module?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L7")) Is Nothing Then
    Sheets("Template").Range("L48").Value = Target.Value
End If
End Sub

Thank you for the reply.

I tried this and it doesn't seem to be working, but in testing it made me think of another question. Is there a way to search for what row something would be in? For example: Deck is in row 18 on "Template", is there a way that I could search for "Deck" on "Design Conditons" to find what row it's in so I can fill in the appropriate cell by adding the column number? (May be more advanced and over my head haha)

Edit: I seemed to get that first part working. I had to add it to the Template Worksheet module.

[Opv]

Thank you for the reply.

I tried this and it doesn't seem to be working, but in testing it made me think of another question. Is there a way to search for what row something would be in? For example: Deck is in row 18 on "Template", is there a way that I could search for "Deck" on "Design Conditons" to find what row it's in so I can fill in the appropriate cell by adding the column number? (May be more advanced and over my head haha)

Edit: I seemed to get that first part working. I had to add it to the Template Worksheet module.

It should work if the code is placed in the "Design Conditions" Worksheet module and not in a standard module. In response to searching for the word, "Deck," and obtaining the row number, you can do something like the following in a standard module:

Sub testing()
Dim myRow As Integer
myRow = Sheets("Design Conditions").Cells.Find(What:="Deck").Row
MsgBox myRow
End Sub

[mike112]

It should work if the code is placed in the "Design Conditions" Worksheet module and not in a standard module.

That would be in "Sheet1 (Design Conditions) under Microsoft Excel Objects, correct? Or would I have to make a new module for that? (I'm sorry I'm completely new to the Excel VBA part). If that is correct, I had to also put it into Sheet2 (Template) to get it to work.

Also thank you very much for your help! It's greatly appreciated!

[Opv]

That would be in "Sheet1 (Design Conditions) under Microsoft Excel Objects, correct? Or would I have to make a new module for that? (I'm sorry I'm completely new to the Excel VBA part). If that is correct, I had to also put it into Sheet2 (Template) to get it to work.

Also thank you very much for your help! It's greatly appreciated!

Yes, the first snippet of code needs to be placed in the existing module for the Sheet you have named "Design Conditions".

However, for the second block of code for finding the word, "Deck," you do need to create and put that code in a standard Module.

[mike112]

I noticed if I put the same thing in the Design module (with the numbers a sheet changed of course) and it seems to lag a bit while it's going thru the process of switching the cell's text. Is there a way to fix that? Or is it normal in excel?

[Opv]

I noticed if I put the same thing in the Design module (with the numbers a sheet changed of course) and it seems to lag a bit while it's going thru the process of switching the cell's text. Is there a way to fix that? Or is it normal in excel?

I'm not exactly sure I know what you mean. Can you be more specific about what is happening, or can you post a sample of your workbook?

[mike112]

I'm not exactly sure I know what you mean. Can you be more specific about what is happening, or can you post a sample of your workbook?

It seems to only be when I have all the other Worksheets there as well. I just tried reducing it to the 2 sheets to post onto here and it works instantly. Basically what was happening is there is a pause (can't click anything) and it keeps flashing "Calculating: (2 Processor(s)): 0%) at the bottom.

[Opv]

It seems to only be when I have all the other Worksheets there as well. I just tried reducing it to the 2 sheets to post onto here and it works instantly. Basically what was happening is there is a pause (can't click anything) and it keeps flashing "Calculating: (2 Processor(s)): 0%) at the bottom.

The suggested VBA code shouldn't be causing that problem. If you can post a sample of the workbook, I'll see if I can figure out what might be going on.

[mike112]

The suggested VBA code shouldn't be causing that problem. If you can post a sample of the workbook, I'll see if I can figure out what might be going on.

[Opv]

It seems you included both the code for the Template worksheet in the Design Conditions sheet, as well as visa versa. I'm tinkering with the workbook. Will post an update shortly.

[Opv]

I think I now understand why the workbook was taking so long to calculate. What you are attempting to do is be able to make a change cell L7 in "Design Conditions" and then to automatically have cell L48 in the "Templates" worksheet to reflect the same value. At the same time, you are attempting to do the reverse. That causes a circular change, in that A is changing B and B is attempting to change A. I think I have solved the problem by temporarily disabling events in each block of code. Try it and see if you are still having a problem.

Also, bear in mind that code placed within a specific worksheet module (such as "Design Conditions" only monitors events within that sheet. That's why the code to monitor events in the "Template" worksheet should not be placed in the "Design Conditions" worksheet module but, rather, within the "Template" worksheet module. I hope this helps.

[mike112]

Yes! That is perfect! I appreciate your time and help.. I am a complete newbie when it comes to this so for you to explain everything helped me a lot.

[Opv]

Yes! That is perfect! I appreciate your time and help.. I am a complete newbie when it comes to this so for you to explain everything helped me a lot.

I'm glad it worked for you. If you are satisfied with the solution, please mark this thread "Solved".

[mike112]

Hey Opv,

I tried to send you a PM but my post count isn't above 10 so hopefully you'll check here again haha.

I had a quick question about making a new sheet and having this property go with it. Currently I have a macro that makes a new sheet while copying the layout/code with it by hitting Ctrl + Q. When I make a change the cell on Design Conditions, it changes fine in Template, but does not change in the new sheets (while having the same code as Template). Is there a way to do it so it'll work for all the new sheets I add? They sometimes go to upwards of 100 sheets.

Thanks!

[Opv]

Hey Opv,

I tried to send you a PM but my post count isn't above 10 so hopefully you'll check here again haha.

I had a quick question about making a new sheet and having this property go with it. Currently I have a macro that makes a new sheet while copying the layout/code with it by hitting Ctrl + Q. When I make a change the cell on Design Conditions, it changes fine in Template, but does not change in the new sheets (while having the same code as Template). Is there a way to do it so it'll work for all the new sheets I add? They sometimes go to upwards of 100 sheets.

Thanks!

You can change the appropriate line in all the new worksheets by using a loop, something like:

Dim ws as Worksheet
For each ws in ThisWorkbook.Worksheets
'Your code to change the appropriate cell in the worksheet
Next

[mike112]

You can change the appropriate line in all the new worksheets by using a loop, something like:

Dim ws as Worksheet
For each ws in ThisWorkbook.Worksheets
'Your code to change the appropriate cell in the worksheet
Next

Still a little confused while trying to actually implement it. I've tried some variations with no luck.

[Opv]

Still a little confused while trying to actually implement it. I've tried some variations with no luck.

What specific ranges do you want to copy from and to?

[mike112]

What specific ranges do you want to copy from and to?

Well, just using those cells we used in the example I put up. How ever, I have the sheets BHD and MAIN SHEET that I don't want to include it on, but for every other sheet I would be using this.

Also, the code would go into the Design Conditions code, correct?