Copy cell from diferrent workbook

[stoly]

Hello
since I do not know much about VBA excel I hope you can help me. I searching this forum 2 days, and cant solve my problem.
My problem is a bit complex for me, but it is easy for someone who knows VBA.
Example:
I have Workbooks "house1" and when I enter data in cell "A1" to "A7", I get the sum in cell "A10" in "sheet1", now I have to copy cell "A10" and more 4 cells, lets say "B12" "B15" "C23" "D9" into Workbooks "house2" in "N1" "N4" "N7" "M4" cell in "sheet1". I need to find that workbook "house2", because there is so many folders under folders (100 or more) and after paste data in "house2", need to left open the file "house2".
If anyone have time to answer, I will be grateful!

[kroz]

Let me see if I understand this correctly.
You have a workbook called "house1" (by the way, what's the name of the sheet you are working in) and you have some data here.
After you finish inserting the data you want to open another workbook.. but you don't really know where it is? That's what i understood from your post.

[stoly]

Let me see if I understand this correctly.
You have a workbook called "house1" (by the way, what's the name of the sheet you are working in) and you have some data here.
After you finish inserting the data you want to open another workbook.. but you don't really know where it is? That's what i understood from your post.

I now where is, but dont now how Vba can find it, workbook is "house1", i did not change name of sheet, but let call him "green".
For me its so complicated to made this macros, I spend more then 10 days and make big nothing.

[wedd]

Hi vba experts,

I would like to know if it is possible to create a csv macro enabled button using vba. If so, how? And would you have sample code of how this can be done.


Many thanks!

[kroz]

I now where is, but dont now how Vba can find it, workbook is "house1", i did not change name of sheet, but let call him "green".
For me its so complicated to made this macros, I spend more then 10 days and make big nothing.

To "find" and open your file you can add this piece of code to your macros:

Sub openfile()
dim MyFile as Variant
MyFile = Application.GetOpenFilename(FileFilter:="Excel files(*.xls), *.xls", Title:="Input the name of the file")
If MyFile = False Then
    MsgBox ("Cancel was pressed")
    Exit Sub
End If
Workbooks.OpenText Filename:=MyFile
End Sub

If you want to copy info from one workbook to the other you will have to use something along these lines:

Workbooks("house1.xls").Sheets("green1").Range(MyRange).Copy Destination:=Workbooks("house2").Sheets("green2").Range("A1")
    Application.CutCopyMode = False

[kroz]

Hi vba experts,

I would like to know if it is possible to create a csv macro enabled button using vba. If so, how? And would you have sample code of how this can be done.


Many thanks!

Please start a new thread if you need help.

[stoly]

To "find" and open your file you can add this piece of code to your macros:

Sub openfile()
dim MyFile as Variant
MyFile = Application.GetOpenFilename(FileFilter:="Excel files(*.xls), *.xls", Title:="Input the name of the file")
If MyFile = False Then
MsgBox ("Cancel was pressed")
Exit Sub
End If
Workbooks.OpenText Filename:=MyFile
End Sub

If you want to copy info from one workbook to the other you will have to use something along these lines:

Workbooks("house1.xls").Sheets("green1").Range(MyRange).Copy Destination:=Workbooks("house2").Sheets("green2").Range("A1")
    Application.CutCopyMode = False

Thanks, but I dont now how make this code to work.
So if you know or anyone else to write this code, but if possible complete?

I tried this code "find", but actualy dosnt open searched file, its open search window where i must manual find this file, and that not solving my problem.

Kroz, thanks again for you time!

[stoly]

Example:
I have Workbooks "house1" and when I enter data in cell "A1" to "A7", I get the sum in cell "A10" in "sheet1", now I have to copy cell "A10" and more 4 cells, lets say "B12" "B15" "C23" "D9" into Workbooks "house2" in "N1" "N4" "N7" "M4" cell in "sheet1". I need to find that workbook "house2", because there is so many folders under folders (100 or more) and after paste data in "house2", need to left open the file "house2".

Problem solved, i find solution, thx everyone for help.
I use help of hyperlink!

This is code for this, only transfer one cell, maybe this thing help someone!

Sub transfer()
Range("A2").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Windows("house1.xls").Activate
    Range("A4").Select
    Selection.Copy
    Windows("house2.xls").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub