Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #21   Report Post  
Old March 25th 17, 07:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 840
Default Array list writing to an Array of 'scattered cells' ?

On Saturday, March 25, 2017 at 8:04:49 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Sat, 25 Mar 2017 07:41:34 -0700 (PDT) schrieb L. Howard:

I got an error at first and added Dim vaSrc.
Now I get no error but no copy to the workbooks.


your didn't refer correctly. And the ranges have the same direction so
you don't have to transpose.

Try:

Sub Copy_Range_to_Range_Single_to_Single()
Dim n&, v1, v2

Dim wksSrc As Worksheet, wksTgt As Worksheet
Dim vaSrc


Const sSrc = "A1,C3,E5,G7,I10"
Const sTgt = "P2,N4,L6,J8,H10"
Const sSrcTgt = "A4:A6|O4:O6,C5:C8|P5:P8,A9|Q9,B11|R11"

'Set ref to source sheet
Set wksSrc = ThisWorkbook.Sheets("Sheet3")

On Error GoTo Cleanup

'Set 1st target sheet and process it
Set wksTgt = Workbooks("Other.xlsm").Sheets("Sheet2")
v1 = Split(sSrc, ","): v2 = Split(sTgt, ",")

For n = LBound(v1) To UBound(v1)

wksTgt.Range(v2(n)) = wksSrc.Range(v1(n))
Next 'n

'Set 2nd target sheet and process it
Set wksTgt = Workbooks("SomeOther.xlsm").Sheets("Sheet4")
v1 = Split(sSrcTgt, ",")

For n = LBound(v1) To UBound(v1)

'Parse the Src:Tgt cell addresses
v2 = Split(v1(n), "|")

wksTgt.Range(v2(1)).Value = wksSrc.Range(v2(0)).Value
Next 'n

Cleanup:

Set wksSrc = Nothing: Set wksTgt = Nothing
End Sub


Regards
Claus B.


Hi Claus,

Well, that sure works nicely!
This is pretty deep coding for my pay grade, but I can follow most of it reading it, but writing it is a booger.

Thanks, it works very well now.

Howard

  #22   Report Post  
Old March 25th 17, 11:28 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
Default Array list writing to an Array of 'scattered cells' ?

your didn't refer correctly. And the ranges have the same direction so
you don't have to transpose.


I put Transpose there to accommodate rows-to-cols OR cols-to-rows when
transferring from a datatable to a summary sheet record row; -it makes no
difference otherwise!

My reusable code...

Sub CopyRangeValues(sSrcTgt$, WksSrc As Worksheet, WksTgt As Worksheet)
' Transfers range data between sheets of open workbook[s].
' Uses Transpose in cases where col data copies to summary row record.
'
' Args: sSrcTgt$ String containing range address pairs.
' (SrcRng|TgtRng,SrcRng|TgtRng...)
' WksSrc The sheet the source data gets copied from.
' WksTgt The sheet the source data gets copied to;
' This sheet can be in any open workbook.
'
Dim n&, v1, v2

v1 = Split(sSrcTgt, ",")
For n = LBound(v1) To UBound(v1)
v2 = Split(v1(n), "|")
WksTgt.Range(v2(1)) = Application.Transpose(WksSrc.Range(v2(0)))
Next 'n
End Sub 'CopyRangeValues

...where the sheet refs include their respective parents (workbooks) like so:

Set wksSrc = ThisWorkbook.Sheets("Data")
Set wksTgt = Workbooks("Some.xls").Sheets("Summary")
sSrcTgt = wksSrc.Range("XferAddrs")

CopyRangeValues sSrcTgt, wksSrc, wksTgt

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #23   Report Post  
Old March 26th 17, 02:29 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
Default Array list writing to an Array of 'scattered cells' ?

FWIW:

Here's another approach I use with a task-specific project that has multiple
workbooks it works with. It can transfer data between various workbooks
according to a table stored in ThisProject.xla:

Sub XferRangeValues()
' Transfers range data between multiple workbooks;
' Range refs are stored in a dynamic named range on "Xfers" sheet;
' Opens/closes workbooks as needed.
'
Dim vXfers, wksSrc As Worksheet, wksTgt As Worksheet
Dim n&, k, v1, v2

vXfers = ThisWorkbook.Sheets("Xfers").Range("XferRefs")
Const sUsrDat$ = ThisWorkbook.Path & "\UserData\"

For n = LBound(vXfers) To UBound(vXfers)
On Error Resume Next
GetSrc:
Set wksSrc = Workbooks(vXfers(n, 1)).Sheets(vXfers(n, 2))
If wksSrc Is Nothing Then '//file not open
Workbooks.Open sUsrDat & vXfers(n, 1): GoTo GetSrc
End If
GetTgt:
Set wksTgt = Workbooks(vXfers(n, 4)).Sheets(vXfers(n, 5))
If wksTgt Is Nothing Then
Workbooks.Open sUsrDat & vXfers(n, 4): GoTo GetTgt
End If
Err.Clear: On Error GoTo Cleanup

v1 = Split(vXfers(n, 3), ","): v2 = Split(vXfers(n, 6), ",')
For k = LBound(v1) To UBound(v1)
wksTgt.Range(v2(k)) = Application.Transpose(wksSrc.Range(v1(k)))
Next 'k
wksSrc.Parent.Close True: wksTgt.Parent.Close True
Next 'n

Cleanup:
Set wksSrc = Nothing: Set wksTgt = Nothing
End Sub 'XferRangeValues

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #24   Report Post  
Old March 26th 17, 03:41 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
Default Array list writing to an Array of 'scattered cells' ?

I recall being able to keep workbooks open if they were next up so I checked
and found I had revised the 'working' project as follows...


Sub XferRangeValues()
' Transfers range data between multiple workbooks;
' Range refs are stored in a dynamic named range on "Xfers" sheet;
' Opens/closes workbooks as needed.
'
Dim vXfers, wksSrc As Worksheet, wksTgt As Worksheet
Dim n&, k, v1, v2

vXfers = ThisWorkbook.Sheets("Xfers").Range("XferRefs")
Const sUsrDat$ = ThisWorkbook.Path & "\UserData\"

For n = LBound(vXfers) To UBound(vXfers)
On Error Resume Next
GetSrc:
Set wksSrc = Workbooks(vXfers(n, 1)).Sheets(vXfers(n, 2))
If wksSrc Is Nothing Then '//file not open
Workbooks.Open sUsrDat & vXfers(n, 1): GoTo GetSrc
End If
GetTgt:
Set wksTgt = Workbooks(vXfers(n, 4)).Sheets(vXfers(n, 5))
If wksTgt Is Nothing Then
Workbooks.Open sUsrDat & vXfers(n, 4): GoTo GetTgt
End If
Err.Clear: On Error GoTo Cleanup

v1 = Split(vXfers(n, 3), ","): v2 = Split(vXfers(n, 6), ",')
For k = LBound(v1) To UBound(v1)
wksTgt.Range(v2(k)) = Application.Transpose(wksSrc.Range(v1(k)))
Next 'k

If Not vXfers(n + 1, 1) = vXfers(n, 1) Then wksSrc.Parent.Close True
If Not vXfers(n + 1, 4) = vXfers(n, 4) Then wksTgt.Parent.Close True
Next 'n

Cleanup:
Set wksSrc = Nothing: Set wksTgt = Nothing
End Sub 'XferRangeValues


...and updated the component file accordingly. (I store frm/bas/cls files in a
"Src" folder for each project. This is where I pulled code for this thread
from!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #25   Report Post  
Old March 28th 17, 05:49 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
Default Array list writing to an Array of 'scattered cells' ?

FWIW:
Just thought I'd share this...


The usage scenario that spawned this process is as follows:

Client-specific tasking to:
a. distribute updated product templates to field reps;
This uses a single source file, multiple target files;
Each field rep's file has an open password, as it also had a 'Notes'
sheet that usually contained confidential rep-specific info;
Each field rep accessed their update file via remote login to the
server.

b. collect previous period files from field reps;
Each field rep uploaded their current working file weekly;
- (this could be during downloading of new templates)
This uses a single target file, multiple source files.

c. distribute various sheets from various files to various staff in
various departments;
This uses various source/target files.

Notes:
The 'Admin' user has restricted access according to their specific need, and so
this was managed in the app via login credentials.

Both field reps and office staff use the same addin. Access to functionality is
controlled via login credentials.

All source/target file require an open password; -this was also stored in the
appropriate Src/Tgt table listing.

All tasks are menu-driven.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Scattered array cells copy to scattered array cells another workbook L. Howard Excel Programming 14 July 14th 14 04:13 PM
Reading variable list of cells into array Diffus Excel Programming 5 October 1st 08 07:03 PM
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) Keith R[_2_] Excel Programming 3 November 13th 07 05:08 PM
combining cells and array from different sheets into an array to pass to IRR() [email protected] Excel Discussion (Misc queries) 3 September 11th 06 07:17 AM
Writing Range to Array Marston Excel Programming 3 August 9th 04 09:11 PM


All times are GMT +1. The time now is 10:08 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017