Code snippet to extract worksheets

[Karthik Thandapani]

Sorry, a correction..

Just wondering if there is a possible flow to move/copy worksheets from 2 or multiple workbooks which are saved in a folder? Thanks


Hi,

Just wondering if there is a possible flow to get worksheet names from 2 or multiple workbooks which are saved in a folder?

Sub SheetNames()
Columns(1).Insert
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
Next i
End Sub

Thanks in advance,
Karthik

 

[Luke M]

In short, yes.
See here for several different examples or merging.
http://www.rondebruin.nl/win/section3.htm

 

[Karthik Thandapani]

@Luke M : there are some examples which allows me to copy files from a folder but not to export sheets into a master file.

Will you guide me please ?

 

[Luke M]

Presumably, you would have the Master file open and then use one of the "Merge data from all workbooks in a folder" macros. Or, you can use the RDBMerge Add-in

 

[Karthik Thandapani]

sorry @Luke M : that didn't help.
I'm looking for a code snippet that moves/copies entire worksheets available from the workbooks in a folder

 

[Luke M]

Once you've got the looping setup, code would be something like:

'already in a loop
set wb = workbooks.open(myFile)
'This is the line to copy worksheet
wb.Worksheets(1).Copy ThisWorkbook.Worksheets(thisworkbook.Worksheets.Count)
'Close the other workbook
ws.Close SaveChanges:=False
'continue loop

 

[Karthik Thandapani]

@Luke M : not sure where I should be inserting the above snippet. I'm struck!

 

[Karthik Thandapani]

Hi,

Please see the attached file.

Presently, I've this which opens up file explorer, once we select the folder, that should copy worksheets to the activeworkbook.

Can you help?

 

[Luke M]

Chopped out the stuff that was in the loop, replaced it with snippet. Attached is a workinig file, able to merge all worksheets from all XL files in selected folder.

 

[Karthik Thandapani]

thanks @Luke M