copying and pasting tabs macro

Hi there.

I need a macro that can cut and paste data (tabs) from one spreadsheet and paste it in another blank spreadsheet. Then go to another spreadsheet copy some data from there and paste it again in the blank worksheet and so on.

Can anyone help with this please?

Length of data may vary. Lots of spreadsheets and data so record may not be best option.

think it might be a move sheet type of command, but I don't know how that exactly works especially putting into a new blank work book, and then moving to another sheet after to copy and paste in. Please help

Thanks.

But what I need to do is a bit more complex than that.

This is the process.

I open up a spreadsheet, I need to copy data from a tab that is labelled "Area1". I need to move or copy and paste (values, formats) into new spreadsheet for Area 1. I then need to go to the data sheet again and find the tab "area 2" then copy paste and put into a new spreadsheet named "area2" and so on for 56 areas.

I then need to go to another data sheet and do the same process and there are 20 different data sheets that need to be copied and pasted into these new spreadsheets

Anyone have any ideas please?

Hi sorry for the confusion.

It would be all the data in the sheet, then that data pasted values and formats (get rid of formulas)

So just as 1 example.

I go to the data spreadsheet "machines"

I go to tab, "area1", I copy and paste all the data, everything and paste values and formats into a new spreadsheet named "area1".

I then go back into the data spreadsheet do the same for "area2" until I get to no 56.

I then go to the next data spreadsheet "expenditure", I do the same as above etc for 20 different sheets.

I'm not sure if its possible to do a macro for all of this in one.

Or whether you need a macro to do the job for each data spreadsheet.

Need them in a certain order as well

Hmm that didn't work at all!!

Hi again

When I run this I get the error

"Run time error 1004
You typed an invalid name for a sheet or chart. Make sure that

The name that you type does not exceed 31 chracters
The name does not contain any of the following character : \/?
or [or]
You did not leave the name blank"

The part of the code it highlights is

ActiveSheet.Name = Left(myTempWorkbook.Name, InStrRev(myTempWorkbook.Name, ".") - 1)

Have you any idea how I can fix this please?

Thank you

sub snb()
   for each sh in Thisworkbook.sheets
     sh.copy
     With activeworkbook
       .sheets(1).usedrange.value=sheets(1).usedrange.value
       .Saveas .sheets(1).name & ".xls"
       .close False
     end with
   Next
End Sub

Thanks.
I will give that a go

Ok, I copied that in over the part which has copy in and it didn't work.

Error said sh not defined

Remove 'option Explicit'