Move sheets to a password protected closed workbook

[aravindhan_3]

Hi,

I have the workbook called "Sales". which has 3 sheets
Sell,
Buy
Rent

Now I want to move only sheet Sell ( only values and formats not formulas) to another workbook which is located in C:\Documents and Settings\DC87\Desktop\Sales\File1.xls
however the file File1.xls is password protected and the password is "Test123".

Please help me to do this in a macro

Regards
Arvind

[RolfJ]

This code should at least get you started in the right direction:


Const SOURCE_WORKSHEET_NAME As String = "Sell"
Const TARGET_WORKBOOK As String = "C:\Documents and Settings\DC87\Desktop\Sales\File1.xls"
Const WB_PASSWORD As String = "Test123"
Const NAME_OF_WORKSHEET_COPY As String = "CopyOfSellWorksheet"

Sub CopyWorksheet_ValuesOnly()

Dim sourceWb As Workbook
Set sourceWb = ActiveWorkbook

'Create temporary copy of Sell worksheet with all formulae converted to values
Dim tempWorksheet As Worksheet
sourceWb.Worksheets(SOURCE_WORKSHEET_NAME).Copy Before:=sourceWb.Sheets(1)
Set tempWorksheet = sourceWb.Worksheets(SOURCE_WORKSHEET_NAME & " (2)")
tempWorksheet.UsedRange.Copy
tempWorksheet.UsedRange.PasteSpecial (xlPasteValues)

'Copy the values-only temporary worksheet to the target workbook
Dim targetWb As Workbook
Application.Workbooks.Open Filename:=TARGET_WORKBOOK, PASSWORD:=WB_PASSWORD, WriteResPassword:=WB_PASSWORD
Set targetWb = ActiveWorkbook
tempWorksheet.Copy Before:=targetWb.Sheets(1)

'You still need to decide on how to name the copy of the Sell worksheet
'PLEASE NOTE:
'Running this code twice will cause an error,
'because a worksheet with this name already exists
targetWb.Worksheets(SOURCE_WORKSHEET_NAME & " (2)").Name = NAME_OF_WORKSHEET_COPY

'Delete the temporary worksheet
Application.DisplayAlerts = False
tempWorksheet.Delete
Application.DisplayAlerts = False

'Save and close the target workbook
targetWb.Close (True)

End Sub