How to copy only chosen sheets into my workbook with excel vba?

0
votes

Sorry for asking this question as I am totally new at excel VBA. Let me give a short illustration on my question.

I have an external workbook which contains worksheets with the tab name: a, b, c, d, e, and f. I just want to copy worksheets a, b and c to my own workbook. The problem is, a, b and c are not always there. Sometimes the workbook has only a and b, or a and c or, b and c, or even only single worksheet a.

May I know how can I apply the excel VBA to do the checking for sheets a, b and c, and copy them to my workbook if they exist.

Thanks!

Cheers, Patch

1
vbaexcel

1 Answers

0
votes

The below should be able to help you, you can add in further sheets using the OR statements. The macro below will append the files to the end of the other workbook. This macro opens the other workbook, if you don't want that you can remove the .Open parameter.

Code not tested but looks to work.

Sub TestMacro()

Dim wb, wbData As Workbook
Dim sh As Worksheet

Set wb = ActiveWorkbook
Set wbData = Workbooks.Open("<Your file address and name here>")

For Each sh In wb.Worksheets
    If sh.Name Like "a" _
        Or sh.Name Like "b" _
        Or sh.Name Like "c" Then sh.Copy Before:=wbData.Sheets(wbData.Sheets.Count)
Next

End Sub