Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old March 2nd 17, 08:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default How to copy to a specific worksheet?

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


I was using the target file specified in your post! I did think,
though, that the filename was rather long!

--
Garry

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

  #12   Report Post  
Old March 2nd 17, 08:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default How to copy to a specific worksheet?

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
  #13   Report Post  
Old March 2nd 17, 08:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default How to copy to a specific worksheet?

I was using the target file specified in your post! I did think,
though, that the filename was rather long!


I see where I misread that! -thanks for the catch!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #14   Report Post  
Old March 3rd 17, 01:17 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 114
Default How to copy to a specific worksheet?

GS wrote:

I was using the target file specified in your post! I did think,
though, that the filename was rather long!


I see where I misread that! -thanks for the catch!


I do the same frequently, usually due to impatience - and regret it
later!

Terry, East Grinstead, UK
  #15   Report Post  
Old March 3rd 17, 04:09 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default How to copy to a specific worksheet?

GS wrote:

I was using the target file specified in your post! I did think,
though, that the filename was rather long!


I see where I misread that! -thanks for the catch!


I do the same frequently, usually due to impatience - and regret it
later!

Terry, East Grinstead, UK


My excuse is tiredness; -I have Lou Gehrig's but can't seem to shake my
life-long habit to push myself to the max<g!

--
Garry

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


  #16   Report Post  
Old March 4th 17, 11:00 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 114
Default How to copy to a specific worksheet?

GS wrote:

GS wrote:

I was using the target file specified in your post! I did think,
though, that the filename was rather long!

I see where I misread that! -thanks for the catch!


I do the same frequently, usually due to impatience - and regret it
later!

Terry, East Grinstead, UK


My excuse is tiredness; -I have Lou Gehrig's but can't seem to shake my
life-long habit to push myself to the max<g!


Very sorry to hear that, Garry. Isn't that what Stephen Hawking suffers
from too?

I have another question (probably one of many!) about this same macro
I'm working on, but reckon I'll open a new post.

Terry, East Grinstead, UK

  #17   Report Post  
Old March 4th 17, 03:05 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default How to copy to a specific worksheet?

GS wrote:

GS wrote:

I was using the target file specified in your post! I did think,
though, that the filename was rather long!

I see where I misread that! -thanks for the catch!

I do the same frequently, usually due to impatience - and regret it
later!

Terry, East Grinstead, UK


My excuse is tiredness; -I have Lou Gehrig's but can't seem to shake
my life-long habit to push myself to the max<g!


Very sorry to hear that, Garry. Isn't that what Stephen Hawking
suffers from too?


Yes! I'm in my 25th year...

I have another question (probably one of many!) about this same macro
I'm working on, but reckon I'll open a new post.

Terry, East Grinstead, UK


--
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
copy specific data to another worksheet Jock Excel Programming 13 December 8th 09 10:49 PM
Copy specific data to second worksheet Andre7266 New Users to Excel 4 May 13th 08 08:39 PM
Copy specific data over to other worksheet dd Excel Programming 1 December 19th 06 10:24 AM
copy specific records to new worksheet violet Excel Programming 1 September 22nd 06 01:31 PM
Copy Worksheet to specific row in another workbook GregR Excel Programming 0 June 28th 05 07:08 PM


All times are GMT +1. The time now is 09:06 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