Save a File

Excelpower · Jul 27th 2004

I have a file that I have protected the code inside the VBA window that I will be giving to others to use. The program is working fine as long as I don't VBA Protect the code. I have the workbook getting data from another workbook and copying it into the existing workbook with the protected code and then saving the existing workbook with the copied data in it. Now that I have Protected the code with a password, it will not let me save the file due to the code been protected, is there a way around this problem.

Thank You very much for any help you can give me to get around this problem.

Excelpower

Excelpower · Jul 28th 2004

Below is the code that I am using to copy the data from one sheet "Data" to the other sheet "Master File". Please note the the line ActiveWorkbook.Save is causing the problem. Like I have said before, this code is protected using the VBA Protection method. This is the first time that I have tried to protect the code in a workbook, and I am not sure how to get rid of this problem. If I can not solve it, I will just leave the code unprotected, not a problem with this workbook, just wanting to know how to use the protection of VBA Code.

Code

Sub CopyToMasterDataBase()
    
    Set rng1 = Worksheets("Data").Range("A2").CurrentRegion.Offset(1, 0)
    Set rng1 = rng1.SpecialCells(xlVisible)
    rng1.Copy
    Windows("Master File.xls").Activate
    Sheets("Master").Select
    ActiveSheet.[a65536].End(xlUp)(2).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWorkbook.Save
    Windows("Data.xls").Activate
    ActiveWorkbook.Close
End Sub

Display More

Thanks for any reply that can get me moving in solving this problem.

EXcelpower

gollem · Jul 28th 2004

Hello,

it is not normal that you cannot save your workbook when your Vba code is protected. Normally it hasn't an influence(I have used it myself).
However if you have protected your workbook or sheet it could give a problem, or perhaps the file is read only?

Gollem

Excelpower · Aug 5th 2004

Hi Gollem,

Thanks for the response, you are right that this in not normal and you should be able to save the file when the code is protected.

I have been doing some testing on this file and for some reason if I save the file manual with the save button on the menu, it will let me save the file, but if I try to save the file using the code and not maunal using the menu button it does not work.

Not sure why, I have other files that is working without this problem, but I am not saving the file with code, I am save the files with the save buttons on the menu. I was just trying to get this done without depending on the user to save the file. I was thinking that I must be using the save code in the wrong way or something.

I will keeep try to find the problem with the code and if I do I will post the solution here.

Thanks for you help
Excelpower

Will Riley · Aug 5th 2004

When you activate the Window, you are not activating the Workbook... although you later do by selecting the sheet

Always best to identify the Worbooks using variables, then you can keep track of them with their variable names as opposed to having to work out which one is the active one all the time...

See if this works,

Code

Sub CopyToMasterDataBase()
Dim wbData As Workbook
Dim wbMaster As Workbook
Dim rng1 As Range


Set wbData = ActiveWorkbook
Set wbMaster = Workbooks("Master File.xls")


Set rng1 = wbData.Worksheets("Data").Range("A2"). _
    CurrentRegion.Offset(1, 0)
Set rng1 = rng1.SpecialCells(xlVisible)
rng1.Copy
With wbMaster.Sheets("Master").[a65536].End(xlUp)(2)
    .PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
End With
wbMaster.Save
wbData.Close
End Sub

Display More

Excelpower · Aug 5th 2004

Thanks for the reply WillR,

This has been a stumper for me, this is the first time I have had this problem come up and I knew I was doing something wrong.

Your code worked great.

Thanks for the input.

Excelpower

Save a File

Participate now!

Share