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.

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.

Hi,

thanks for the prompt reply, that worked great. I was thinking would this be
easy enough to change into a userform type of thing? For example, when
someone else uses the same sheet a form would appear asking them what data
they want to extract onto a new tab and what they want the tab to be called
etc?

thanks again.

"Joel" wrote:


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.

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.

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.

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.

Not on my pc it doesn't

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

sorry, i did know that.
thanks for help, have sorted it now..

"Mike H" wrote:

Not on my pc it doesn't

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

I think you've confused:

worksheets("sheet1").copy
or
worksheets("sheet1").move

with
worksheets.add



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.


--

Dave Peterson

Hi Joel,

Its been a few weeks since i last spoke to you about the below Joel, but i
modified the code you gave for my own stuff but somehow it does something
weird but not want i want it to!

Here is my code with my own info in it. I have added my own sheet names but
dont know what im doing wrong. The original code can be found in the original
post. Please help. thanks so much.

Sub splitdata()

AAACol = Array("A", "B", "C")
BBBCol = Array("D", "E")
CCCcol = Array("F")
DDDcol = Array("G", "H")


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

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

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

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

With Sheets("XXX")
ColCount = 1
For Each col In AAACol
.Columns(col).Copy _
Destination:=AAA.Columns(ColCount)
ColCount = ColCount + 1
Next col

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

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

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

End With
End With

End Sub


"Dave Peterson" wrote:

I think you've confused:

worksheets("sheet1").copy
or
worksheets("sheet1").move

with
worksheets.add



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.


--

Dave Peterson

I'm not Joel, but you may want to explain what that weird thing is.

Maybe it's because you didn't qualify this line:
With Sheets("XXX")
as
With .Sheets("XXX")



Gemz wrote:

Hi Joel,

Its been a few weeks since i last spoke to you about the below Joel, but i
modified the code you gave for my own stuff but somehow it does something
weird but not want i want it to!

Here is my code with my own info in it. I have added my own sheet names but
dont know what im doing wrong. The original code can be found in the original
post. Please help. thanks so much.

Sub splitdata()

AAACol = Array("A", "B", "C")
BBBCol = Array("D", "E")
CCCcol = Array("F")
DDDcol = Array("G", "H")

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

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

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

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

With Sheets("XXX")
ColCount = 1
For Each col In AAACol
.Columns(col).Copy _
Destination:=AAA.Columns(ColCount)
ColCount = ColCount + 1
Next col

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

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

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

End With
End With

End Sub

"Dave Peterson" wrote:

I think you've confused:

worksheets("sheet1").copy
or
worksheets("sheet1").move

with
worksheets.add



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.


--

Dave Peterson


--

Dave Peterson