copy value from one workbook to another in VBA

[luolovepi]

I want to know how to copy the value of some cells of WORKBOOK1's worksheet1 TO WORKBOOK2's worksheet1 in VBA.

Note: in workbook1-worksheet1, the type of the cell the not the same as the corresponding cell's type in workbook2-worksheet1. Should I make the type to be consistent?

And can I write the code as below?

workbook("book1"). worksheet(1).range("B42")= workbook("book2").worksheet(1).range("A1")

Can anyone help me?
Thanks a lot!

 

[Hermanito]

What do you mean by cell-type?

This should work fine (assuming both workbook files are open when running this code):

Workbooks("book1").Worksheets(1).Range("B42").Value= Workbooks("book2").Worksheets(1).Range("A1").Value

 

[luolovepi]

Hi Hermanito,

Thank you so much for your reply!

What I mean about cell-type is that, in workbook1-sheet1, the value of the cell is set to be a Number type. Then I want to copy this number to workbook2-sheet1. I originally wanted to do as below, so I asked whether I should declare the cell in workbook2-sheet1 to be Number as well.

Dim cellToCopy As String 'String? Can I copy the number if I declare it as String here? 
cellToCopy= Workbooks("book1").Worksheets(1).Range("B42").Value
cellToCopy.copy
Workbooks("book2").Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

[/code]
or:

Dim cellToCopy As String 'String? Can I copy the number if I declare it as String here? 
cellToCopy= Workbooks("book1").Worksheets(1).Range("B42").Value
Workbooks("book2").Worksheets(1).Range("A1")=cellToCopy

Are my two approaches correct?

Regards,
lolo

What do you mean by cell-type?

This should work fine:

Workbooks("book1").Worksheets(1).Range("B42").Value= Workbooks("book2").Worksheets(1).Range("A1").Value

 

[Hermanito]

The first approach won't work, there is no .copy method on the string type. The second approach would work but why do you want to use an intermediate variable? Have you tried to use the code like I wrote previously?

If you declare the variable as string, you will still be able to copy a number, but it will be treated as a string. Because Excel does a lot of guesswork behind the screens about data types in cells, when you put the stringvalue containing a number into a new cell, it might be recognised again as a number.

In general, it's best to leave the cell format set to General, and only use the specific formats when you really need them.

Also, in VBA there is one special datatype, called Variant, this type can contain just about anything (integer, string, date, double, boolean, ...) and Excel will do its best to convert from one to the other when you mix types together. This can lead to all sorts of unexpected behaviour, and using it is also slower, so my advice is to use specific datatypes in VBA.
But in this case you can avoid all that by directly assigning the value of one cell to another cell without intermediate variable. That way you don't need to worry about what type of data it contains, Excel will do that for you.

 

[luolovepi]

Hi Hermanito,

Thank you so much! Your explanation is just what I want to know about. I want to know the usage of data type in excel. That's why I want to find out whether a declaration of string type will do or not~~

Thank you very much! you just explained very well and clear! I have understood.

Best regards,
lolo^-^

The first approach won't work, there is no .copy method on the string type. The second approach would work but why do you want to use an intermediate variable? Have you tried to use the code like I wrote previously?

If you declare the variable as string, you will still be able to copy a number, but it will be treated as a string. Because Excel does a lot of guesswork behind the screens about data types in cells, when you put the stringvalue containing a number into a new cell, it might be recognised again as a number.

In general, it's best to leave the cell format set to General, and only use the specific formats when you really need them.

Also, in VBA there is one special datatype, called Variant, this type can contain just about anything (integer, string, date, double, boolean, ...) and Excel will do its best to convert from one to the other when you mix types together. This can lead to all sorts of unexpected behaviour, and using it is also slower, so my advice is to use specific datatypes in VBA.
But in this case you can avoid all that by directly assigning the value of one cell to another cell without intermediate variable. That way you don't need to worry about what type of data it contains, Excel will do that for you.