Extracting individual sheets from a 14 page excel document... 1

[TonyU]

[tt]Hello all, I have a 14 page workbook and would like to know if it's possible with vba to extract two specific sheets creating a separate workbook..

Thanks in advanced

<%=Tony%>
€ € € € € € € € € €
​

 

[RobBroekhuis]

By &quot;14 page workbook&quot;, do you mean a workbook containing 14 sheets (i.e., not one worksheet containing 14 pages)?
In that case, it's easy - if the worksheets you're interested in are called &quot;dick&quot; and &quot;jane&quot;:

SheetNames=array(&quot;dick&quot;,&quot;jane&quot
sheets(SheetNames).copy

creates a new workbook with just those two sheets.
Rob

 

[TonyU]

[tt]Yes Rob, I do mean a workbook containing 14 sheets.

Do I just use your code in the vba editor? and or how do I implemented.

<%=Tony%>
€ € € € € € € € € €
​

 

[RobBroekhuis]

As a full sub, the following would work:

sub CopyDickandJane
dim SheetNames
SheetNames=array(&quot;dick&quot;,&quot;jane&quot
sheets(SheetNames).copy
end sub

Put the macro in a VBA module (either inside your 14-page workbook, or in your personal.xls). Then you can run the macro using any of the usual methods (most easily, using Alt-F8 from within Excel).

Rob

 

[TonyU]

[tt]

Sweet!!!!!!!
Thank you very very much Rob, I greatly appreciate your help.

<%=Tony%>
€ € € € € € € € € €
​

 

[kildeer]

Let me challenge you a little further. I want to select all the sheets but one in a given workbook so that I can do an automatic &quot;search and replace&quot; using text strings that I want to keep intact in the unselected sheet. I don't want to specifically name each of the sheets in my VBA code because I will be adding other sheets in the future. I don't want to have to keep rewriting my code every time I add a new sheet. Any ideas?