Help with a copy function
 

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.)


--
Newbie80
------------------------------------------------------------------------
Newbie80's Profile: http://www.excelforum.com/member.php...o&userid=25946
View this thread: http://www.excelforum.com/showthread...hreadid=393772

Help with a copy function
 

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
Befo=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.

Help with a copy function
 

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
------------------------------------------------------------------------
Newbie80's Profile: http://www.excelforum.com/member.php...o&userid=25946
View this thread: http://www.excelforum.com/showthread...hreadid=393772

Help with a copy function
 

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

Help with a copy function
 

Need to have them removed otherweis the sheets will stack up everytim
the macro is runing :confused

--
Newbie8
-----------------------------------------------------------------------
Newbie80's Profile: http://www.excelforum.com/member.php...fo&userid=2594
View this thread: http://www.excelforum.com/showthread.php?threadid=39377

Help with a copy function
 

Will do that thanks again


--
Newbie80
------------------------------------------------------------------------
Newbie80's Profile: http://www.excelforum.com/member.php...o&userid=25946
View this thread: http://www.excelforum.com/showthread...hreadid=393772

Help with a copy function
 

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).

Help with a copy function
 

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
------------------------------------------------------------------------
Newbie80's Profile: http://www.excelforum.com/member.php...o&userid=25946
View this thread: http://www.excelforum.com/showthread...hreadid=393772

Help with a copy function
 

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

Still have a little problem can't rename the sheet to an alread
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

--
Newbie8
-----------------------------------------------------------------------
Newbie80's Profile: http://www.excelforum.com/member.php...fo&userid=2594
View this thread: http://www.excelforum.com/showthread.php?threadid=39377

Help with a copy function
 

Nevermind, im the best fixed this to :

--
Newbie8
-----------------------------------------------------------------------
Newbie80's Profile: http://www.excelforum.com/member.php...fo&userid=2594
View this thread: http://www.excelforum.com/showthread.php?threadid=39377