copy and keep updating

Microsoft Book: office excel 2003 inside...
September 2, 2009 at 02:19:11
Specs: Windows XP, pentium 4 3.00Ghz. 1gb ram
I want to autocopy values(number and text) from:

Sheet1 Col. B16:B35 and B77:B96 to sheet2 Col. B18:B37 and B50:B69
Sheet1 Col. C16:C35 and C77:C96 to sheet2 Col. F18:F37 and F50:F69
Sheet1 Col. D16:D35 and D77:D96 to sheet2 Col. G18:G37 and G50:G69
Sheet1 Col. E16:E35 and E77:E96 to sheet2 Col. H18:H37 and H50:H69
Sheet1 Col. F16:F35 and F77:F96 to sheet2 Col. I18:I37 and I50:I69

The sheet2 values need to be able to update changes in sheet1 in realtime.

I used to insert/delete or pull up or down values in sheet1 and i have tried with vlookup and copy then paste special which does not yield satisfactory result in sheet2.


See More: copy and keep updating

Report •


#1
September 2, 2009 at 05:21:24
Simply use, for example, in Sheet2 B18, enter =Sheet1!B16
Then copy it down.


Anything entered into Sheet1 B18 will automatically appear in Sheet 2 B16.

HTH
Bryan


Report •

#2
September 4, 2009 at 03:05:53
I am pained that i am unable to make my question clearly. So, i am rewritting my question which i think will achieve my objective.

If in sheet1 column H contain "Copy" then:

copy Sheet1 B16:B35 and paste to Sheet2 B18:B37
copy Sheet1 C16:F35 and paste to Sheet2 F18:I37


Example:

Sheet1
-----A----B--------C------D-------E--------F--------G--------H---
16------20mm-----10------12------No.------120-------------------
17------25mm-----20------10------No.------200--------------Copy-
18------32mm-----50------10------No.------500--------------Copy-
19------40mm-----60------10------No.------600-------------------
20------50mm-----100-----20------No.------2000-------------Copy-
.
.
.
77------20mm-----10-----12------No.------120---------------Copy--
78------25mm-----20-----10------No.------200---------------Copy--
79------32mm-----50-----10------No.------500---------------------
80------40mm-----60-----10------No.------600---------------------
81------50mm-----100----20------No.------2000--------------Copy...


Sheet2
-----A----B----C----D-----E-----F--------G--------H--------I----
18------25mm-------------------20-------10-------No.-----200---
19------32mm-------------------50-------10-------No.-----500---
20------50mm-------------------100------20-------No.-----2000--
.
.
.
50------20mm-------------------10-------12-------No.-----120---
51------25mm-------------------20-------10-------No.-----200---
52------50mm-------------------100------20-------No.-----2000--


ABCDEFHI are column and 16, 17, 18, 19... are row numbers. Also please note of the row numbers which the data get adjusted automatically at top, begining row18 and row50 in sheet2.


Report •

#3
September 4, 2009 at 05:26:10
re: "If in sheet1 column H contain "Copy"...

Are you saying that you want the ranges to be copied/updated as soon as you enter the text "Copy" in column H?

Are all of the updates supposed to be happen if "Copy" appears anywhere in column H or do you want to copy only the specific line that contains "Copy" in column H?

re: "Also please note of the row numbers which the data get adjusted automatically at top, begining row18 and row50 in sheet2."

I do not understand this statement.


Report •

Related Solutions

#4
September 5, 2009 at 01:20:33
I want to copy row containing "Copy" in column H (But don't want to copy entire row). The following code does exactly as i need except 2 problems:

Sub Transfer()
'Transfer data from active worksheet to Sheet2
With Worksheets("Sheet2")
.Range("B16:B35").Value = Range("B18:B37").Value
.Range("F16:F35").Value = Range("C18:C37").Value
.Range("G16:G35").Value = Range("D18:D37").Value
.Range("H16:H35").Value = Range("E18:E37").Value
.Range("I16:I35").Value = Range("F18:F37").Value
End With
End Sub

Problem1: Instead of Range ("B16:B35").Value = Range("B18:B37").Value, I may use Range ("B16:B22").Value = Range("B18:B24").Value. That is, the range I use may fluctuate everytime. That's why I propose to have "Copy" in column H (In place of "Yes")as seen here: http://en.allexperts.com/q/Excel-10...


Problem 2: Whenever I made changes in sheet1(Active worksheet/Master sheet) it is not reflecting in sheet2. Hence, I have to run the code everytime I made changes in sheet1 so that sheet2 get updated.


Report •

#5
September 5, 2009 at 16:00:39
We're getting closer, but I still need a little clarification.

Please do not take offense, but I think it would help if you answered my questions directly, one by one, with a Yes or No, Right or Wrong, etc., adding more detail if required. Since your requirements appear to be rather complex, I want to make sure I understand your needs before I propose, or even spend time working on, any code.

1 - At this point I have to assume that since you will be varying the Range that will get copied, we can not hard code any Ranges like was done in the sample code you provided. We have to have the code determine which rows to copy based on which rows contain the word Copy in Column H. Is that a correct assumption?

2 - I have noticed that in the examples you have given, the destination row is sometimes 2 rows higher than the source row. (e.g. Sheet1!B16:B35 to Sheet2!B18:B37) but in other cases the destination is 22 rows lower than the source (e.g. Sheet1!B77:B96 to Sheet2!B50:B69) Am I interpreting the examples correctly?

2a - If the answer to 2 is Yes, how would you expect the code to know what row to use as the destination, based on just the word Copy in Column H? Will there be some other key for the code to check?

3 - I see from your examples that Column B gets copied to Column B, C gets copied to F, and D, E, and F get copied to G, H and I respectively. Am I interpreting the examples correctly?

4 - With regards to your Problem 2, where you state that you want these updates to occur automatically, let me explain how that could work so you can tell us how you want it to happen.

Excel has something called a Worksheet_Change event which can monitor a worksheet and "fire" (run) whenever a change occurs within the worksheet. Typically, the first few lines of the code will check and see what changed or where did the change occur or some combination thereof.

e.g.

If Target.Address = "$H$1" Then... do something

or

If Target.Column = 4 Then... do something

or

If Target.Address = "$B$16" And _
   Range("H" & Target.Row) = "Copy" _
   Then... do something

So, you have to tell us when you want the update to occur.

Do you want the range to be copied as soon as you enter Copy in Column H? Do you want the range to be copied as soon as you enter data in the range and there is also the word Copy in Column H of that row? etc.

If you want the updates to occur automatically without having to run the code manually, you have to tell us what event(s) have to occur for the code to run.


Report •

#6
September 5, 2009 at 22:25:11
Thanks Derby. Now I can feel myself at the end of a tunnel.

1 - Yes
2 - Yes
2a- This is what keep me thinking. I feel isn't it can be set condition that 'If there is more than 15 blank cells between copy in column H, then the rows containing 'Copy' after 15 blank cells be copied to B50 ...
3 - Yes
4 - I want the update to occur automatically whenever there is a change in column F of sheet1


Report •

#7
September 6, 2009 at 06:40:10
Closer still...

First, I apologize for an error in my previous post. I said that some destination rows were 22 rows lower than the source. That number should have been 27.

I mention that because that is the last clarification I need.

In response to 2a you said:

If there is more than 15 blank cells between copy in column H, then the rows containing 'Copy' after 15 blank cells be copied to B50

1 - I assume you mean "after 16 or more blank cells" since your criteria was "more than 15 blank cells".

2 - Is that 15 or more blank cells in both directions, up and down?

3 - rows is plural. Obviously, you can't copy more than one row to a single row.

So let's say you make a change to Sheet1!F77 and there are 16 rows without "Copy" in H. Per your instructions, the data from Sheet1!Row 77 should get copied to Sheet2!Row 50. What happens if the next change you make also happens in a cell where there are 16 blank cells between "Copy"? Does that row also get copied to Sheet2!Row 50 or does it get copied to Sheet2!Row 51 and so on?

Other than working on that issue, the code appears to be fairly simple for the "2 rows higher" cases. Why don't you try this and let me know if it does what you expect, ignoring the "blank cells" issue at this point.

Right click the sheet tab for Sheet1, choose View Code and paste this in. If it does what you want, then all we have left is to work on getting myRowDiff to be the correct value based on the "blank cells" issue.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 6 And Range("H" & Target.Row) = "Copy" Then
  myRowDiff = 2
   Range("B" & Target.Row).Copy _
          Destination:=Sheets(2).Range("B" & Target.Row + myRowDiff)
   Range("C" & Target.Row).Copy _
          Destination:=Sheets(2).Range("F" & Target.Row + myRowDiff)
   Range("D" & Target.Row & ":F" & Target.Row).Copy _
          Destination:=Sheets(2).Range("G" & Target.Row + myRowDiff)
 End If
End Sub


Report •

#8
September 6, 2009 at 23:05:26
1 - Yes
2 - Yes
3 - There are only two pages each in sheet1 and sheet2. Chances of getting another 15 blank cells may not happen. If Sheet1 Row 77 is copied to sheet2 Row50 then all row in sheet1 after row 77 should be copied to sheet2 row 51, 52, 53 and so on.

The code is working but I have to double-click each and every row in Col. F to execute Copy. Can you change Col. F to Cell D8 to trigger copy/update(refers to response No. 6 (4)?

I am taking your precious time. So, please copy address below to the address bar to download my excel file(75k size). You may be required to click Skip, Skip > Then 'Document' at top and then at extreme right of Nabam.xls to download the file(wait for few seconds to download). (Username = Nabam and Password = 007aja)


https://www.keepandshare.com/business/logout.php?i=784222


Report •

#9
September 7, 2009 at 07:33:35
The double-click-to-update issue is caused by the answer to a question I did not ask - and should learn to ask from now on.

I had assumed that you were manually changing the data in Column F. Formulae do not cause the Worksheet_Change event to fire and since Column F contains a formula, you have to manually edit it (double click) for VBA to recognize the "change".

There is also a Worksheet_Calculate event which will fire whenever the sheet recalculates, but this event is not Target specific. You could use it for this project, but it will fire with each and every recalculation which can be pretty inefficient.

Before I work on the 15 blank line issue, which might be pretty complicated, let me offer this:

What if we used the Worksheet_BeforeDoubleClick and the Worksheet_Change event in conjunction?

When you double click a cell in Column H, this code will either place Copy in it - if it is currently empty - or it will clear the cell if it wasn't empty.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If Target.Column = 8 And Target = "" Then
   Target = "Copy"
  Else: Target = ""
 End If
End Sub

Now we change the Worksheet_Change event to monitor Column H and if the change is such that the cell now contains Copy, we copy the data from B:F to Sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 8 And Range("H" & Target.Row) = "Copy" Then
  myRowDiff = 2
   Range("B" & Target.Row).Copy _
          Destination:=Sheets(2).Range("B" & Target.Row + myRowDiff)
   Range("C" & Target.Row).Copy _
          Destination:=Sheets(2).Range("F" & Target.Row + myRowDiff)
   Range("D" & Target.Row & ":F" & Target.Row).Copy _
          Destination:=Sheets(2).Range("G" & Target.Row + myRowDiff)
 End If
End Sub

To use this method, you would make your quantity or price or whatever changes to the data in B:F and then double click in column H.

Obviously there are many other ways to trigger the code, but the word Copy would at least give you a visual indication of what you've copied for that particular job.

Let me know if that works for you and then we can move on to the 15 row issue.


Report •

#10
September 8, 2009 at 04:20:25
Yes, the code works for me except 15 row issue

Report •


Ask Question