Friday, July 9, 2010

Move all worksheets from a workbook to another

Hi,

Ever moved all the worksheets from a workbook to another. I did it previously, so thought to share the code with you all.

Public Sub CopyWorksheets(sourceBook As Workbook, destBook As Workbook)

Dim shtCounter As Integer
Dim wkSheet As Worksheet

'remove all the sheets from Destination workbook except one.
'the last worksheet will be deleted once we have copied the worksheets

For shtCounter = destBook.Worksheets.Count To 2 Step -1
Set wkSheet = destBook.Worksheets(shtCounter)
Application.DisplayAlerts = False
wkSheet.Delete
Application.DisplayAlerts = True
Next shtCounter

'rename the sheet1 so that we can assure there is no duplicity

Set wkSheet = Worksheets(1)

wkSheet.Name = "123111xSheet111789" 'giving some arbit name so that we can
' assure that the sheets we are trying
'copy doesn't have the same name as the worksheet's name.

'copy sheets to destWKB

sourceBook.Worksheets.Copy after:=destBook.Worksheets(1)



Application.DisplayAlerts = False
wkSheet.Delete
Application.DisplayAlerts = True
End Sub


HTH,
Vikas Bhandari

3 comments:

Roy Cox said...

Why not just use SaveCopyAs?

Vikas Bhandari said...

Because Save as option may bring some binary format along with it if we save a 2003 file to 2007 through Save As, so save as is not recommended always :)

Vikas

ANOOne Digital said...

Hey Vikas,

Would be Great to talk to you. Can you please send me your number? I am in Delhi as well.

an01digitalservices@gmail.com

Thank You