Macro to copy data from three worksheets into new file? 1

[deisehun]

I have three Excel worksheets - which detail Sales orders.

A sister office wants to connect into a shared network drive and grab the reports on a daily basis. The reports have the queries behind them, and as such they would be able to see Live data.

Is it possible to run a Macro to copy the three worksheets into a new file and 'Paste Special' so there was no query behind it?

Thanks

 

[pbcharlie]

Hi there, just saw your question.
I am sure that if you record the macro whilst you are doing your copy and pasting you will get the desired results.

I just quickly tried it and got the following

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 18/05/2007 by Charlie Snedden
'

'
    Cells.Select
    Selection.Copy
    Windows("Book3").Activate
    Cells.Select
    ActiveSheet.Paste
    Windows("Book4").Activate
    Sheets("Sheet2").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book3").Activate
    Sheets("Sheet2").Select
    Cells.Select
    ActiveSheet.Paste
    Windows("Book4").Activate
    Sheets("Sheet3").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book3").Activate
    Cells.Select
    Sheets("Sheet3").Select
    Cells.Select
    ActiveSheet.Paste
End Sub

hope this helps

Charlie

 

[deisehun]

Thanks Charlie - I'm totally clueless when it comes to Macros

So if we say the existing three worksheets are Books 1-3 in book1.xls

If I want to save them to the same file name in the same format - would the above code be the same?

Do I go to Tools/Macros/Record new Macro - give it a name

Copy and paste the three worksheets into a new file - stop the macro

Rerun the query on the original file and go to Macros again and press 'go'

Would I need to have both files open?

Thanks

 

[pbcharlie]

Do I go to Tools/Macros/Record new Macro - give it a name

Copy and paste the three worksheets into a new file - stop the macro

Rerun the query on the original file and go to Macros again and press 'go'

Would I need to have both files open?

Yes, what I would do is record the macro into the xls that you want the data into, that way when you open the xls with that data you want to copy the macro will remember the xls that you want to open.

have a play with recoding macros, you will get the hang of them.


Charlie

 

[deisehun]

I have the copy & paste special Macro working fine, but now I need the output file as a .csv file with ; seperating the fields, like below:

10768;23714;;100214;KG;150;620;04.05.2007

When I try and save as a .csv file, the field seperation is standard - i.e First field is column A, 2nd field is in column b etc

Can anyone help?

Thanks...

 

[SkipVought]

On a separate sheet, concatenate the cells on the first sheet into Column A, separating each cell with TEXT ; ...
[tt]
=Sheet1!A1&";"&Sheet1!B1&";"&......
[/tt]
Then SaveAs the sheet a SPACE DELIMITED file (*.prn)

Skip,

_[red][/red]

 

[deisehun]

To concatenate the cells, do I have the the CONCATENATE command or is there an easier way?

Thanks

 

[SkipVought]

I just showed you an example of concatenation.

Skip,

_[red][/red]