I dont understand, was this how i can make it like a userform input - so the
user themselves specify what columns and the sheet name etc? sorry if i didnt
make it clear before.

thanks.

"Joel" wrote:

you code will put the new sheets in a new workbook unles you have AFTER

from
Worksheets.Add
to
Worksheets.Add after:=sheets(sheets.count)

"Mike H" wrote:

Perhaps

Insert into a module

Sub stantial()
For x = 1 To 3
Worksheets.Add
ActiveSheet.Name = "Report " & x
Next

Set myrange1 = Sheets("Sheet1").Range("A:A,B:B,C:C,G:G,H:H,Y:Y,Z: Z")
Set myrange2 = Sheets("Sheet1").Range("D:D:J:J:K:K:L:L")
Set myrange3 = Sheets("Sheet1").Range("AA:AA,AB:AB,AC:AC")

myrange1.Copy
Sheets("Report 1").Range("A1").PasteSpecial
myrange2.Copy
Sheets("Report 2").Range("A1").PasteSpecial
myrange3.Copy
Sheets("Report 3").Range("A1").PasteSpecial

End Sub

Mike

"Gemz" wrote:

I have a sheet with data and I need to use it for different things €“ I am
using different bits of the data all the time because I need to produce a
few different reports from the same data set. Is there a way I can get a
macro to actually produce all the different reports that I want? For example,
I would like to macro to firstly name a new tab in the workbook as €˜Report 1
then enter the specified columns that I want into that named sheet. For
example, in sheet named €˜Report 1 I would like to see columns A,B,C,G,H,Y,Z
and then in the same workbook I would like to get another sheet renamed to
€˜Report 2 and then copy columns €˜D,E,J,K,L and then finally another sheet
to be renamed to €˜Report 3 and then copy across columns €˜AA, AB, AC . And I
might need to repeat this a couple more times.

Thanks in advance.