Try this example
http://www.rondebruin.nl/copy3.htm

Post back if you need help



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Rhonda" wrote in message ...
I need a macro to loop through a directory
(C:\MyDocuments) and identify files with the .bak
extension. Then open them one at a time because I need to
copy columns to a master. I found this macro, can it be
changed to my needs?

Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in
the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count

' Insert your code here.
' The following line shows how to reference a sheet
within
' the loop by displaying the worksheet name in a
dialog box.
MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

End Sub

Use this Rhonda

Set sourceRange = mybook.Worksheets(1).Columns("G:W")

There are only 256 columns??? in Excel


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Rhonda" wrote in message ...
Yes, the bottom macro would be great, how do I change it
to copy cols G To W from each workbook :

Copy a column or columns from each workbook

Remember Excel have only 256 columns.

Sub TestFile2()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim Colnum As Long
Dim a As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Colnum = 1
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
Set sourceRange = mybook.Worksheets(1).Columns(1)
a = sourceRange.Columns.Count
Set destrange = basebook.Worksheets(1).Columns
(Colnum)

sourceRange.Copy destrange
' Instead of this line you can use the code below
to copy only the values
'With sourceRange
' Set destrange = basebook.Worksheets
(1).Columns(Colnum). _
' Resize(, .Columns.Count)
'End With
'destrange.Value = sourceRange.Value

mybook.Close False
Colnum = Colnum + a
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub














-----Original Message-----
Try this example
http://www.rondebruin.nl/copy3.htm

Post back if you need help



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Rhonda" wrote in message
...
I need a macro to loop through a directory
(C:\MyDocuments) and identify files with the .bak
extension. Then open them one at a time because I need
to
copy columns to a master. I found this macro, can it be
changed to my needs?

Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in
the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count

' Insert your code here.
' The following line shows how to reference a
sheet
within
' the loop by displaying the worksheet name in a
dialog box.
MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

End Sub



.