Combine many worksheets in one worksheet

**kmlprtsngh** · 09-09-2010, 04:08 AM

Hi all,
I have 8 worksheets all placed in different folders.I want that after I copy and paste the first worksheet in my main sheet, the other worksheet i copy should be pasted right below the first sheet.
Any suggestions.
Kp

**snb** · 09-09-2010, 05:17 AM

Code:

Sub snb()
  for j= 1 to 8
    with workbooks.add("E:\OF\Folder" & j & "\workbook" & j & ".xls")
       sq=.sheets(1).usedrange
       .close False
    end with
    thisworkbook.sheets(1).cells(rows.count,1).end(xlup).offset(1).resize(ubound(sq),ubound(sq,2))=sq
  next
End Sub

You only have to adjust the pathnames and the filenames.

**kmlprtsngh** · 09-09-2010, 06:33 AM

Thanks for replying.
The code you posted didnot help.
Actually what I want is a simple code which will paste 8 worksheets one after the other.

**snb** · 09-09-2010, 06:40 AM

I do not have the impression that you used my code, after adapting it to your specific situation (which I coudn't because you didn't provide any information)

**kmlprtsngh** · 09-09-2010, 07:35 AM

Well thanks for replying.
Can you just tell me how to select the cell after the last used cell.
I copied my 1st sheet till u19 for example starting from d4.
Now I want d20 to be active for next sheet to be pasted there.
Waiting for your reply.

**JBeaucaire** · 09-09-2010, 12:00 PM

SNB, I think you missed the fact that his 8 workbooks are not in the same folder, they are "all placed in different folders". So a loop like that won't work. Probably just need a simple open/copy/close command on all 8 specific files.

**JBeaucaire** · 09-09-2010, 12:06 PM

Something like this maybe:

Code:

Option Explicit

Sub Copy8Sheets()
Dim wsDest As Worksheet
Dim wbData As Workbook

Set wsDest = ThisWorkbook.Sheets("Summary")

'Copy from file1
Set wbData = Workbooks.Open("C:\2010\Test\Book1.xls")
    wbData.Sheets("Sheet1").Range("D2:U12").Copy _
        wsDest.Range("D" & Rows.Count).End(xlUp).Offset(1)
    wbData.Close False
    Set wbData = Nothing
    
'Copy from file2
Set wbData = Workbooks.Open("C:\2009\Archive\Book2.xls")
    wbData.Sheets("Sheet1").Range("D2:U12").Copy _
        wsDest.Range("D" & Rows.Count).End(xlUp).Offset(1)
    wbData.Close False

'Copy from file3
Set wbData = Workbooks.Open("C:\2008\Test22\Archive\Book3.xls")
    wbData.Sheets("Sheet1").Range("D2:U12").Copy _
        wsDest.Range("D" & Rows.Count).End(xlUp).Offset(1)
    wbData.Close False

'continue with more files....




End Sub

**snb** · 09-09-2010, 12:38 PM

@JB

I think you missed the different folders and the different workbooks in my code.

with workbooks.add("E:\OF\Folder" & j & "\workbook" & j & ".xls")

**JBeaucaire** · 09-09-2010, 01:40 PM

No, I saw that. I don't think I've ever seen a folder naming system in an office like Folder1, Folder2....Folder8. People tend to use descriptive names. And I take the OP to truly mean folders are chaotically organized and filenames are chaotically named.

You and I would structure stuff much differently than the average Joe, so the loops we employ on our projects run into a lot of difficulty when applied to the general office populace habits.

Anyway, it's good code, I just think it's an never-ending tweak to get that to work properly if the OP described his situation accurately.

**snb** · 09-09-2010, 04:26 PM

As the OP didn't provide abundant information I saw my code as an illustration how to vary foldernames and filenames.

I can alwasy be modified to a chaotically organized hard disd/server with

Code:

with workbooks.add("E:\OF\" & Choose(j,"name","organization","adresses,"documents") & "\" &choose(j,"planning","documentation","Help","background")  & ".xls")

**kmlprtsngh** · 09-10-2010, 02:23 AM

Thanks JB,your code worked perfect.

**JBeaucaire** · 09-10-2010, 02:49 AM

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Thread: Combine many worksheets in one worksheet

Thread Tools

Display

Combine many worksheets in one worksheet

Re: Combine many worksheets in one worksheet

Re: Combine many worksheets in one worksheet

Re: Combine many worksheets in one worksheet

Re: Combine many worksheets in one worksheet

Re: Combine many worksheets in one worksheet

Re: Combine many worksheets in one worksheet

Re: Combine many worksheets in one worksheet

Re: Combine many worksheets in one worksheet

Re: Combine many worksheets in one worksheet

Re: Combine many worksheets in one worksheet

Re: Combine many worksheets in one worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions