Using VBA to copy data into a closed workbook?????

[andrewrgd]

I am trying to copy and export sheet1 from an open workbook to another specific closed workbook ie. c:\my documents\test.xls. There will also be several exports so I need the test workbook to keep excepting the data.

Ideally I will create a command button to run the code in the open workbook that I'm exporting.

Cheers

 

[andrewrgd]

Please Help!

I have used the following code to copy the required data into an archieve file. Is it possible to amend the code so instead of going into a separate workbook each time it goes into the same workbook but different sheet?

Thanks!

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("Test").Activate
Range("A1:K7").Select
Selection.Copy
Windows("MasterFile.xls").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="Archieve " & Format(Now(), "dd mm yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Range("A1").Select

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

 

[yankee428]

How about if you add a new sheet before pasting and instead of SaveAs, just use Save:

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("Test").Activate
Range("A1:K7").Select
Selection.Copy
Windows("MasterFile.xls").Activate

Sheets.Add 'added

Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.Save 'added
'ActiveWorkbook.SaveAs Filename:="Archieve " & Format(Now(), "dd mm yyyy") & ".xls", _
'FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
'ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Range("A1").Select

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

 

[andrewrgd]

Excellent - Thanks. Lastly how do you put an identifier in the sheet name of the masterfile i.e the name of the file your copying?

Thank you so much!

 

[yankee428]

So if I read u right you are looking to change your sheet name based on some input somewhere. So you would need to adjust this to whatever your qualifier would be. Does that help?

Sub afdsf()
Dim qualifier As String
Dim msn As String
qualifier = "-1"
msn = Sheets(1).Name
Sheets(1).Name = msn & qualifier
End Sub