VBA to copy values from one sheet to next column on another sheet

Equine Investor

Board Regular
Joined
Nov 20, 2002
Messages
99
Hi,

I have a workbook Sheet1, which has stockmarket prices in column O.
Column A has the stock code.

This data is copied to Sheet2
Column A Sheet2 has the stock code
Column B has the stock price copied from Sheet1 column O.

What I'm trying to get, is if the price changes in Sheet1 column O, (for any stock), it is copied to Sheet2 in the next available column for that stock.

I currently have a macro that does the initial part:

Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A5:A45").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Sheets("Sheet1").Select
    Range("O5:O45").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Range("C1").Select
End Sub
 
Last edited:

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
Hi Equine Investor,

You could try something like this in your sheet1 Private Sub Worksheet_Change:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 15 Then
Val2 = Cells(Target.Row, Target.Column)
Co = Sheets("Sheet2").Range("IV" + Mid$(Str$(Target.Row), 2)).End(xlToLeft).Column + 1
Sheets(2).Cells(Target.Row, Co) = Val2
End If
End Sub

ColinKJ
 

Equine Investor

Board Regular
Joined
Nov 20, 2002
Messages
99
Thanks Colin,

I tried pasting it in the VBA module, but it doesn't seem to do anything even though the prices have changed?
 

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
Hi Equine Investor,

What changes the value in the Sheet 1 Column O, do you do it manually, or is it a formula.

ColinKJ
 

Equine Investor

Board Regular
Joined
Nov 20, 2002
Messages
99

ADVERTISEMENT

Colin,

The price is displayed in column O from software which logs the prices to excel in column O.
Basically it's a web query.
The data is always logged to Sheet1 column O.
 

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
Hi Equine Investor,

Sorry about this, a couple of more questions.

In colum O on sheet 1, is there a value or a formula.?

Do you have the web query code in your WB?

Who / What activates the web query?

ColinKJ
 

Equine Investor

Board Regular
Joined
Nov 20, 2002
Messages
99

ADVERTISEMENT

No problems Colin, I really appreciate your efforts to help me.

In Sheet 1 column O there is only a value.

There is no formula, it is just populated by a price and each time the program refreshes the prices changes if it has physically changed elsewhere (on the web).
External software does the web query, it is not part of the workbook code.

The workbook just displays the price, but if the price changes in Sheet1 column O, (for any stock), I'd like it to be copied to Sheet2 in the next available column for that stock.

So you'd have something like this on sheet2

Code:
Stock  Price                             

AAA    2.70     2.80   2.64     2.48
AAB   15.00   14.80  16.00   16.20
ABD     3.00    3.90  
CAH   29.00 
EML     5.00    5.10    5.20
 

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
Hi Equine Investor,

The code I did creates the record table you describe whenever the value in Sheet1 Column O is changed manually. However, our problem appears to be the software that carries out the web query update, switches off the Application Event function, which in turn prevents the code I did from running. Consequently my code doesn’t detect the changes.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>

If ok with you, I'll post another thread to see if anyone on the forum can assist.

ColinKJ
 

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983
Hi Equine Investor,

Try the following.

Remove the code from the Sheet 1 Private Sub Worksheet_Change Event.

Install this in the Sheet1 Calculate Event

Code:
Private Sub Worksheet_Calculate()
CheckCopy
End Sub

Then install the following routine in a Standard Module:

Code:
Sub CheckCopy()
R = Sheets(1).Range("A65536").End(xlUp).Row
For a = 1 To R
    If Sheets(1).Cells(a, 15) <> Sheets(2).Cells(a, Sheets("Sheet2").Range("IV" + Mid$(Str$(a), 2)).End(xlToLeft).Column) Then
        Sheets(2).Cells(a, Sheets("Sheet2").Range("IV" + Mid$(Str$(a), 2)).End(xlToLeft).Column + 1) = Sheets(1).Cells(a, 15)
    End If
Next a
End Sub

ColinKJ
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top