Kindly help me on this..

I have 25 excel sheets saves in my C drive...

I wish to copy column z from first excel sheet & paste it col A of the new
sheet.
Then
I wish to copy column z from second excel sheet & paste it col B of the new
sheet.
Then
Col z of third sheet in Col C of new sheet

So on so forth till i fiinish my last 25th sheet.

This is something we can do manually but if we can do it using macro it
would be great.

Thanks a lot for help in advance..

Boss

try this code. The code is copying Column A of each worksheet. You may need
to make minor changes. Also change the Folder to the appropriate folder.

Sub test()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Folder = "C:\temp\test"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Folder = "C:\temp\test"

Set fsread = CreateObject("Scripting.FileSystemObject")
Colcount = 1
First = True
Do
If First = True Then
Filename = Dir(Folder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
'open files
Workbooks.Open Filename:=Folder & "\" & Filename
ActiveWorkbook.ActiveSheet.Columns("A:A").Copy _
Destination:=ThisWorkbook.ActiveSheet.Columns(Colc ount)
Colcount = Colcount + 1
ActiveWorkbook.Close
End If
Loop While Filename < ""


End Sub

"Boss" wrote:

Kindly help me on this..

I have 25 excel sheets saves in my C drive...

I wish to copy column z from first excel sheet & paste it col A of the new
sheet.
Then
I wish to copy column z from second excel sheet & paste it col B of the new
sheet.
Then
Col z of third sheet in Col C of new sheet

So on so forth till i fiinish my last 25th sheet.

This is something we can do manually but if we can do it using macro it
would be great.

Thanks a lot for help in advance..

Boss

This was awesome..

Thanks, Thanks a lot for your help..

Boss

"Joel" wrote:

try this code. The code is copying Column A of each worksheet. You may need
to make minor changes. Also change the Folder to the appropriate folder.

Sub test()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Folder = "C:\temp\test"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Folder = "C:\temp\test"

Set fsread = CreateObject("Scripting.FileSystemObject")
Colcount = 1
First = True
Do
If First = True Then
Filename = Dir(Folder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
'open files
Workbooks.Open Filename:=Folder & "\" & Filename
ActiveWorkbook.ActiveSheet.Columns("A:A").Copy _
Destination:=ThisWorkbook.ActiveSheet.Columns(Colc ount)
Colcount = Colcount + 1
ActiveWorkbook.Close
End If
Loop While Filename < ""


End Sub

"Boss" wrote:

Kindly help me on this..

I have 25 excel sheets saves in my C drive...

I wish to copy column z from first excel sheet & paste it col A of the new
sheet.
Then
I wish to copy column z from second excel sheet & paste it col B of the new
sheet.
Then
Col z of third sheet in Col C of new sheet

So on so forth till i fiinish my last 25th sheet.

This is something we can do manually but if we can do it using macro it
would be great.

Thanks a lot for help in advance..

Boss