Copy one cell into another in a different workbook 1

[Mute101]

Im reasonably familiar with VBA but havent really done anything with Excel before so my question is how much information does Excel need to do my above request.

Do I need to set a workbook object up or can I just reference the book somehow?

Im looking to do this off the push of a button rather than a macro but trying to create this in a macro gives me a lot of junk about selecting cells and copying them, is it not possible to just do;

workbook1!sheet1!a1 = workbook2!sheet1!b7

Any pointers appreciated!

Cheers
Sim

----------------------------------------
I realised I was dyslexic when I went to a toga party dressed as a goat.
----------------------------------------

 

[PHV]

If the 2 workbooks are open in the same instance of Excel:
Workbooks("workbook1").Sheets("sheet1").Range("A1") = Workbooks("workbook2").Sheets("sheet1").Range("B7")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mute101]

Thanks for the fast reply,

Using the code you provided gives me a subscript error. I have checked the book and sheet references and they are correct so Im not quite sure why it cant find the cell references.

The only code I am running to get the error is;

Workbooks("Book1").Sheets("Sheet1").Range("A1") = Workbooks("VM0009Prices").Sheets("Sheet1").Range("K6")

----------------------------------------
I realised I was dyslexic when I went to a toga party dressed as a goat.
----------------------------------------

 

[PHV]

Just to be sure, what happens if you type the following in the debug window (Ctrl+G) ?
for each w in workbooks: ? w.name: next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mute101]

Output:

for each w in workbooks: ? w.name: next
VM0009Prices.xls
Book1.xls

I've been reading through the Object Browser and Ive started writing code using;

Dim Source As Workbook
Dim Dest As Workbook

Application.ScreenUpdating = False

    Set Source = ActiveWorkbook
    Set Dest = Workbooks.Open("I:\Book1.xls")
       
    '1GIFC - 1/1
    For d = 8 To 19
        s = 6
        Dest.Sheets("1GIFC").Range("e" & d) = Source.Sheets("Sheet1").Range("k" & s)
        s = s + 1
    Next

Application.ScreenUpdating = True

but it seems to work very slowly through my changes and there are going to be a lot (1000's) to do so if the other method will be quicker I'd like to know what to do to fix it!

Cheers
Simon

----------------------------------------
I realised I was dyslexic when I went to a toga party dressed as a goat.
----------------------------------------

 

[PHV]

Why not simply this ?
Source.Sheets("Sheet1").Range("K6:K17").Copy Dest.Sheets("1GIFC").Range("E8:E19")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mute101]

Just a belated thankyou for steering me in the right direction!

Have a star for your patience

----------------------------------------
I realised I was dyslexic when I went to a toga party dressed as a goat.
----------------------------------------