PDA

View Full Version : Solved: Moving data



Tenspeed39355
11-12-2005, 06:40 AM
Good morning guys. Here is the problem. I have data in Sheet1 column J and data in Sheet2 column J. The data will change from time to time due to the data
changing in Columns B thru I. How can I put the data in column J from both Sheets in Sheet3 so I can see it change when the data in Sheet1 and 2 Column J changes.. Thanks for your time with this problem.
Max
P.S. I forget to say that there is around 600 rows of data. in column J:hi:

mdmackillop
11-12-2005, 06:48 AM
Hi Tenspeed,
Can you post a small sample of Sheets 1, 2 and what you would like to see on sheet 3?

malik641
11-12-2005, 10:32 AM
Hey Max :hi:

This seems to work alright. Check it out:
In sheet3, A1="Sheet1" A2="Sheet2"

Place this in the Sheet1 Module:
Option Explicit
Private Sub Worksheet_Calculate()
WatchValues "Sheet1"
End Sub


Now place this in the Sheet2 Module:
Option Explicit
Private Sub Worksheet_Calculate()
WatchValues "Sheet2"
End Sub

Now create a new module and place this code in it:
Option Explicit
Sub WatchValues(SheetName As String)
Application.ScreenUpdating = False
Dim iLastRow1 As Long
Dim iLastRow3 As Long
If SheetName = "Sheet1" Then
iLastRow1 = Sheets("Sheet1").Cells(Rows.Count, "J").End(xlUp).Row
iLastRow3 = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet3").Range("A2" & Cells(iLastRow3, "A")).ClearContents
Sheets("Sheet1").Range(Cells(1, "J"), Cells(iLastRow1, "J")).Copy
Sheets("Sheet3").Range("A2").PasteSpecial xlValues
ElseIf SheetName = "Sheet2" Then
iLastRow1 = Sheets("Sheet1").Cells(Rows.Count, "J").End(xlUp).Row
iLastRow3 = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet3").Range("B2" & Cells(iLastRow3, "B")).ClearContents
Sheets("Sheet2").Range(Cells(1, "J"), Cells(iLastRow1, "J")).Copy
Sheets("Sheet3").Range("B2").PasteSpecial xlValues
End If
Application.CutCopyMode = False
Application.ScreenUpdating = False
End Sub

Hope this is what you were looking for :thumb

Zack Barresse
11-12-2005, 10:42 AM
...
Place this in the Sheet1 Module:
Option Explicit
Private Sub Worksheet_Calculate()
WatchValues "Sheet1"
End Sub


Now place this in the Sheet2 Module:
Option Explicit
Private Sub Worksheet_Calculate()
WatchValues "Sheet2"
End Sub
...

Seems to me a workbook level event may be worth it rather than putting code in each worksheet. ;)

malik641
11-12-2005, 11:20 AM
Seems to me a workbook level event may be worth it rather than putting code in each worksheet. ;)Good idea.

And you know what...I don't really like the code I gave out..too sloppy :mkay

So check THIS out:
Forget the worksheet modules.

Insert this into the "ThisWorkbook" module:
Option Explicit
Private Sub Workbook_SheetCalculate(ByVal sh As Object)
WatchValues
End Sub


And place this into a new module:
Option Explicit
Sub WatchValues()
Application.ScreenUpdating = False
Dim copyRng1 As Range
Dim copyRng2 As Range
Dim iRowSh1 As Long
Dim iRowSh2 As Long
Dim iRowSh3A As Long
Dim iRowSh3B As Long
With Sheets("Sheet1")
iRowSh1 = .Cells(Rows.Count, "J").End(xlUp).Row
Set copyRng1 = .Range("J1:J" & iRowSh1)
End With
With Sheets("Sheet2")
iRowSh2 = .Cells(Rows.Count, "J").End(xlUp).Row
Set copyRng2 = .Range("J1:J" & iRowSh2)
End With
With Sheets("Sheet3")
iRowSh3A = .Cells(Rows.Count, "A").End(xlUp).Row + 1
iRowSh3B = .Cells(Rows.Count, "B").End(xlUp).Row + 1
.Range("A2:A" & iRowSh3A).ClearContents
.Range("B2:B" & iRowSh3B).ClearContents

Sheets("Sheet1").Range(copyRng1.Address).Copy
.Range("A2").PasteSpecial xlValues

Sheets("Sheet2").Range(copyRng2.Address).Copy
.Range("B2").PasteSpecial xlValues

End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Okay...NOW we're good here :thumb (at least I think :think: )

austenr
11-12-2005, 04:45 PM
Nice one Joseph http://vbaexpress.com/forum/images/smilies/023.gifhttp://vbaexpress.com/forum/images/smilies/clap.gif

alimcpill
11-12-2005, 04:59 PM
errrrr what am I missing here? Why can't you just use a formula and drag it down? ie something like =Sheet1!J1 and =Sheet2!J1 etc?

malik641
11-12-2005, 07:16 PM
errrrr what am I missing here? Why can't you just use a formula and drag it down? ie something like =Sheet1!J1 and =Sheet2!J1 etc?Where's the fun in that? :)

Not to mention if he deletes a row then he will get #REF! errors :yes

malik641
11-12-2005, 07:42 PM
Nice one Joseph http://vbaexpress.com/forum/images/smilies/023.gifhttp://vbaexpress.com/forum/images/smilies/clap.gif
Thank you! :vv

Tenspeed39355
11-13-2005, 05:34 AM
Ok guys I found another way to solve the problem. I can use VLookup to check back and pick the data I need. I want to thank you for your time and trouble with this
I will rate the thread
Max