Solved: Modify exiting VBA code for copied the particular sheet from multiple excel files.

[r_know]

Hi,

Last time Mr. Lucas introduced the Code which copied the different excel files containing one folder to new excel file by name of sheet.

VBA code work very accurate; but here I am looking forward to change code where I do not need the name of sheet. Like here given Sheet name like sheet1,DDR, test etc.

I want particular original properties "sheet1" therefore whatever name in the folder for sheet1 for different excel file but they pick up Sheet1.

Pls see the attach file.

I want copy DDR 22 May 10 from Book 1, DDR 23 May 10 from Book 2 as there properties said that they are "sheet1"

How to modify below code?

[vba]Option Explicit
Sub open_workbooks_same_folder()
Dim folder As String
Dim Wb As Workbook, sFile As String
Dim Cwb As Workbook
Dim lrow As Long
Dim iChoice As String
folder = ThisWorkbook.Path & "\"
' folder = "C:\Temp\"
Set Cwb = ThisWorkbook
sFile = Dir(folder & "*.xls")
iChoice = InputBox("Pick which Sheet to import", "Select Sheet")


Do While sFile <> ""
If sFile <> Cwb.Name Then
On Error Resume Next
Set Wb = Workbooks.Open(folder & sFile)
' Wb.Sheets("Sheet1").Copy After:=Cwb.Sheets(ThisWorkbook.Sheets.Count)
Wb.Sheets(iChoice).Copy After:=Cwb.Sheets(ThisWorkbook.Sheets.Count)
Wb.Close True
End If
sFile = Dir
Loop
Cwb.Worksheets("Data").Range("A1").Select
End Sub[/vba]
Looking forward to wonderful replies...

[mikerickson]

The thread title says Mac, but that code uses Windows path separators.

Are you currently running that code on a Mac?

Is the sheet you want copied the left most tab of each workbook?

[r_know]

Hi Mike,

I used word MAC for Macro. (VBA Code)

I am using code on MS office 2003 looks sad from me which first interpret wrongly.

I want copy to particular sheet from the multiple excel files in the folder by properties name "Sheet1" instead of that Sheet name like DDR.

I am going to change title and make change is understanding the thread.

[mikerickson]

I'm not on Windows, but chaning this line might work for you.
[VBA]Wb.Sheets(1).Copy After:=Cwb.Sheets(ThisWorkbook.Sheets.Count) [/VBA]

[r_know]

Thanks A LOT

Problem Solved PARTIALLY!

[r_know]

My Initial Question and Solution Quite different?

Pls modify as per the initial first Thread. I want as per properties, if I select "sheet2" then it will not work as per above.

Rahul