Copy specific tab into wb

[halimi1306]

Hi,

How to copy specific tab from ws. For example I want to copy "Summary-GNI" and "Summary-Capital" only from the slave. The code below will import all tabs in ws.

Thanks.


[vba]Option Explicit
Public Sub ImportSheetData()
Dim sFld As String, sFlPath As String, sFile As String
Dim vPath As Variant
Dim wb As Workbook, wbThisBk As Workbook
Dim ws As Worksheet
Dim i As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'For calling the deletion sub
Call DeleteOldSheets
Set wbThisBk = ThisWorkbook
'Getting the path
sFlPath = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
If sFlPath = "False" Then
MsgBox "No Files Selected"
Exit Sub
Else
vPath = Split(sFlPath, "\")
sFld = ""
For i = LBound(vPath) To UBound(vPath)
If InStr(1, vPath(i), ".xls") = 0 Then
sFld = sFld & vPath(i) & "\"
End If
Next i
ChDir sFld
sFile = Dir("*.xls*")
Do While sFile <> ""
Set wb = Workbooks.Open(sFld & sFile)
For Each ws In wb.Sheets
ws.Copy Before:=wbThisBk.Sheets("End")
Next ws
wb.Close False
sFile = Dir
Loop
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub DeleteOldSheets()
'Deleting old worksheets except Model Engine, Guidelines, Macro Control, Summary Dashboard and End
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Select Case ws.Name
Case "Model Engine (Read Only)", "Guidelines (Read Only)", "Macro Control (Read Only)", "Summary Dashboard", "End"
'do nothing
Case Else
ws.Delete
End Select
Next ws
End Sub[/vba]

[GTO]

I did not test your current code, but presuming all is well, then just add a check to see if the ws.name matches.
[VBA]Public Sub ImportSheetData()

'...preceeding code...
ChDir sFld
sFile = Dir("*.xls*")
Do While sFile <> ""
Set wb = Workbooks.Open(sFld & sFile)
For Each ws In wb.Sheets
If ws.Name = "Summary-GNI" _
Or ws.Name = "Summary-Capital" Then
ws.Copy Before:=wbThisBk.Sheets("End")
End If
Next ws
wb.Close False
sFile = Dir
Loop
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub[/VBA]

Hope that helps,

Mark

[halimi1306]

Thanks Mark, will try it out.

[halimi1306]

Hi,

the code works well unless for protected worksheet (worksheet password is "asd",
another thing is how to copy the sheet without formula but keeping all the format. Secondly, how to add another ws other than "Summary - GNI" and "Summary - Capital".

Is there any way to copy all ws with name "Summary - " on one go .

Thanks for your help.