r_know
05-23-2010, 03:46 AM
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?
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
Looking forward to wonderful replies...
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?
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
Looking forward to wonderful replies...