Solved

# EXCEL copy destination values

Posted on 2011-10-19
186 Views
Hello Experts,

I need a bit of code that will increment through several ranges, copy and drop the values into another range where a calculation will be performed, then drop the results back into another incremented range. Speed is key, lots of ranges to calculate.

See the attached sheet.

Thanks,
biker9
Copy-values-in-ranges.xlsx
0
Question by:biker9

LVL 43

Expert Comment

The simplest way without code is to move the formulas down to row 24 and then copy it below each of the input set.

See attached.
Copy-of-Copy-values-in-ranges.xlsx
0

LVL 17

Expert Comment

@ssaqibh i right - I'm not sure why you don't just do this with formulas.  However if you do need the final values as plain numbers without formulas (why?), this code does it for your example.  Of course you say your final data will have many ranges, and this code is necessarily dependent on the format of the data you supply - there's not obvious way for the code to second-guess your layout.  So it will certainly need updating for the final data.  I've trie to make it obvious what you would change, but you might need to post the full spreadsheet at some point.
``````Sub ProcesssBlocks()

Dim rngSource As Excel.Range
Dim rngTarget As Excel.Range
Dim rngPreCalc As Excel.Range
Dim rngCalculated As Excel.Range
Dim i As Long

' define no of rows and cols here, so it only neds to be changed once
Const lngRows As Long = 6, lngCols As Long = 3

' set data-dependent locations
Set rngSource = ActiveSheet.Range("C5").Resize(lngRows, lngCols)
Set rngTarget = rngSource.Offset(19, 0)

' again the location of these will probably be different in your solution
Set rngPreCalc = ActiveSheet.Range("P5").Resize(lngRows, lngCols)
Set rngCalculated = rngPreCalc.Offset(9, 0)

For i = 1 To 3              ' this is dependent on your data
rngPreCalc.Value = rngSource.Value      ' copy source to staging area
rngTarget.Value = rngCalculated.Value   ' copy calculated figures to final area

' move to the next source and target locations (offset dependent on layout)
Set rngSource = rngSource.Offset(0, 4)
Set rngTarget = rngTarget.Offset(0, 4)
Next i

End Sub
'
``````
0

Author Comment

Thanks for the input, simply copying the formulas would work but I have a a large amount of them to copy, easier (i think) just to copy the variables, calc the result and paste results back where i need them.

So Andrew, your solution works great, but my result ranges are a different size then the input range (sorry should have mentioned that at the outset), so if you could adjust this for me I would much appreciate. The calculated result and the output range will always be the same size.

Tx,

Ray
Copy-values-in-ranges-Rev.xlsx
0

LVL 17

Expert Comment

Hi - I don't quite understand the new spreadsheet - the source ranges aren't the same shape as the destinations.  I really need to see exactly what your data is.  At the moment there is no easy way of guessing where it should be, as the source ranges have blank columns inside them.
0

LVL 43

Expert Comment

You can still do it the way I suggested. The more data you add the more you copy the formulas.

In fact you can even copy a hundred sets of formulas in one go. You do not have to copy one set at a time. You only have to make sure that the input data are pasted at a regular interval and not at a random spacing horizontally.
0

LVL 17

Expert Comment

Agreed - I think whichever method you use it would better to structure your data more carefully - having blocks spaced across the sheet can look more familiar, but it's much more efficient in Excel to organise it regularly by rows or columns.
0

Author Comment

Sorry but the copy formulas won't work for me, (thousands of formulas) but I did restructure the "entry" table a bit so no blank columns, the key is that the "source" entry table will always be the same size, and the  calculated results are expressed in tables that are always the same size, but they are different size then the "source" data entry table.

Copy-values-in-ranges-Rev-2.xlsx
0

LVL 17

Expert Comment

@biker9, sorry if I seem to be being dense here, but your last file posted still does not answer my question - the source ranges seem to bear no relation to to the result ranges.  Can you post some real data?  If not I don't see how any of the experts here can help you.
0

Author Comment

It is I who is  dense i am sure, I'm new to vba so I'm sure it was my lack of explanation that is confusing the issue.  I've managed to come up with a solution with a macro that works tho, not elegant, but it I think it will do the job.

Copy-values-in-ranges-Rev.xlsm
0

LVL 17

Accepted Solution

What I am trying to understand is your comment in the question ' lots of ranges to calculate'. As the example stands at the moment there are just the three ranges, and the working ranges come after them on the sheet, so effectively blocking space for any more of you ranges.

For a sensible, scalable macro solution it would be best to have the working ranges in the first columns, then the macro could iterate across all the ranges it finds in subsequent columns, stopping when it finds a blank cell.  In my original answer I had For i = 1 To 3, which is always a bad sign in a macro, because it means the macro needs changing if more data is added.

In the attached workbook, I have created 2 new sheets.  The first one 'Preferred' shows how I would do this using formulas, which is exactly what @ssaqibh suggested, so if you like this make sure he shares the points.

The second sheet ('Macro') uses the approach you asked for, but moves the working ranges to the start, so you can extend the data ranges as far as you like to the right in columns, and the macro will just pick up whatever is there (as long as the spacing is consistent). Copy-values-in-ranges-Rev.xlsm Copy-values-in-ranges-Rev.xlsm
0

LVL 17

Expert Comment

I accidentally included two files there - they are both the same.
0

Author Closing Comment

Works perfectly!
0

## Featured Post

### Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.