PDA

View Full Version : [SOLVED] Copy cell from diferrent workbook



stoly
11-21-2010, 04:55 PM
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:

kroz
11-21-2010, 11:59 PM
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
11-22-2010, 12:11 AM
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

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

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



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

stoly
11-22-2010, 02:29 AM
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! :beerchug:

:help:help:help:help:help:help:help:help:help

stoly
11-23-2010, 08:32 AM
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