Urgent help needed with variable s 3

[J741]

Hi.

I am trying to write a macro to copy some content from one workbook to another workbook (not different worksheets in the same workbook) and I am having some difficutly with which I need urgent help.

Se the following bits of code from my macro:

   Public sWorkbookName As String
   sWorkbookName = ThisWorkbook.Name
   Windows(sWorkbookFullName).Activate

I get this error when the 3rd line tries to run: "Runtime Error 9. Subscript out of range"

I have verified that the variable sWorkbookName does contain the correct name of the workbook, and if I replace the reference to the varaibal 'sWorkbookName' in the 3rd line with the text string this variable contains, then it works as expected. But, when using a variable it does not work.

Can anyone tell me why this is causing problems, and tell me how to fix it please?


My memory is not as good as it should be, and neither is my memory.

I have forgotten more than I can remember

 

[jges]

The 3rd line references the non-existent variable sWorkbookFullName instead of sWorkbookName that you have defined.

 

[SkipVought]

hi,

copy some content from one workbook to another workbook

There is no requirement to activate or select anything if you properly reference your objects.

  thisworkbook.sheets(somesheet).range(somerange).copy

  workbooks(otherworkbook).sheets(othersheet).Cells(row, col).pastespecial xlpasteall

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[J741]

Yup. Wrong variable name used. D'oh!

Thank you jges, for your sharp eyes.

I feel foolish now.


My memory is not as good as it should be, and neither is my memory.

I have forgotten more than I can remember

 

[J741]

SkipVought, thank you for your input, however I don't understand it.

I am a relative novice when it comes to VBA stuff, so I have been creating my macros by using the 'macro recorder' in Excel and then editing / manipulating the resulting VBA code to fit my need. So far this has worked out 'OK', but there is a great deal that I do not know about VBA and therefore I am certain that there may be better ways to do things. Hopefully I will learn some of those better ways by posting on these forums.


My memory is not as good as it should be, and neither is my memory.

I have forgotten more than I can remember

 

[SkipVought]

copy some content from one workbook to another workbook

There are several ways. I just wrote some code for copying and pasting without having to activate or select anything. Its all done with object references.

Please explain what you do not understand.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[J741]

Hi SkipVought.

So the parts I don't understand in your example, are as follows...

  thisworkbook.sheets(somesheet).range(somerange).copy

1. What if the source is not in the current workbook? I expect that the reference to 'thisworkbook' would not work. (maybe this is answered in the next example code part)
2. How do I specify 'All' as being the range? (when recording the macro, I just click on the little square in the top-left corner of the worksheet).

  workbooks(otherworkbook).sheets(othersheet).Cells(row, col).pastespecial xlpasteall

3. Why 'pastespecial' instead of just 'paste'?
4. What does 'xlpasteall' mean? why not just 'paste all'? What does the 'xl' part mean?

Basically, it's the whole syntax of these variable or function references that I'm confused by. I mean, I've written code (a long time ago, before Windows 95) in basic, assembly language, Pascal, and C, but the newer languages have syntax and references that are not familiar to me so I am trying to figure them out.

I'm sure there are reference materials or look-up tables for these somewhere, I just don't know where to find them, so doing a Google search or posting on forums like this one get me small bits and pieces of the overall puzzle, one piece at a time.


My memory is not as good as it should be, and neither is my memory.

I have forgotten more than I can remember

 

[SkipVought]

1. What if the source is not in the current workbook? I expect that the reference to 'thisworkbook' would not work. (maybe this is answered in the next example code part)

Then you choose the workbook reference applicable

2. How do I specify 'All' as being the range? (when recording the macro, I just click on the little square in the top-left corner of the worksheet).

Sheets("somesheetname").cells.copy

3. Why 'pastespecial' instead of just 'paste'?

Paste ASSUMES a cell. Good coding does not depend on assumptions.

4. What does 'xlpasteall' mean? why not just 'paste all'? What does the 'xl' part mean?

xlPasteAll is a constant, meaning paste values, formulas, formats et al. If you were to look at Edit > Paste Special in the Excel menu, you would see all the options available. The same options are available in VBA.

Look in VB Help for info in the Excel Object Model. Excel, VBA uses objects, properties & methods. If you are using 2007+ the VB Help has some good stuff on these topics.

Just browse in this forum. You can learn a lot.







Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PDQBach]

For future reference, include:

Option Explicit

at the beginning of your VBA code. Attempts to use undeclared variables will result in a runtime diagnostic and you will see that your reference to "sWorkbookFullName" is to something you neglected to define. Now all you have to do is figure out what reference you should have used.