?
Solved

Copy file help

Posted on 2011-02-15
5
Medium Priority
?
336 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 2000 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

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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

764 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