Copying Between Workbooks

[BluesBros]

A friend at work mentioned this site may be able to help, so here goes.

I have two spreadsheets one called Current the other MasterFile. I need to copy the data on sheet1 from Current to MasterFile along with the formats and where there are formulas these as values. Once the data has been copied into the MasterFile I would like to save it as MasterFile with todays date e.g MasterFile 05 01 12.xls

Would it be possible to transfer the data into the MasterFile without it being opened or do both need to be opened at the same time?

Thanks

Nick

 

[HalfAce]

Hi Nick, welcome to the board.
Are you wanting a routine so you can do this whenever you like?

It might be possible to do it with only one of the workbooks open, but I don't know how. It's easy enough to open the other workbook, perform the operation and save/close it again within the code, so that shouldn't really be an issue.

Am I correct in thinking you're using the MasterFile as a template and then saving it under a different name each time? (Meaning you'll be doing this on some kind of a regular basis? - and thereby answering the first question?)

When you copy from Current, are you going to be copying the entire sheet, or a specified range" (What would the range be?)

Also, is there anywhere in the sheet that already contains the date for which you'll want the workbook named?

Dan

 

[BluesBros]

Hello Dan, I'll try and answer your questions in order.

1. The routine will need to be done 2-3 times a week on an ad-hoc basis.

2. Yes. MasterFile is a template. Ideally I would like the new saved file to be called Archieve (with todays date).

3. The cells I need to copy across are A4:Y5121.

4. No the date is not shown on the worksheet.

Hope that helps?

Nick

 

[HalfAce]

Hi Nick,
Try this and see if I got everything covered.
You didn't indicate if you wanted the new file (Archieve 1-16-05) left open at the end of this or not, so I went ahead and closed it.
You'll also need to amend the file path to your own.
You will also need to replace Z1 in this code with an unused cell if Z1 in your MasterFile is already in use.

Sub TestSub()
Dim i As String
Application.ScreenUpdating = False
With Range("Z1")
    .Value = Now
    .NumberFormat = "m-d-yy"
End With
i = [Z1].Text
[Z1].ClearContents
Range("A4:Y5121").Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1:Y5118").Value = Range("A1:Y5118").Value
[A1].Select
ActiveWorkbook.SaveAs Filename:="C:\My Documents\Your Folder\Archieve " & i & ".xls"
ActiveWorkbook.Close
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Does this do what you're after?
Dan

 

[BluesBros]

Not quite what I'm after. I've managed to record a macro that opens the MasterFile and pastes data into it (see below). There are three things I'm struggling with:-

1. I can't get it to save automatically as Archieve with todays date.

2. Do I need to show the path where the new file will be saved too?

3. How do I overcome the problem if the Archieve file has already been created for that day?

Sub Test11()
'
' Macro11 Macro
' Macro recorded 17/01/2005 by Nick
'

'
Workbooks.Open Filename:="C:\My Documents\MasterFile.xls"
Windows("Current").Activate
Range("A4:G13").Select
Selection.Copy
Windows("MasterFile.xls").Activate
Range("A4").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A4").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\My Documents\Archieve050115.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Range("A4").Select
End Sub

 

[HalfAce]

OK, I think I understand.
This assumes a few things.
1. The "Current" file is in the same folder that your MasterFile is in, and this is also the same folder you want the "Archieve" file(s) to get saved in.
(If any of these are to use a different path, you'll need to supply the path(s).)
2. There are no other files with the string "MasterFile" in their names.
3. Your MasterFile is in fact named "MasterFile" (or at least has that string in its name).

This code goes in the "Current" file (in a standard module).
It will work whether or not your MasterFile is already open at the time, and whether or not there already exists a file named for the same date.

Sub CopyToMasterFile()
Dim Wb As Workbook
Dim i As String
Application.ScreenUpdating = False
With Range("Z1")
    .Value = Now
    .NumberFormat = "mmddyy"
End With
i = [Z1].Text
[Z1].ClearContents
Range("A4:Y5121").Copy
On Error Resume Next
Set Wb = Workbooks("MasterFile.xls")
    If Wb Is Nothing Then '(MasterFile is not open so open it.)
    Workbooks.Open Filename:="MasterFile.xls"
    Set Wb = Nothing
    On Error GoTo 0
    Else '(MasterFile is already open so activate it.)
    Windows("MasterFile").Activate
    Set Wb = Nothing
    On Error GoTo 0
End If
[A4].Select
ActiveSheet.Paste
Range("A4:Y5121").Value = Range("A4:Y5121").Value
[A1].Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="Archieve" & i & ".xls"
ActiveWorkbook.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

This come a little closer to what you're after?
Dan

 

[BluesBros]

Dan,

Have two questions:-

1. You look in "Z1" at the start of the macro. What does this do?

2. I get an error box that shows up when the macro is running all it says is 400 with a red cross. The only thing that seems to happen is the MasterFile opens and its at this point the error appears. Any ideas as to what is causing this?

Not sure if you needed to know but I'm using Excel 2000

Thanks (again)

Nick

 

[HalfAce]

Hi Nick,
The reference to Z1 is where we're temporarily putting the current date, to be used in the file's SaveAs name. The date gets entered there and then formatted the way you asked for it:
With Range("Z1")
.Value = Now
.NumberFormat = "mmddyy"
End With
And then we assign the variable i as whatever text is in cell Z1, so i becomes the date with the proper format. We then just clear out Z1 as we don't need it anymore and then refer to i later in the code when we want the date. (You'll see the reference to i in the name we save the workbook under towards the end of the code.)

I don't know off hand what the error 400 would be. Normally it will describe what the error is. Which line of code is highlighted when you hit debug?

(I'm using XL 2003, but it ought to work fine with 2000. I'll check it out at work tomorrow. I use 2000 there.)
In the meantime, maybe someone who knows what error 400 is can clue us both in.

Dan

 

[BluesBros]

Dan,

Have played around with the code you supplied and now seem to have got over the 400 error problem! Don't know what caused it but the code below seems to be working. Not sure if there is a better way to have the code but at least its doing what I need.

Thanks for your help.

Nick

Sub CopyToMasterFile()
Dim Wb As Workbook
'Dim i As String
Application.ScreenUpdating = False

On Error Resume Next
Set Wb = Workbooks("MasterFile.xls")
If Wb Is Nothing Then '(MasterFile is not open so open it.)
Workbooks.Open Filename:="MasterFile.xls"
Set Wb = Nothing
On Error GoTo 0
Else '(MasterFile is already open so activate it.)
Windows("MasterFile").Activate
Set Wb = Nothing
On Error GoTo 0
End If

Windows("Book1").Activate
Range("A4:Y5121").Select
Selection.Copy
Windows("MasterFile.xls").Activate
Range("A4").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A4").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="Archieve " & Format(Now(), "yy mm dd") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Range("A4").Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[HalfAce]

You're most welcome.
Don't know what the error was being caused by, but great job figuring out a way to make it work.
I took the liberty of cleaning up some unecessary stuff, but (functionally) leaving it as is. You might want to give it a shot, but make no mistake, for what you're doing and as often as you'll be doing it, what you've got is just fine.
Again, good work.

Sub CopyToMasterFile()
Dim Wb As Workbook
Application.ScreenUpdating = False
On Error Resume Next
Set Wb = Workbooks("MasterFile.xls")
  If Wb Is Nothing Then '(MasterFile is not open so open it.)
    Workbooks.Open Filename:="MasterFile.xls"
    Set Wb = Nothing
    On Error GoTo 0
  Else '(MasterFile is already open so activate it.)
    Windows("MasterFile").Activate
    Set Wb = Nothing
    On Error GoTo 0
End If
Windows("Book1").Activate
Range("A4:Y5121").Select
Selection.Copy
Windows("MasterFile.xls").Activate
Range("A4").Select
Selection.PasteSpecial Paste:=xlFormats
Selection.Value = Selection.Value
Range("A4").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="Archieve " & Format(Now(), "yy mm dd") & ".xls"
ActiveWindow.Close
Range("A4").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Dan