VBA: copy using right command

[brian_griffin]

Hello,

I would like to use a VBA command to copy a specific part (date) of a certain cell in various workbooks.

I have the following code:

WBZ.Worksheets(1).Range("G" & WBZ.Worksheets(1).Range("G65536").End(xlUp).Row + 1).Value = Right(WBQ.Worksheets(1).Range("D86").Copy, 4)

Unfortunately, I get the error "-2147221080" with the error description "Automation error".

If I rephrase a part of the code to:

WBZ.Worksheets(1).Range("G" &  WBZ.Worksheets(1).Range("G65536").End(xlUp).Row + 1).Value = 2004

The command is working properly.

Does anyone have an idea that could be wrong with my command?

Thank you very much in advance.

Best
Brian

 

[Jonmo1]

Welcome to the board.

Try removing the .Copy part

 

[brian_griffin]

Hi jonmo1

Thanks for your suggestion.

Unfortunately, it does not work.

WBZ.Worksheets(1).Range("G" & WBZ.Worksheets(1).Range("G65536").End(xlUp).Row + 1).Value = Right(WBQ.Worksheets(1).Range("D89"), 1)

I get the same error for this command.

Best
Brian

 

[Jonmo1]

What are WBZ and WBQ ?
Can you post the code that initializes those variables?

I presume they are workbook variables
something like

Dim WBZ As Workbook, WBQ As Workbook

Set WBZ = Workbooks("BookZName.xls")
Set WBQ = Workbooks("BookQName.xls")


The code you just posted works for me If I remove those two references (making it just refer to the current active book)


Plus, you changed more than I suggested you change..
You changed the 4 to 1 in the Right function.

 

[brian_griffin]

First, you are right.

I changed the code to check whether there is any problem with the cell or the number of characters.

Second, you are also right.

I messed something up with the initialization.


Now the code is working fine.

Thanks

Brian

 

[Jonmo1]

Glad to help, thanks for the feedback