Array list writing to an Array of 'scattered cells' ?
On Friday, March 24, 2017 at 2:59:42 AM UTC-7, GS wrote:
Another question, if I may?
With the sSrc$ = "A1,C3,E5,G7,I10 range on Sheet3, how would I make the
sTgt$ = "P2,N4,L6,J8,H10" be Sheet4
Howard
Sub Copy_Scattered_Cells_Garry_2()
Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"
Dim n&, vaSrc, vaTgt
vaSrc = Split(sSrc, ","): vaTgt = Split(sTgt, ",")
For n = LBound(vaSrc) To UBound(vaSrc)
Range(vaTgt(n)) = Range(vaSrc(n))
Next 'n
End Sub
Typically, source data is on the active sheet and is being sent to a target
sheet which may or may not be in the same workbook. Claus' reply refs both
sheets as being in the same workbook. I'm inclined to ref the workbook so
there's no ambiguity...
Dim wksSrc As Worksheet, wksTgt As Worksheet
Set wksSrc = ThisWorkbook.Sheets("Sheet3")
Set wksTgt = ThisWorkbook.Sheets("Sheet4")
-OR- '//if copying to 1 or more workbooks...
Sub CopyScatteredCells()
Dim wkbSrc As Workbook, wkbTgt As Workbook
Dim wksSrc As Worksheet, wksTgt As Worksheet
Set wkbSrc = ThisWorkbook: Set wkbTgt = Workbooks("Other.xls")
Set wksSrc = wkbSrc.Sheets("Sheet3")
Set wksTgt = wkbTgt.Sheets("Sheet4")
'Do stuff...
wkbTgt.Close SaveChanges:=True
Set wkbTgt = Workbooks.Open("C:\SomeOther.xls")
Set wksTgt = wkbTgt.Sheets("Sheet4")
'Do more stuff...
wkbTgt.Close True
Cleanup: '//error handler exit
Set wksSrc = Nothing: Set wkbSrc = Nothing
Set wksTgt = Nothing: Set wkbTgt = Nothing
End Sub
--
Garry
Hi Garry,
I built the two Other/SomeOther sheets and it works perfect.
Thanks a lot for the code. It sure is fast, but there's not very much data.
Howard
|