Joining multiple worksheets

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

I need to join or combine multiple worksheets from multiple workbooks rather
than having a number of workbooks open.

What is the easiest way to do this?

Roger
 
Hi

Moving/Copying all sheets into a single workbook - open both target and
source workbooks, right-click on source sheet tab (When you want to
move/copy several sheets from target workbook, then block them before),
select 'Move or Copy' from drop-down menu, select target workbook into 'To
book' field, when you want to copy, i.e. the sheet isn't deleted from source
workbook, then check 'Create a copy', and at last press OK.
 
Ron thanks.
I have a requirement to copy the same range in many spreadsheets to a single
spreadsheet, with many worksheets. ie, each spreadsheet should have it's
own worksheet within one workbook.
The source is many workbooks but only one sheet is used in each workbook.
The destination needs to be one workbook, but many worksheets if you get my
drift.
Each tab on the destination should reflect the filename of the source.
Regards
Roger
 
I've tried the macro 'copy a range from each workbook', which works fine,
but each range needs to be on a new worksheet within one workbook.
How do I modify the macro to insert a new worksheet and copy the new range
to it, for all workbooks?
 
Hi Roger

Try something like this


Sub TestFile1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim Nsh As Worksheet
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

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
Set sourceRange = mybook.Worksheets(1).Range("A1:C5")

Set Nsh = basebook.Worksheets.Add(after:= _
basebook.Sheets(basebook.Sheets.Count))
On Error Resume Next
Nsh.Name = mybook.Name
On Error GoTo 0

Set destrange = Nsh.Range("A1")
sourceRange.Copy destrange

mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
 
Ron
This works really well.

Now I need to do some research as to what you've done, and how you've done
it, so I can modify the finer points of the program.

Brilliant and thankyou for giving me a helping hand

Regards
Roger
 
Back
Top