Help with a copy function

[Newbie80]

Hi, I need to make a macro, when I push the macro It should open another workbook and copy some of the sheets into the current workbook.

So I would need some help how to copy a sheet which is in another workbook in to the one Im working with.

(This will then be used for open diffrent workbooks and pick specific sheets from them so it will be some kind of master sheet which you can add information to dynamically.)

[Blue Aardvark]

Hi Newbie

How about this as a starting point

Sub import()
Dim wbkSource As Workbook

'Open file
Call Workbooks.Open("C:/Cache.xls", False, True)
Set wbkSource = ActiveWorkbook

'Copy sheets 1,2,3 to this workbook
wbkSource.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
Before:=ThisWorkbook.Sheets(1)

'Close file
wbkSource.Saved = True
wbkSource.Close
End Sub

If you don't want new sheets to be created in the workbook then you should
try copying and pasting the data onto existing sheets.

[Newbie80]

Many thanks hade alredy managed to do this anyway, but I also want to clean up before I make the new files.

Is there anyway you can delete a sheet with out using it's full name.

e.g
ThisWorkbook.Sheets("name-yyyy").delete
yyyy varies so would like to remove all sheets that starts with:

("name-") only

[Newbie80]

Need to have them removed otherweis the sheets will stack up everytime the macro is runing

[Blue Aardvark]

I don't know of any quick and easy way of deleting sheets like that - I don't
think you can use wildcard characters with collection objects.

If I was you I would just loop through all the sheets and delete the ones
you want, something like......

Dim sht as sheet
for each sht in worksheets
if left(sheet.name,5) = "name-" then sht.delete
next

[Newbie80]

Will do that thanks again

[Blue Aardvark]

Now you have confused me! I thought you wanted to delete all sheets that
begin with a set piece of text. (Thats what the bit of code does).

[Newbie80]

Don't worry, you gave me the function i wanted. It's working good, thanks.


I have another question though: Is there a way to change name on the sheet when copying it?

This is my copy function:
sourceWb.Worksheet("Name on source sheet").Copy after:=thisWb.Worksheet(thisWb.Worksheets.count)

Want to change the source name in the new workbook.

Take your time, you have already been really helpfull.

[Newbie80]

Solved this myself:
thisWb.Worksheets("Name on source").Name = "New name"

Still have a little problem can't rename the sheet to an already existing sheet.
Would like it to be something like:

if(sheet already have the same name)

rename it to "New name (2)"

Is this possible?

[Newbie80]

Nevermind, im the best fixed this to :P