PDA

View Full Version : [SOLVED] Interchanging data on different sheets



mike112
05-29-2012, 07:52 AM
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
05-29-2012, 08:16 AM
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
05-29-2012, 08:29 AM
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
05-29-2012, 08:44 AM
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
05-29-2012, 08:53 AM
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
05-29-2012, 08:59 AM
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
05-29-2012, 09:14 AM
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
05-29-2012, 09:20 AM
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
05-29-2012, 09:45 AM
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
05-29-2012, 10:22 AM
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
05-29-2012, 11:16 AM
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
05-29-2012, 12:30 PM
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
05-29-2012, 01:26 PM
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
05-30-2012, 06:01 AM
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
05-30-2012, 07:07 AM
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
05-31-2012, 06:16 AM
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
05-31-2012, 07:53 AM
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
05-31-2012, 08:45 AM
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
05-31-2012, 08:47 AM
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
05-31-2012, 08:51 AM
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? :doh:

Opv
05-31-2012, 09:04 AM
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? :doh:

My understand is that your initial objective was to have cell L48 in Sheets("Template") changed to match the cell L7 in Sheets("Design Conditions"), and visa versa, whenever either of those cells is changed.

Which cell in the other worksheets (excluding the two noted exceptions) do you want to change, L7, L48 or some other cell? Also, do you want the change to occur regardless of whether the change was initiated from within Design Conditions or Templates? Please provide a bit more explanation as to your ultimate objective.

mike112
05-31-2012, 09:08 AM
Sorry.. It's easy to see what I want in front of me, and harder to explain it in words.

Basically, any new sheet I create will look exactly the same as "Template" (It will copy the same data and will be changed manually). I'm looking to have it so (for the example we used before) if I change L7 on Design Conditions, it will change L48 on every sheet except the noted exceptions and visa versa.

Hopefully that clears the confusion on my part.

Opv
05-31-2012, 09:27 AM
Sorry.. It's easy to see what I want in front of me, and harder to explain it in words.

Basically, any new sheet I create will look exactly the same as "Template" (It will copy the same data and will be changed manually). I'm looking to have it so (for the example we used before) if I change L7 on Design Conditions, it will change L48 on every sheet except the noted exceptions and visa versa.

Hopefully that clears the confusion on my part.

That is helpful. So, if I'm understanding you correctly, you are wanting to be able to change ALL worksheets (excluding the two noted exceptions) and you are wanting those worksheets to be changed regardless of whether the change is initiated in Design Conditions or in Template?

mike112
05-31-2012, 10:36 AM
That is helpful. So, if I'm understanding you correctly, you are wanting to be able to change ALL worksheets (excluding the two noted exceptions) and you are wanting those worksheets to be changed regardless of whether the change is initiated in Design Conditions or in Template?

That's exactly what I want to be able to do! Ha ha, sorry I did not include that before.

Opv
05-31-2012, 10:42 AM
That's exactly what I want to be able to do! Ha ha, sorry I did not include that before.

OK. Try this. REPLACE the existing code in the DESIGN CONDITIONS worksheet module with the following:



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L7")) Is Nothing Then
Application.EnableEvents = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "BHD" Or ws.Name = "MAIN SHEET" Then
GoTo continueHere
Else: ws.Range("L48").Value = Target.Value
End If
continueHere:
Next
Application.EnableEvents = True
End If
End Sub


and REPLACE the code in the TEMPLATES worksheet module with the following:



Private Sub Worksheet_Change(ByVal Target As Range)
'Exclude Worksheets BHD and MAIN SHEET
Application.EnableEvents = False
If Not Intersect(Target, Range("L48")) Is Nothing Then
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "BHD" Or ws.Name = "MAIN SHEET" Then
GoTo continueHere
Else: ws.Range("L48").Value = Target.Value
End If
continueHere:
Next
Sheets("Design Conditions").Range("L7").Value = Target.Value
Application.EnableEvents = True
End If
End Sub

mike112
05-31-2012, 10:52 AM
That is perfect! Thank you once again, Opv, you've been amazing in helping me!

Opv
05-31-2012, 10:54 AM
That is perfect! Thank you once again, Opv, you've been amazing in helping me!

I'm glad it worked for you.