Sub MakeReports()

Report1Col = Array("A", "B", "C", "G", "H", "Y", "Z")
Report2Col = Array("D", "E", "J", "K", "L")
Report3Col = Array("AA", "AB", "AC")


With ThisWorkbook
.Worksheets.Add after:=.Sheets(.Sheets.Count)
Set RPT1 = ActiveSheet
RPT1.Name = "Report1"

.Worksheets.Add after:=.Sheets(.Sheets.Count)
Set RPT2 = ActiveSheet
RPT2.Name = "Report2"

.Worksheets.Add after:=.Sheets(.Sheets.Count)
Set RPT3 = ActiveSheet
RPT3.Name = "Report3"

With Sheets("Data")
ColCount = 1
For Each col In Report1Col
.Columns(col).Copy _
Destination:=RPT1.Columns(ColCount)
ColCount = ColCount + 1
Next col

ColCount = 1
For Each col In Report2Col
.Columns(col).Copy _
Destination:=RPT2.Columns(ColCount)
ColCount = ColCount + 1
Next col

ColCount = 1
For Each col In Report3Col
.Columns(col).Copy _
Destination:=RPT3.Columns(ColCount)
ColCount = ColCount + 1
Next col

End With
End With

End Sub

"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.