Copying sheets form other workbooks
 

Good morning all,
I have 5 to 8 workbooks in a folder called Class. The workbooks will not
always have the same name but they will only contain one sheet in each book.
I would like to copy that sheet from each workbook into the current workbook.
Is there a way to copy all sheets from all workbooks from a folder such as
c:\class.

Copying sheets form other workbooks
 

Hi Jeff,

See Ron de Bruin's sample code at:

http://www.rondebruin.nl/copy3.htm#sheet

See particularly Sub Example11 on that page.

---
Regards,
Norman



"Jeff" wrote in message
...
Good morning all,
I have 5 to 8 workbooks in a folder called Class. The workbooks will not
always have the same name but they will only contain one sheet in each
book.
I would like to copy that sheet from each workbook into the current
workbook.
Is there a way to copy all sheets from all workbooks from a folder such as
c:\class.

Copying sheets form other workbooks
 

That is what I was looking for. Thank you very much Norman!

"Norman Jones" wrote:

Hi Jeff,

See Ron de Bruin's sample code at:

http://www.rondebruin.nl/copy3.htm#sheet

See particularly Sub Example11 on that page.

---
Regards,
Norman



"Jeff" wrote in message
...
Good morning all,
I have 5 to 8 workbooks in a folder called Class. The workbooks will not
always have the same name but they will only contain one sheet in each
book.
I would like to copy that sheet from each workbook into the current
workbook.
Is there a way to copy all sheets from all workbooks from a folder such as
c:\class.

Copying sheets form other workbooks
 

Hi Jeff,

How about something like this ...

In a standard module of the master workbook where you want worksheets copied
into, paste the following code.

Sub CopySheets()
Dim fd As FileDialog
Dim vrtSelectedItem As Variant
Dim wsName As String

' Create a FileDialog object as a Folder Picker dialog box.
' Allows user to select the folder at run-time.
' (Or just hardcode the value of wsName below).
Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd
' Use the Show method to display the Folder Picker
' dialog box and return the user's action.
If .Show = -1 Then ' The user pressed the OK button.
vrtSelectedItem = .SelectedItems(1)
' Folder Picker does not allow multi-select so must be 1.
Else ' The user pressed Cancel.
Exit Sub
End If
End With

' Assuming all files that you're looking for
' are Excel workbooks with a .xls extension ...
wsName = Dir(vrtSelectedItem & Application.PathSeparator & "*.xls")
Application.ScreenUpdating = False ' Prevent screen flickering
Do While Len(wsName) 0
Workbooks.Open Filename:=wsName, ReadOnly:=True
With ActiveWorkbook
.Sheets(1).Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)
.Close SaveChanges:=False
End With
wsName = Dir ' Get the next workbook (if there is one)
Loop
Application.ScreenUpdating = True

'Set the object variable to Nothing.
Set fd = Nothing
End Sub


Note: This routine will copy the first worksheet of every *.xls workbook in
the selected folder into the workbook from which this code is run. In order
to avoid duplication, you may want to move *.xls files out of the selected
folder after they have been successfully processed into your master
workbook.

Trust this helps and let me know how you get on.

Enjoy! Sean.

"Jeff" wrote in message
...
Good morning all,
I have 5 to 8 workbooks in a folder called Class. The workbooks will not
always have the same name but they will only contain one sheet in each
book.
I would like to copy that sheet from each workbook into the current
workbook.
Is there a way to copy all sheets from all workbooks from a folder such as
c:\class.

Copying sheets form other workbooks
 

That works very well also. Thank you very much!

"Sean T. Connolly" wrote:

Hi Jeff,

How about something like this ...

In a standard module of the master workbook where you want worksheets copied
into, paste the following code.

Sub CopySheets()
Dim fd As FileDialog
Dim vrtSelectedItem As Variant
Dim wsName As String

' Create a FileDialog object as a Folder Picker dialog box.
' Allows user to select the folder at run-time.
' (Or just hardcode the value of wsName below).
Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd
' Use the Show method to display the Folder Picker
' dialog box and return the user's action.
If .Show = -1 Then ' The user pressed the OK button.
vrtSelectedItem = .SelectedItems(1)
' Folder Picker does not allow multi-select so must be 1.
Else ' The user pressed Cancel.
Exit Sub
End If
End With

' Assuming all files that you're looking for
' are Excel workbooks with a .xls extension ...
wsName = Dir(vrtSelectedItem & Application.PathSeparator & "*.xls")
Application.ScreenUpdating = False ' Prevent screen flickering
Do While Len(wsName) 0
Workbooks.Open Filename:=wsName, ReadOnly:=True
With ActiveWorkbook
.Sheets(1).Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)
.Close SaveChanges:=False
End With
wsName = Dir ' Get the next workbook (if there is one)
Loop
Application.ScreenUpdating = True

'Set the object variable to Nothing.
Set fd = Nothing
End Sub


Note: This routine will copy the first worksheet of every *.xls workbook in
the selected folder into the workbook from which this code is run. In order
to avoid duplication, you may want to move *.xls files out of the selected
folder after they have been successfully processed into your master
workbook.

Trust this helps and let me know how you get on.

Enjoy! Sean.

"Jeff" wrote in message
...
Good morning all,
I have 5 to 8 workbooks in a folder called Class. The workbooks will not
always have the same name but they will only contain one sheet in each
book.
I would like to copy that sheet from each workbook into the current
workbook.
Is there a way to copy all sheets from all workbooks from a folder such as
c:\class.

Copying sheets form other workbooks
 

I think it would be a good idea to move or delete the files as I copy them.
How would I do that?

"Sean T. Connolly" wrote:

Hi Jeff,

How about something like this ...

In a standard module of the master workbook where you want worksheets copied
into, paste the following code.

Sub CopySheets()
Dim fd As FileDialog
Dim vrtSelectedItem As Variant
Dim wsName As String

' Create a FileDialog object as a Folder Picker dialog box.
' Allows user to select the folder at run-time.
' (Or just hardcode the value of wsName below).
Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd
' Use the Show method to display the Folder Picker
' dialog box and return the user's action.
If .Show = -1 Then ' The user pressed the OK button.
vrtSelectedItem = .SelectedItems(1)
' Folder Picker does not allow multi-select so must be 1.
Else ' The user pressed Cancel.
Exit Sub
End If
End With

' Assuming all files that you're looking for
' are Excel workbooks with a .xls extension ...
wsName = Dir(vrtSelectedItem & Application.PathSeparator & "*.xls")
Application.ScreenUpdating = False ' Prevent screen flickering
Do While Len(wsName) 0
Workbooks.Open Filename:=wsName, ReadOnly:=True
With ActiveWorkbook
.Sheets(1).Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)
.Close SaveChanges:=False
End With
wsName = Dir ' Get the next workbook (if there is one)
Loop
Application.ScreenUpdating = True

'Set the object variable to Nothing.
Set fd = Nothing
End Sub


Note: This routine will copy the first worksheet of every *.xls workbook in
the selected folder into the workbook from which this code is run. In order
to avoid duplication, you may want to move *.xls files out of the selected
folder after they have been successfully processed into your master
workbook.

Trust this helps and let me know how you get on.

Enjoy! Sean.

"Jeff" wrote in message
...
Good morning all,
I have 5 to 8 workbooks in a folder called Class. The workbooks will not
always have the same name but they will only contain one sheet in each
book.
I would like to copy that sheet from each workbook into the current
workbook.
Is there a way to copy all sheets from all workbooks from a folder such as
c:\class.