I'm hung up on another code conversion for scattered cells to other
workbooks. The code below works just fine copying to workbooks named "Other"
and "SomeOther" to any sheet I want.
I am trying to do this Const to workbook "Other":
Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"
And I want this Const to go to workbook "SomeOther":
'Value-pair the Src|Tgt cell addresses
Const sSrcTgt$ = "A4:A6=O4:O6,C5:C8=P5:P8,A9=Q9,B11=R11"
Where you would use:
For n = LBound(v1) To UBound(v1)
'Parse the Src=Tgt cell addresses
v2 = Split(v1(n), "=")
Sheets("Sheet4").Range(v2(1)) = Application.Transpose(Range(v2(0)))
Next 'n
Not able to get it to work, I have copied the Dim's as needed for it. Not
getting any errors, just no output to workbook "SomeOther". (the output to
workbook "Other" works as it should even though the ranges for "SomeOther"
don't)
Howard
There's no reason for it to work with any workbook other than ActiveWorkbook
because there's no explicit ref to any other workbook! The ref to
ActiveWorkbook is implicit.
(this is unmodified and works fine, has none of my attempted conversions in
it) Sub CopyScatteredCells_SomeOther_Workbooks_XXX()
Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"
Dim n&, vaSrc, vaTgt
Dim wkbSrc As Workbook, wkbTgt As Workbook
Dim wksSrc As Worksheet, wksTgt As Worksheet
' Set wkbSrc = ThisWorkbook: Set wkbTgt = Workbooks("SomeOther.xlsm") 'can
do it this way OR two lines Set wkbSrc = ThisWorkbook
Set wkbTgt = Workbooks("Other.xlsm")
Set wksSrc = wkbSrc.Sheets("Sheet3")
Set wksTgt = wkbTgt.Sheets("Sheet2") '/ sheet2 or whatever on "Other"
vaSrc = Split(sSrc, ","): vaTgt = Split(sTgt, ",")
For n = LBound(vaSrc) To UBound(vaSrc)
wksTgt.Range(vaTgt(n)) = wksSrc.Range(vaSrc(n))
Next 'n
' wkbTgt.Close SaveChanges:=True
Set wkbTgt = Workbooks("SomeOther.xlsm")
Set wksTgt = wkbTgt.Sheets("Sheet4") '/ sheet4 or whatever on
"SomeOther"
vaSrc = Split(sSrc, ","): vaTgt = Split(sTgt, ",")
These arrays are already loaded with these strings and so do not need to be
reloaded unless the refs change.
For n = LBound(vaSrc) To UBound(vaSrc)
wksTgt.Range(vaTgt(n)) = wksSrc.Range(vaSrc(n))
Next 'n
' wkbTgt.Close True
Cleanup: '//error handler exit
Set wksSrc = Nothing: Set wkbSrc = Nothing
Set wksTgt = Nothing: Set wkbTgt = Nothing
End Sub
Just curious why the target workbooks are macro enabled...
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion