Copy A1:A13 from different workbook

[ccl28]

Hi,

I have a workbook name apple.xls that required user to key in the filename that they used. I do not know how many file the user will key in.
Example filename they used: 1) a.xls 2) c.xls 3) q.xls 4) z.xls

Based on the filename, I would like to copy the value in the filename of sheet 2 of A1:A13 into the apple.xls sheet 3 of B1:B13.

How to write a macro that can do this function?

Thank you.

[FSt1]

hi,
Somethink like this might work for you. you would have to change the file
path your own file path.

Sub MacCopyStuff()
Dim fnam As String
fnam = InputBox("enter a file name>")
Workbooks.Open Filename:= _
"C:\Documents and Settings\Owner\My Documents\" _
& fnam & ".xls"
'use your own file pathe
Sheets("sheet2").Activate
Range("A1:A13").Copy
Windows("yourfilename").Activate 'file name of the file that has this
macro
Sheets("Sheet3").Activate
Range("B1").PasteSpecial xlPasteAll
Windows(fnam).Activate
ActiveWindow.Close
End Sub
regards
FSt1
"ccl28" wrote:

>
> Hi,
>
> I have a workbook name apple.xls that required user to key in the
> filename that they used. I do not know how many file the user will key
> in.
> Example filename they used: 1) a.xls 2) c.xls 3) q.xls 4) z.xls
>
> Based on the filename, I would like to copy the value in the filename
> of sheet 2 of A1:A13 into the apple.xls sheet 3 of B1:B13.
>
> How to write a macro that can do this function?
>
> Thank you.
>
>
> --
> ccl28
> ------------------------------------------------------------------------
> ccl28's Profile: http://www.excelforum.com/member.php...o&userid=36095
> View this thread: http://www.excelforum.com/showthread...hreadid=565869
>
>

[[email protected]]

> I have a workbook name apple.xls that required user to key in the
> filename that they used. I do not know how many file the user will key
> in.
> Example filename they used: 1) a.xls 2) c.xls 3) q.xls 4) z.xls
>
> Based on the filename, I would like to copy the value in the filename
> of sheet 2 of A1:A13 into the apple.xls sheet 3 of B1:B13.

Hi Ccl8,

maybe you can use this simple codes:

Sub RetrieveValuetoApple()
Range("A1:A13").Value =
Workbooks("Book2").Sheets(1).Range("A1:A13").Value
End Sub

[[email protected]]

Hi Again Ccl28,

maybe this is all you need :
Sub RetrieveValuetoApple()
Workbooks("Apple.xls").Sheets(3).Range("B1:B13").Value =
Workbooks("a.xls").Sheets(2).Range("A1:A13").Value
Workbooks("Apple.xls").Sheets(3).Range("C1:C13").Value =
Workbooks("c.xls").Sheets(2).Range("A1:A13").Value
Workbooks("Apple.xls").Sheets(3).Range("D1:D13").Value =
Workbooks("q.xls").Sheets(2).Range("A1:A13").Value
Workbooks("Apple.xls").Sheets(3).Range("E1:E13").Value =
Workbooks("z.xls").Sheets(2).Range("A1:A13").Value
End Sub

thanks,

HAlim

> I have a workbook name apple.xls that required user to key in the
> filename that they used. I do not know how many file the user will key
> in.
> Example filename they used: 1) a.xls 2) c.xls 3) q.xls 4) z.xls
>
> Based on the filename, I would like to copy the value in the filename
> of sheet 2 of A1:A13 into the apple.xls sheet 3 of B1:B13.
..com/showthread.php?threadid=565869