I have created a loop which will look into a directory and list and open the files in chronological order. What I want to do is as the loop is running, is to copy data from the opened file into the workbook I am running the macro from: with each sheet copied being copied onto a new sheet in the master workbook. Is this possible? I have tried to do this before but in keeps indexing as sheet1(1)sheet1(2) (and running out of indices) rather than sheet1,Sheet2. Help??
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Copying from opened files into one workbook
- Thread starter djphill
- Start date
Richie(UK)
MrExcel MVP
- Joined
- May 17, 2002
- Messages
- 3,329
Hi djphill,
Welcome to the board.
How about calling a routine something like this from your workbook open routine:
<pre>Sub CopySheets()
Dim newsheet As Worksheet, j As Integer, wsname As String
For j = 1 To ActiveWorkbook.Sheets.Count
With ActiveWorkbook.Worksheets(j)
wsname = .Name
.UsedRange.Copy
End With
Set newsheet = ThisWorkbook.Sheets.Add(After:=Worksheets(Worksheets.Count))
'add sheet to copy to
newsheet.Name = wsname
'give it the same name as the copied sheet
newsheet.Range("A1").PasteSpecial
Application.CutCopyMode = False 'clear the clipboard
Set newsheet = Nothing 'clear memory
Next j
End Sub</pre>
HTH
Welcome to the board.
How about calling a routine something like this from your workbook open routine:
<pre>Sub CopySheets()
Dim newsheet As Worksheet, j As Integer, wsname As String
For j = 1 To ActiveWorkbook.Sheets.Count
With ActiveWorkbook.Worksheets(j)
wsname = .Name
.UsedRange.Copy
End With
Set newsheet = ThisWorkbook.Sheets.Add(After:=Worksheets(Worksheets.Count))
'add sheet to copy to
newsheet.Name = wsname
'give it the same name as the copied sheet
newsheet.Range("A1").PasteSpecial
Application.CutCopyMode = False 'clear the clipboard
Set newsheet = Nothing 'clear memory
Next j
End Sub</pre>
HTH
Upvote
0
