Copy data from another workbook

[smithcza]

Hi

How dow I copy data from another workbook into a open workbook and closing the other workbook again using code.

Regards,

SmithCZA

SmithcZA

 

[AnotherHiggins]

Turn on your macro recorder (Tools > Macro > Record New Macro) and go through the steps.

Observe the code that was generated.

Post back with any questions about what you see.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[smithcza]

I can get to open a workbook OK, problem is to close it. If I record a macro like you suggested, it uses the filename of the opened workbook. Problem is, the filename will not be the same everytime so I can't hard-code the filename into the macro.

This is what I have so far:

    FName = Application.GetOpenFilename _
        (filefilter:="Microsoft Excel Workbooks (*.xls),*.xls") 'All Files (*.*),*.*")
    Workbooks.Open Filename:=FName
    
    Cells.Select
    Range("A1").Activate
    Selection.Copy
    Windows("Element vs results - TCLP.xls").Activate
    Sheets("sheet3").Select
    Range("a1").Select
    ActiveSheet.Paste

If I want to close the opened workbook with code from the recorder, it gives me this:

    Windows("Informal RR worksheet.xls").Activate
    ActiveWindow.Close

Regards,

SmithcZA

 

[AnotherHiggins]

As soon as you open the workbook, add this:

NewWB = activeworkbook.name

Then, when you want to close it later, you can use

Windows(NewWB).Activate
    ActiveWindow.Close

Which can be shortened to

Windows(NewWB).Close

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[smithcza]

Thanks a mill

Wil give it a go.

SmithcZA