Export and save
 

Hello all,

I am a Help Desk assistant(really a student worker) at my college and
have been commissioned, with my previous experience, as the
database/excel guy. I have a lot of background in making specialised
DB's and workbooks, but I have hit a problem I can't resolve.

I have built a spreadsheet with macros that allow my boss'es boss to
simply copy information that he needs from a webserver(which I have no
other way of obtaining the data other than copying and pasting) and
then press a key combo that runs a scripted macro to paste the data and
update everything accordingly. There are two batches of data that must
be retrieved, and seperate macros for both.

The issue that I am facing is creating a macro to export the "report"
spreadsheet, which is the part that is to be printed to a seperate
workbook and automatically save it as a workbook that is named after
the year and month of the report. I cannot seem to get this to work.
Below is the code I have used that seems to work, but there are two
issues I need to resolve:

1. When using the code below, it creates a link to the previous
workbook(which I do not want).

2. It also produces an error in the line of code for the PasteSpecial.

The idea is to copy two worksheets over to new workbook, disassociate
it with the old workbook, and save the workbook automatically. I had a
different script working before, but it has seemed to collapse on me.

CODE:

Windows("Monthly Comparison.xls").Activate
Sheets(Array("Tickets", "DATA")).Copy

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

:ENDCODE

Export and save
 

Try:

With Workbooks("Monthly Comparison.xls")
.Sheets(Array("Tickets", "DATA")).Copy
End With
With ActiveWorkbook
With .Sheets("Tickets").UsedRange
.Value = .Value
End With
With .Sheets("DATA").UsedRange
.Value = .Value
End With
End With

Hope this helps
Rowan

wrote:
Hello all,

I am a Help Desk assistant(really a student worker) at my college and
have been commissioned, with my previous experience, as the
database/excel guy. I have a lot of background in making specialised
DB's and workbooks, but I have hit a problem I can't resolve.

I have built a spreadsheet with macros that allow my boss'es boss to
simply copy information that he needs from a webserver(which I have no
other way of obtaining the data other than copying and pasting) and
then press a key combo that runs a scripted macro to paste the data and
update everything accordingly. There are two batches of data that must
be retrieved, and seperate macros for both.

The issue that I am facing is creating a macro to export the "report"
spreadsheet, which is the part that is to be printed to a seperate
workbook and automatically save it as a workbook that is named after
the year and month of the report. I cannot seem to get this to work.
Below is the code I have used that seems to work, but there are two
issues I need to resolve:

1. When using the code below, it creates a link to the previous
workbook(which I do not want).

2. It also produces an error in the line of code for the PasteSpecial.

The idea is to copy two worksheets over to new workbook, disassociate
it with the old workbook, and save the workbook automatically. I had a
different script working before, but it has seemed to collapse on me.

CODE:

Windows("Monthly Comparison.xls").Activate
Sheets(Array("Tickets", "DATA")).Copy

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

:ENDCODE