Solved

Copy file help

Posted on 2011-02-15
5
330 Views
Last Modified: 2012-06-22
Please can you help:-

Sheets(Array("Low Volume", "DataSheet")).Copy

With the attached code it saves a copy of sheets 4 & 6. The trouble is that within the copied file, the launch userform no longer works.

When I open the file the Forms module is not copied.

Please can you help alter the code so that the entire workbook is copied.

Private Sub CommandButton2_Click()
Dim strPath As String, strFileName As String
Dim Extn As String, strFname As String
Dim INUm As Long, CheckMe As Long

strPath = "C:\Documents and Settings\Administrator\My Documents\Gary\project\New Folder\"
strFileName = "Summary_" & Format(Sheets("Low Volume").Range("C5").Value, "YYYY-MM-DD")
Extn = ".xls"

strFname = Dir(strPath & strFileName & "_" & INUm & Extn)
Do While Len(strFname) <> 0
INUm = INUm + 1
strFname = Dir(strPath & strFileName & "_" & INUm & Extn)
Loop

Sheets(Array("Low Volume", "DataSheet")).Copy
ActiveWorkbook.SaveAs Filename:=strPath & strFileName & "_" & INUm & Extn
ActiveWorkbook.Close
CheckMe = MsgBox("Clear Contents?", vbYesNo)
If CheckMe = vbYes Then

Sheets("Low Volume").Unprotect Sheets("DataSheet").Range("B2").Value
ThisWorkbook.ActiveSheet.Range("B14:K414,W14:W414,N14:N414,Q14:Q414").ClearContents
Sheets("Low Volume").Protect Sheets("DataSheet").Range("B2").Value
 End If
End Sub

Open in new window

gary-test3.xls
0
Comment
Question by:Gazza83
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34902375
Gary,

As you are effectively copying the entireworkbook I suggest you do a SaveAs rather than a Copy, then resave the file with its original path

This is a lot cleaner than writing code to programmatically copy the UserForm and Module code

The only caution is whether you don't want to save the model at this time

Cheers

Dave
Private Sub CommandButton2_Click()
    Dim strPath As String, strFileName As String
    Dim Extn As String, strFname As String
    Dim INUm As Long, CheckMe As Long
    Dim strThisPath As String
    strThisPath = ThisWorkbook.FullName


    strPath = "C:\Documents and Settings\Administrator\My Documents\Gary\project\New Folder\"
    strFileName = "Summary_" & Format(Sheets("Low Volume").Range("C5").Value, "YYYY-MM-DD")
    Extn = ".xls"

    strFname = Dir(strPath & strFileName & "_" & INUm & Extn)
    Do While Len(strFname) <> 0
        INUm = INUm + 1
        strFname = Dir(strPath & strFileName & "_" & INUm & Extn)
    Loop

    ActiveWorkbook.SaveAs Filename:=strPath & strFileName & "_" & INUm & Extn
    CheckMe = MsgBox("Clear Contents?", vbYesNo)
    If CheckMe = vbYes Then
        Sheets("Low Volume").Unprotect Sheets("DataSheet").Range("B2").Value
        ThisWorkbook.ActiveSheet.Range("B14:K414,W14:W414,N14:N414,Q14:Q414").ClearContents
        Sheets("Low Volume").Protect Sheets("DataSheet").Range("B2").Value
    End If
    ActiveWorkbook.SaveAs strThisPath
End Sub

Open in new window

0
 

Author Comment

by:Gazza83
ID: 34902440
I want the 'orginal file' to retain its own file name. So the 'Archive' file would be separate because it will have a different file name.

If there is a way to SaveAs without unpdating the name of the currently opened workbook then that would be just what  I need.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34902470
> I want the 'orginal file' to retain its own file name. So the 'Archive' file would be separate because it will have a different file name

Thats what my code does
ie saves the archive file
then resaves the 'orginal file' with the original name ( ActiveWorkbook.SaveAs strThisPath)

Cheers

Dave


0
 

Author Comment

by:Gazza83
ID: 34902555
Ah I see now, it might be just my pc, but I get a command prompt as whether to replace the existing file.

is there a way to disable this? Or to add "_1" to the file name
save-as.JPG
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 34902576
hth

Dave
Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs strThisPath
    Application.DisplayAlerts = True

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now