copy text from one excel to another one

Question

I would like to copy the text from a excel file in a specify range to another excel file in the same position

Here is the code that I tried

Sub OneCell()

    ChDir "C:\Workfile"
    Windows("simple av & cv template(Level).xls").Activate
    Sheets("Table ENG SG").Select
    Range("C9:C44").Select
    Selection.Copy
    Windows("Finalize").Activate
    Sheets("sheet1").Select
    ActiveSheet.Paste

End Sub

Do I need to define sth at the beginning of my program first or did I make any mistakes?

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Nov 18 '13 at 06:45
"something" lol I mean when i first learned programming , i need to define some integer , string , before i actually typed a program — user3003130, Nov 19 '13 at 09:30

Mark Fitzgerald · Accepted Answer · 2013-11-18T09:59:44.700

0

Assuming both your workbooks are open in the same instance of Excel which you can confirm by going to the View tab > Switch Windows and seeing if both workbook names are listed:

Sub ModifiedOneCell()
Workbooks("simple av & cv template(Level).xls").Sheets("Table ENG SG") _
.Range("C9:C44").Copy _
Destination:=Workbooks("Finalize").Sheets("sheet1").Range("C9:C44")
End Sub

The help for this can be found by opening the Object Browser in VBA (press F2), scrolling down to the Range object, clicking Copy from the list in the right pane then press F1.

The [space][underscore at the end of lines is the line continuation key combination in VBA allowing you to split long lines of code for readability.

If your workbooks are open in separate instances of Excel (i.e. only one is visible in Switch Windows), then copying to the clipboard and selecting Windows as you did in your code is the correct approach.

You would need to add

Range("C9:C44").PasteSpecial xlPasteAll

in place of

ActiveSheet.Paste

to get the result into the desired range

Ranges don't have a Paste method - only PasteSpecial.

edited Nov 18 '13 at 09:59

answered Nov 18 '13 at 03:32

Mark Fitzgerald

3,048
3
24
29

in `ModifiedOneCell` is there a reason for the second `Copy` (within destination?), or should there be a `.PasteSpecial xlPasteValues`? – Takedasama Nov 18 '13 at 09:40
@Takedasama The `.Copy` should not be there and I have now deleted it. I made an error when copying the answer from VBA. Adding `.Pastespecial xlPasteValues` to the end will cause an error. – Mark Fitzgerald Nov 18 '13 at 09:59
yeah you all are awesome! – user3003130 Nov 26 '13 at 08:52
@user3003130, thanks for acknowledging and accepting the answer. I hope I've helped you on your path to better Excel-VBA programming. `F1` and `F2` are your best friends! – Mark Fitzgerald Nov 26 '13 at 09:23

copy text from one excel to another one

1 Answers1