How to copy to a specific worksheet?
First, a reminder that I've long since lost what little VBA know-how I
had around 15-20 years ago, so basically I'm a novice. Cut to the essentials, I want to copy cell B5 from one worksheet to C16 of another worksheet in another workbook. I found this example of copying between worksheets: http://analysistabs.com/excel-vba/co...et-to-another/ So thought I'd start with that simpler situation. I therefore first opened Excel 365 and a new file (workbook?) called My Book 1.xlsm. I then named two tabs (sheets) 'Source' and 'Destination' respectively. In Source I typed 'Cell B5' into B5 and left Destination empty. I wrote the following code: Sub CopyBetweenWorksheetsInSameBook() Sheets("Track Data").Range("B5").Copy Destination:=Sheets("Walk Index").Range("C16") End Sub I ran it with F5 and it worked as expected. Then I edited the macro to use one of my actual workbooks, adding two sheets and using this code: Sub CopyBetweenWorksheetsInSameBook() Sheets("Track Data").Range("B5").Copy Destination:=Sheets("Walk Index").Range("C16") End Sub That also worked OK. So I then tried to rewrite the macro to copy to a specified different destination workbook. The file is called Walk Index.xlsm and it's in the same folder as the source workbook. But this didn't work: Sub CopyTrackSheetCellsToWalkIndex() Sheets("Track Data").Range("B5").Copy Destination:=Workbook("Walk Index")Sheets("TEMP").Range("C16") End Sub Could someone put me straight on specifying the workbook and its worksheet please? Terry, East Grinstead, UK |
How to copy to a specific worksheet?
Terry,
As with refs to a sheet (Sheets("Source")) where it refs a specific sheet in the 'Sheets' collection of the active workbook, so too must you ref a workbook! So... Destination:=Workbooks("Walk Index").Sheets("TEMP").Range("C16") ...where workbooks is plural, and you omitted the dot before Sheets. Note that in the case of Workbooks() you are referring to the workbooks collection of the running instance of Excel. This means the file must be open! If you want to read/write closed workbooks then that requires using an ADODB recordset which involves a somewhat steep learning curve. Alternatively, you can turn ScreenUpdating off and process 'behind-the-scene' but involves considerable overhead when doing several files; -making the user wait until the process ends. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
How to copy to a specific worksheet?
GS wrote:
Terry, As with refs to a sheet (Sheets("Source")) where it refs a specific sheet in the 'Sheets' collection of the active workbook, so too must you ref a workbook! So... Destination:=Workbooks("Walk Index").Sheets("TEMP").Range("C16") ..where workbooks is plural, and you omitted the dot before Sheets. Note that in the case of Workbooks() you are referring to the workbooks collection of the running instance of Excel. This means the file must be open! If you want to read/write closed workbooks then that requires using an ADODB recordset which involves a somewhat steep learning curve. Alternatively, you can turn ScreenUpdating off and process 'behind-the-scene' but involves considerable overhead when doing several files; -making the user wait until the process ends. Thanks Garry. Made those two changes and 99% sure I've done so correctly - but it still fails. The source workbook 20160723-Day02-WH-Hoops-J-e560-m6.9.xlsm with its single sheet 'Track Data' is open. So is the destination workbook Walk Index.xlsm with its single (empty) sheet 'TEMP'. Whether I run from the module or from Alt+F8 list, I get the same error. Here's the layout: https://dl.dropboxusercontent.com/u/...Copying-01.jpg Sub CopyTrackSheetCellsToWalkIndex() ' This fails. Run-time error '9': Subscript out of range Sheets("Track Data").Range("B5").Copy Destination:=Workbooks("Walk Index").Sheets("TEMP").Range("C16") End Sub Do I perhaps also have to identify the source workbook, even though it's active when I run the macro? Terry, East Grinstead, UK |
How to copy to a specific worksheet?
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 Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
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! Many thanks Garry, I'll tackle that in the morning. It surprises me how much code seems necessary to copy one cell to another! BTW, it was on page 17 of Walkenbach's excellent 'Excel 2000 Programming for Dummies' (my most recent book!) that I read "If you omit specific references, Excel uses the *active* objects." Terry, East Grinstead, UK |
How to copy to a specific worksheet?
BTW, it was on page 17 of Walkenbach's excellent 'Excel 2000
Programming for Dummies' (my most recent book!) that I read "If you omit specific references, Excel uses the *active* objects." ...and so is why 'Best Practice' recommends using *fully qualified refs! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
How to copy to a specific worksheet?
But the source *was* active when the macro was run.
|
How to copy to a specific worksheet?
But the source *was* active when the macro was run.
No excuse to abondon good programming convention. Also a safety net if for some reason your Source wb becomes not the active workbook. Good habits will not bite you; bad habits will!<g (just saying...) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 12:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com