Link to home
Create AccountLog in
Avatar of montrof
montrofFlag for United States of America

asked on

VBA Excel Sheets with similar names to new workbook

I need to copy sheets with similar names to a new workbook.  So if there are sheets

SHeetA
SheetA (1)
SheetA (2)
Would all copy Range A1:C34 to the new sheet starting at A1 and shifting based on the number of sheets it copies.  So the Values of SheetA (1) would be pasted starting at E1 skipping one column.

You could also have just a single sheet

SheetB

Which would just be copied to a new workbook.  Coping range A1:C34 to the new workbook and pasting it to A1.

Thanks,
Montrof
Avatar of Norie
Norie

Do you know the (partial) names of the sheets you want to copy from?

For example 'SheetA'.
What if in a workbook you have

Sheet1
Sheet1 A
Sheet2
Sheet3
SheetA
SheetA (1)
SheetA (2)

It is clear for SheetA but what about Sheet2, Sheet3 ?? are they ignored ?
and for Sheet1 who also have a similar sheet Sheet1 A do we save it in same workbook as SheetA or a new one ??

Best is to post a sample workbook.
gowflow
and if
Avatar of montrof

ASKER

So in your example above all sheets would end in (#) so SheetA  SheetA (1) and SheetA (2) would all be copied to a new workbook and Saved.  The other sheets would each go to an individual new workbook.   So all the sheets that have multiple sheets with the same name would end in (#).  

Thanks,
Montrof
So basically in my example you will get the following for which pls confirm:

Workbook1
sheet1 (will contain Sheet1  and Sheet1 A)

Workbook2
Sheet1 (will contain Sheet2)

Workbook3
Sheet1 (will contain Sheet3)

Workbook4
Sheet1 (will contain SheetA, SheetA (1), SheetA (2))

For sure all of them we copy Range A1:C34
pls confirm.
gowflow
Avatar of montrof

ASKER

Yes that is correct.  

Thanks,
Montrof
ok last question
How do we name the workbooks and we save them I presume in same directory as the current file.
gowflow
Avatar of montrof

ASKER

That would work for me.

Thanks,
Montrof
yes but you did not answer first part

"How do we name them"

gowflow
Avatar of montrof

ASKER

Oh sorry just name the same as the base sheet name,  like Sheet1, Sheet2, Sheet3, and SheetA

montrof
montrof

Are 'Sheet1', 'Sheet2', etc the real names of the sheets you are working with?
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of montrof

ASKER

Thank you so much!!!
Your welcome
gowflow