Hi,
I am wondering if someone can help me move data from a range
(contiguous data like a table) to another range that has multiple
areas. In short, the destination may have lines with formulas between
each area and I am not always moving the whole row. You can think of
this as a complicated copy/paste because I am not deleting the source
data.
I have tried assigning the source range to the destination range area
by area, but that takes too long. Then I tried by using ranges with
multiple areas but the data does not show up correctly after the first
area. Here is some sample code to see what I'm referring to. My
source data has a 'key' in the left column, but the users want to see
the values instead of a vlookup formula.
Any ideas? Thank you for your help. I've posted some sample code so
you can see what I'm trying to do.
Public Sub TestMove()
Dim sht As Worksheet
Dim rngSource As Range
Dim rngDestination As Range
Dim r As Long, c As Long, i As Long
Set sht = Worksheets.Add
With sht
'set up a test source data table
.Range("A1").Value = "Source Data"
For r = 2 To 5
For c = 1 To 3
i = i + 1
.Cells(r, c).Value = i
Next
Next
'set up what I'd like the result to look like
.Range("E1").Value = "Desired Result (takes too long to do it
this way)"
.Range("E2:G2").Value = .Range("A2:C2").Value
.Range("F4:G4").Value = .Range("B3:C3").Value
.Range("E6:G6").Value = .Range("A4:C4").Value
.Range("E7:F7").Value = .Range("A5:B5").Value
'NOTE: In my file, I can do an operation similar to the above
using loops, but this
'takes a LONG time to execute with a lot of data. (my source
table is 80 rows x 60 columns)
'Attempt - using ranges with multiple areas
.Range("A11").Value = "Attempt 1 (ranges with multiple areas)"
Set rngSource = .Range("A2:C2,B3:C3,A4:C4,A5:B5")
Set rngDestination = .Range("E12:G12,F14:G14,E16:G16,E17:F17")
rngDestination.Value = rngSource.Value
End With
Set rngSource = Nothing
Set rngDestination = Nothing
Set sht = Nothing
End Sub
I am not stuck on the idea of ranges with multiple areas. Any
solution would be very much appreciated that can get me the result
without disturbing the 'empty' cells in the destination range (which
actually contain formulas). I would like the destination cells to have
values in them instead of formulas.
Thank you so much for your help.
Calvin
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 5,200 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
