Copy cell from diferrent workbook

Printable View

11-21-2010, 04:55 PM
stoly

Copy cell from diferrent workbook

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. :dunno
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!:beerchug:
11-21-2010, 11:59 PM
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.
11-22-2010, 12:11 AM
stoly

Quote:

Originally Posted by 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.

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.:dunno
11-22-2010, 12:50 AM
wedd

How to create a macro that will produce a csv file from a button

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! :beerchug:
11-22-2010, 01:47 AM
kroz

Quote:

Originally Posted by stoly

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.:dunno

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

Code:

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:

Code:

Workbooks("house1.xls").Sheets("green1").Range(MyRange).Copy Destination:=Workbooks("house2").Sheets("green2").Range("A1") Application.CutCopyMode = False
11-22-2010, 01:47 AM
kroz

Quote:

Originally Posted by 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! :beerchug:

Please start a new thread if you need help.
11-22-2010, 02:29 AM
stoly

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

Code:

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

Quote:

Originally Posted by kroz

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

Code:

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! :beerchug:

:help:help:help:help:help:help:help:help:help
11-23-2010, 08:32 AM
stoly

Quote:

Originally Posted by 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!

Code:

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