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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.