Terry Pinnell wrote:
GS wrote:
If you're looking to catch yourself up on using VBA perhaps you
might be interested in grabbing one of John Walkenbach's books...
http://spreadsheetpage.com/.com
Programming 'Best Practice' suggests that every object should have
*fully qualified refs* and so how I'd do this is...
Sub CopyTrackSheetCellsToWalkIndex()
Dim wsSource As Worksheet, wsTarget As Worksheet
Set wsSource = ActiveWorkbook.Sheets("Track Data")
'Or if from the file running the code;
Set wsSource = ThisWorkbook.Sheets("Track Data")
Set wsTarget = _
Workbooks("20160723-Day02-WH-Hoops-J-e560-m6.9.xlsm").Sheets("TEMP")
wsTarget.Range("C16") = wsSource.Range("B5")
Set wsSource = Nothing: Set wsTarget = Nothing
End Sub
You could also take it 1 step further...
Sub CopyTrackSheetCellsToWalkIndex2()
Dim rngSource As Range, rngTarget As Range
Set rngSource = ActiveWorkbook.Sheets("Track Data").Range("B5")
Set rngTarget =
Workbooks("20160723-Day02-WH-Hoops-J-e560-m6.9.xlsm").Sheets("TEMP").Range("C16")
rngTarget = rngSource
Set rngSource = Nothing: Set rngTarget = Nothing
End Sub
..where .Value is the default property of the Range object and so I
did not include it simply for code brevity!
Garry,
Still no joy unfortunately. Both of those versions failed with the
same error, 'Subscript out of range'.
I'm still working on it and will report back if I have any success.
BTW, I don't follow your final point, about 'Value'.
Also, just noticed that you seem to be defining the target (the
destination for the paste) wrongly: it should be Walk Index.xlsm.
Terry, East Grinstead, UK
Meanwhile I'm happy to report that I have this simple code working:
Sub CopyTrackSheetCellsToWalkIndexFromxlnitwit()
'Track Data' is in the active workbook
'Presumably that's why its name was not needed?
Sheets("Track Data").Range("B5").Copy Destination:=Workbooks("Walk
Index.xlsm").Sheets("TEMP").Range("C16")
End Sub
(How do I compose this so that it can be copy/pasted as it stands
please?).
Terry, East Grinstead, UK
Try...
Sub CopyTrackSheetCellsToWalkIndex()
Dim wsSource As Worksheet, wsTarget As Worksheet
Set wsSource = ActiveWorkbook.Sheets("Track Data")
'Or if from the file running the code;
Set wsSource = ThisWorkbook.Sheets("Track Data")
Set rngTarget = Workbooks("Walk Index.xlsm").Sheets("TEMP")
wsTarget.Range("C16") = wsSource.Range("B5")
Set wsSource = Nothing: Set wsTarget = Nothing
End Sub
-OR-
Sub CopyTrackSheetCellsToWalkIndex2()
Dim rngSource As Range, rngTarget As Range
Set rngSource = ActiveWorkbook.Sheets("Track Data").Range("B5")
Set rngTarget = _
Workbooks("Walk Index.xlsm").Sheets("TEMP").Range("C16")
rngTarget = rngSource
Set rngSource = Nothing: Set rngTarget = Nothing
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion