Solved: Moving data

[Tenspeed39355]

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

[mdmackillop]

Hi Tenspeed,
Can you post a small sample of Sheets 1, 2 and what you would like to see on sheet 3?

[malik641]

Hey Max

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

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

[/VBA]
Now place this in the Sheet2 Module:
[VBA] Option Explicit
Private Sub Worksheet_Calculate()
WatchValues "Sheet2"
End Sub
[/VBA]
Now create a new module and place this code in it:
[VBA] 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
[/VBA]
Hope this is what you were looking for

[Zack Barresse]

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

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

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

[malik641]

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

So check THIS out:
Forget the worksheet modules.

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

[/VBA]
And place this into a new module:
[VBA] 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
[/VBA]

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

[austenr]

Nice one Joseph

[alimcpill]

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]

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

[malik641]

Nice one Joseph

Thank you!

[Tenspeed39355]

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