Solved

Copy file help

Posted on 2011-02-15
5
334 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

697 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