Copy from a range & paste values on change of cell VBA

[mahhogany]

Hi All

I have this range of data
B C D E
A1|60% 50% 67% 78%
A2|90% 65% 78% 45%
A3|60% 50% 67% 78%
A4|90% 65% 78% 45%
A5|90% 65% 78% 45%
A6|60% 50% 67% 78%
A7|90% 65% 78% 45%
A8|90% 65% 78% 45% ---Contains formula

When the value of a particular cell (F3) changes I would like the VBA code to copy cells A2 to E8 and paste the values in A1:E7 overwriting the existing data. The formula in Row 8 will remain untouched. Can anyone help me with the code? Much appreciated.

 

[mvptomlinson]

The following worksheet_change macro should work. Put this code in the worksheet module where your data resides.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("F3")) Is Nothing Then
    Range("A2:E8").Copy
    Range("A1:E7").PasteSpecial xlPasteValues
End If
End Sub

 

[mahhogany]

The following worksheet_change macro should work. Put this code in the worksheet module where your data resides.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("F3")) Is Nothing Then
    Range("A2:E8").Copy
    Range("A1:E7").PasteSpecial xlPasteValues
End If
End Sub

Many thanks

How would I modify the line so that the copy and paste function only automated before the event of a data refresh? The Data refresh would happen manually.

 

[Jerry Sullivan]

By "data refresh" are you saying that you have Calculation set to Manual, and you only want to Copy>PasteValues when a Calculation occurs (and F3 has changed since the previous Calculation)?

 

[mahhogany]

By "data refresh" are you saying that you have Calculation set to Manual, and you only want to Copy>PasteValues when a Calculation occurs (and F3 has changed since the previous Calculation)?

I have some data connections in the spreadsheet.

When I hit the Data Refresh button, I want the vba function to be triggered so that the values are copy and pasted before the spreadsheet is refreshed and the new data is calculated.

 

[Jerry Sullivan]

You should be able to use something similar to the code mvptomlinson suggested in a QueryTable_BeforeRefresh Event Procedure instead of Worksheet_Change.

I haven't used that Event before, but this example from Microsoft seems to be referenced from many threads on the topic.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q213187

If you'll provide a little more info, I'll try to help you adapt it.
1. What Sheet is your Query Table (QT) on?

2. Is it the only QT on that Sheet - so we can reference by Index(1)?

3. What type of Data Source is connected?

 

[mahhogany]

Thanks JS

The sheet is called 'Data'
The Data source is a SharePoint 2007 list ( I am using Excel 2007)

 

[Jerry Sullivan]

You can try this....

In the VBA Project Explorer, Insert a Class Module
In the VBA Properties pane, rename the new Class Module: "CEventQT"

Paste this code into that CEventQT Class Module

Option Explicit

Public WithEvents QT As QueryTable

Private Sub QT_BeforeRefresh(Cancel As Boolean)
    Debug.Print "Data will be refreshed."
    With Sheets("Data")
        .Range("A2:E8").Copy
        .Range("A1:E7").PasteSpecial xlPasteValues
    End With
End Sub

To automatically initalize this whenever you open your Workbook,
paste this code into your ThisWorkBook code Module.

Option Explicit

Dim clsEventQT As New CEventQT

Private Sub Workbook_Open()
    Set clsEventQT.QT = Sheets("Data").QueryTables(1)
End Sub

After setting up these two code modules, save your workbook, close and reopen it to initialize the instance of the Event.

In your Data Connection Properties for this Query Table, uncheck the box next to: "Enable background refresh"

If this works correctly, each time you manually refresh your QT, the specified range will be copied, shifted up one row and pasted as values.

I'll caveot this suggestion by noting that I don't have any prior experience with this.
It seems to work in my test, and I'll welcome corrections and improvements from those with more experience.

 

[Jerry Sullivan]

mahhogany,

When I pasted your sheet name "Data" over my test's "Sheet1", an extra quote was temporarily posted.

I've fixed that in the post now, but wanted to send you another note to save you some debugging if you already copied the code before I fixed it.

 

[mahhogany]

mahhogany,

When I pasted your sheet name "Data" over my test's "Sheet1", an extra quote was temporarily posted.

I've fixed that in the post now, but wanted to send you another note to save you some debugging if you already copied the code before I fixed it.

You are a Star JS!

Thanks so much! I am off to try this now and will let you know how I get on.