|
Macro to split data
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. |
Macro to split data
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. |
Macro to split data
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. |
Macro to split data
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. |
Macro to split data
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. |
Macro to split data
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. |
Macro to split data
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. |
Macro to split data
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. |
Macro to split data
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 |
Macro to split data
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 |
Macro to split data
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 |
Macro to split data
Hi Dave,
I changed the line to: With .Sheets("XXX") but now when i run the macro it runs but highlights the above line and the tab in my spreadsheet which was called XXX changes to DDD (which is the last thing specified in my code). I dont know why thats happening! Thanks for your help. "Dave Peterson" wrote: 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 |
Macro to split data
I don't see anything in your code that would do this.
I assumed that Sheets("xxx") was part of the workbook that owned the code--that it belonged under the "with Thisworkbook" line. Is that correct? And did you change anything else in your code? If you did, it's time to repost it. Gemz wrote: Hi Dave, I changed the line to: With .Sheets("XXX") but now when i run the macro it runs but highlights the above line and the tab in my spreadsheet which was called XXX changes to DDD (which is the last thing specified in my code). I dont know why thats happening! Thanks for your help. "Dave Peterson" wrote: 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 -- Dave Peterson |
Macro to split data
Hi,
I didnt change anything, below is my code. Just to explain what i am doing: 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 the AAA,BBB,CCC,DDD Are the names i want to give to 4 new sheets in the workbook. XXX is the current sheet with all the data which will split into each of the AAA,BBB,CCC,DDD depending on the criteria they meet. As the code states certain columns from sheet XXX would be copied into AAA then BBB etc. I used the original code given by Joel and just substituted the names in there for mine.. i dont know what i have done wrong! although the bit in Joels code which said RPT1, RPT2 etc.. i was unsure of here and just re-stated AAA,BBB etc. Also, now somehow my macros in the personal.xls book have disappeared! it reported and error and then they just disappeared? i dont have the option of selecting from personal.xls book. i know this is unrelated to the above but is there any way i can sort this? Thanks alot. "Dave Peterson" wrote: I don't see anything in your code that would do this. I assumed that Sheets("xxx") was part of the workbook that owned the code--that it belonged under the "with Thisworkbook" line. Is that correct? And did you change anything else in your code? If you did, it's time to repost it. Gemz wrote: Hi Dave, I changed the line to: With .Sheets("XXX") but now when i run the macro it runs but highlights the above line and the tab in my spreadsheet which was called XXX changes to DDD (which is the last thing specified in my code). I dont know why thats happening! Thanks for your help. "Dave Peterson" wrote: 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 -- Dave Peterson |
Macro to split data
change:
With.Sheets("XXX") to With .Sheets("XXX") Gemz wrote: Hi, I didnt change anything, below is my code. Just to explain what i am doing: 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 the AAA,BBB,CCC,DDD Are the names i want to give to 4 new sheets in the workbook. XXX is the current sheet with all the data which will split into each of the AAA,BBB,CCC,DDD depending on the criteria they meet. As the code states certain columns from sheet XXX would be copied into AAA then BBB etc. I used the original code given by Joel and just substituted the names in there for mine.. i dont know what i have done wrong! although the bit in Joels code which said RPT1, RPT2 etc.. i was unsure of here and just re-stated AAA,BBB etc. Also, now somehow my macros in the personal.xls book have disappeared! it reported and error and then they just disappeared? i dont have the option of selecting from personal.xls book. i know this is unrelated to the above but is there any way i can sort this? Thanks alot. "Dave Peterson" wrote: I don't see anything in your code that would do this. I assumed that Sheets("xxx") was part of the workbook that owned the code--that it belonged under the "with Thisworkbook" line. Is that correct? And did you change anything else in your code? If you did, it's time to repost it. Gemz wrote: Hi Dave, I changed the line to: With .Sheets("XXX") but now when i run the macro it runs but highlights the above line and the tab in my spreadsheet which was called XXX changes to DDD (which is the last thing specified in my code). I dont know why thats happening! Thanks for your help. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
Macro to split data
Depending on the version of excel you're running, maybe your personal.xls file
was marked as bad and was disabled. Open excel Help|About MS Excel|Click the Disabled Items button Try to re-enable it. Gemz wrote: Hi, I didnt change anything, below is my code. Just to explain what i am doing: 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 the AAA,BBB,CCC,DDD Are the names i want to give to 4 new sheets in the workbook. XXX is the current sheet with all the data which will split into each of the AAA,BBB,CCC,DDD depending on the criteria they meet. As the code states certain columns from sheet XXX would be copied into AAA then BBB etc. I used the original code given by Joel and just substituted the names in there for mine.. i dont know what i have done wrong! although the bit in Joels code which said RPT1, RPT2 etc.. i was unsure of here and just re-stated AAA,BBB etc. Also, now somehow my macros in the personal.xls book have disappeared! it reported and error and then they just disappeared? i dont have the option of selecting from personal.xls book. i know this is unrelated to the above but is there any way i can sort this? Thanks alot. "Dave Peterson" wrote: I don't see anything in your code that would do this. I assumed that Sheets("xxx") was part of the workbook that owned the code--that it belonged under the "with Thisworkbook" line. Is that correct? And did you change anything else in your code? If you did, it's time to repost it. Gemz wrote: Hi Dave, I changed the line to: With .Sheets("XXX") but now when i run the macro it runs but highlights the above line and the tab in my spreadsheet which was called XXX changes to DDD (which is the last thing specified in my code). I dont know why thats happening! Thanks for your help. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
Macro to split data
Hi,
It appears to have enabled the personal.xls but it doesnt let me do anything, everytime i go to play the macro it keeps reporting error and recovers my docs and then repeats the whole process everytime i want to go and play the macros. please help!! i have a tonne of macros in the personal.xls book. Also, any advise on getting the macro working? because it still isnt. Would you like me to send you my workbook so you can see? but this can only be done on your personal email... thanks for your help! "Dave Peterson" wrote: Depending on the version of excel you're running, maybe your personal.xls file was marked as bad and was disabled. Open excel Help|About MS Excel|Click the Disabled Items button Try to re-enable it. Gemz wrote: Hi, I didnt change anything, below is my code. Just to explain what i am doing: 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 the AAA,BBB,CCC,DDD Are the names i want to give to 4 new sheets in the workbook. XXX is the current sheet with all the data which will split into each of the AAA,BBB,CCC,DDD depending on the criteria they meet. As the code states certain columns from sheet XXX would be copied into AAA then BBB etc. I used the original code given by Joel and just substituted the names in there for mine.. i dont know what i have done wrong! although the bit in Joels code which said RPT1, RPT2 etc.. i was unsure of here and just re-stated AAA,BBB etc. Also, now somehow my macros in the personal.xls book have disappeared! it reported and error and then they just disappeared? i dont have the option of selecting from personal.xls book. i know this is unrelated to the above but is there any way i can sort this? Thanks alot. "Dave Peterson" wrote: I don't see anything in your code that would do this. I assumed that Sheets("xxx") was part of the workbook that owned the code--that it belonged under the "with Thisworkbook" line. Is that correct? And did you change anything else in your code? If you did, it's time to repost it. Gemz wrote: Hi Dave, I changed the line to: With .Sheets("XXX") but now when i run the macro it runs but highlights the above line and the tab in my spreadsheet which was called XXX changes to DDD (which is the last thing specified in my code). I dont know why thats happening! Thanks for your help. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
Macro to split data
It sounds like your personal.xls file is really corrupted.
I'd throw away that copy of the file and put a backup version in its place. No thanks to the offer of sending me a file. Gemz wrote: Hi, It appears to have enabled the personal.xls but it doesnt let me do anything, everytime i go to play the macro it keeps reporting error and recovers my docs and then repeats the whole process everytime i want to go and play the macros. please help!! i have a tonne of macros in the personal.xls book. Also, any advise on getting the macro working? because it still isnt. Would you like me to send you my workbook so you can see? but this can only be done on your personal email... thanks for your help! "Dave Peterson" wrote: Depending on the version of excel you're running, maybe your personal.xls file was marked as bad and was disabled. Open excel Help|About MS Excel|Click the Disabled Items button Try to re-enable it. Gemz wrote: Hi, I didnt change anything, below is my code. Just to explain what i am doing: 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 the AAA,BBB,CCC,DDD Are the names i want to give to 4 new sheets in the workbook. XXX is the current sheet with all the data which will split into each of the AAA,BBB,CCC,DDD depending on the criteria they meet. As the code states certain columns from sheet XXX would be copied into AAA then BBB etc. I used the original code given by Joel and just substituted the names in there for mine.. i dont know what i have done wrong! although the bit in Joels code which said RPT1, RPT2 etc.. i was unsure of here and just re-stated AAA,BBB etc. Also, now somehow my macros in the personal.xls book have disappeared! it reported and error and then they just disappeared? i dont have the option of selecting from personal.xls book. i know this is unrelated to the above but is there any way i can sort this? Thanks alot. "Dave Peterson" wrote: I don't see anything in your code that would do this. I assumed that Sheets("xxx") was part of the workbook that owned the code--that it belonged under the "with Thisworkbook" line. Is that correct? And did you change anything else in your code? If you did, it's time to repost it. Gemz wrote: Hi Dave, I changed the line to: With .Sheets("XXX") but now when i run the macro it runs but highlights the above line and the tab in my spreadsheet which was called XXX changes to DDD (which is the last thing specified in my code). I dont know why thats happening! Thanks for your help. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Macro to split data
Hi,
it doesnt seem to make a difference what file i open, every excel file i open it just doesnt let me do anything in the macro menu, it just keeps reporting error. i have even tried on a fresh new excel sheet and still no difference... thanks "Dave Peterson" wrote: It sounds like your personal.xls file is really corrupted. I'd throw away that copy of the file and put a backup version in its place. No thanks to the offer of sending me a file. Gemz wrote: Hi, It appears to have enabled the personal.xls but it doesnt let me do anything, everytime i go to play the macro it keeps reporting error and recovers my docs and then repeats the whole process everytime i want to go and play the macros. please help!! i have a tonne of macros in the personal.xls book. Also, any advise on getting the macro working? because it still isnt. Would you like me to send you my workbook so you can see? but this can only be done on your personal email... thanks for your help! "Dave Peterson" wrote: Depending on the version of excel you're running, maybe your personal.xls file was marked as bad and was disabled. Open excel Help|About MS Excel|Click the Disabled Items button Try to re-enable it. Gemz wrote: Hi, I didnt change anything, below is my code. Just to explain what i am doing: 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 the AAA,BBB,CCC,DDD Are the names i want to give to 4 new sheets in the workbook. XXX is the current sheet with all the data which will split into each of the AAA,BBB,CCC,DDD depending on the criteria they meet. As the code states certain columns from sheet XXX would be copied into AAA then BBB etc. I used the original code given by Joel and just substituted the names in there for mine.. i dont know what i have done wrong! although the bit in Joels code which said RPT1, RPT2 etc.. i was unsure of here and just re-stated AAA,BBB etc. Also, now somehow my macros in the personal.xls book have disappeared! it reported and error and then they just disappeared? i dont have the option of selecting from personal.xls book. i know this is unrelated to the above but is there any way i can sort this? Thanks alot. "Dave Peterson" wrote: I don't see anything in your code that would do this. I assumed that Sheets("xxx") was part of the workbook that owned the code--that it belonged under the "with Thisworkbook" line. Is that correct? And did you change anything else in your code? If you did, it's time to repost it. Gemz wrote: Hi Dave, I changed the line to: With .Sheets("XXX") but now when i run the macro it runs but highlights the above line and the tab in my spreadsheet which was called XXX changes to DDD (which is the last thing specified in my code). I dont know why thats happening! Thanks for your help. "Dave Peterson" wrote: 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 -- Dave Peterson |
Macro to split data
What are you trying and what error do you see?
Gemz wrote: Hi, it doesnt seem to make a difference what file i open, every excel file i open it just doesnt let me do anything in the macro menu, it just keeps reporting error. i have even tried on a fresh new excel sheet and still no difference... thanks "Dave Peterson" wrote: It sounds like your personal.xls file is really corrupted. I'd throw away that copy of the file and put a backup version in its place. No thanks to the offer of sending me a file. Gemz wrote: Hi, It appears to have enabled the personal.xls but it doesnt let me do anything, everytime i go to play the macro it keeps reporting error and recovers my docs and then repeats the whole process everytime i want to go and play the macros. please help!! i have a tonne of macros in the personal.xls book. Also, any advise on getting the macro working? because it still isnt. Would you like me to send you my workbook so you can see? but this can only be done on your personal email... thanks for your help! "Dave Peterson" wrote: Depending on the version of excel you're running, maybe your personal.xls file was marked as bad and was disabled. Open excel Help|About MS Excel|Click the Disabled Items button Try to re-enable it. Gemz wrote: Hi, I didnt change anything, below is my code. Just to explain what i am doing: 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 the AAA,BBB,CCC,DDD Are the names i want to give to 4 new sheets in the workbook. XXX is the current sheet with all the data which will split into each of the AAA,BBB,CCC,DDD depending on the criteria they meet. As the code states certain columns from sheet XXX would be copied into AAA then BBB etc. I used the original code given by Joel and just substituted the names in there for mine.. i dont know what i have done wrong! although the bit in Joels code which said RPT1, RPT2 etc.. i was unsure of here and just re-stated AAA,BBB etc. Also, now somehow my macros in the personal.xls book have disappeared! it reported and error and then they just disappeared? i dont have the option of selecting from personal.xls book. i know this is unrelated to the above but is there any way i can sort this? Thanks alot. "Dave Peterson" wrote: I don't see anything in your code that would do this. I assumed that Sheets("xxx") was part of the workbook that owned the code--that it belonged under the "with Thisworkbook" line. Is that correct? And did you change anything else in your code? If you did, it's time to repost it. Gemz wrote: Hi Dave, I changed the line to: With .Sheets("XXX") but now when i run the macro it runs but highlights the above line and the tab in my spreadsheet which was called XXX changes to DDD (which is the last thing specified in my code). I dont know why thats happening! Thanks for your help. "Dave Peterson" wrote: 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 ÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆÀšÃ‚¢Ãƒ ¢â€šÂ¬Ã€¦Ã‚¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚¬Ã ƒÆ€¹Ã€¦Ã¢â€šÂ¬Ã…€œR eport 1ÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆÀšÃ‚¢Ãƒ ¢â€šÂ¬Ã€¦Ã‚¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚¬Ã ƒÆÀšÃ‚¢ÃƒÂ¢Ã¢â‚¬Å¡Ã‚¬Ã€¦Ã‚¾Ã ĉ‚¬Å¡Ãƒ€šÃ‚¢ then enter the specified columns that I want into that named sheet. For example, in sheet named ÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆÀšÃ‚¢Ãƒ ¢â€šÂ¬Ã€¦Ã‚¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚¬Ã ƒÆ€¹Ã€¦Ã¢â€šÂ¬Ã…€œR eport 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 ÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆÀšÃ‚¢Ãƒ ¢â€šÂ¬Ã€¦Ã‚¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚¬Ã ƒÆ€¹Ã€¦Ã¢â€šÂ¬Ã…€œR eport 2ÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆÀšÃ‚¢Ãƒ ¢â€šÂ¬Ã€¦Ã‚¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚¬Ã ƒÆÀšÃ‚¢ÃƒÂ¢Ã¢â‚¬Å¡Ã‚¬Ã€¦Ã‚¾Ã ĉ‚¬Å¡Ãƒ€šÃ‚¢ and then copy columns ÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆÀšÃ‚¢Ãƒ ¢â€šÂ¬Ã€¦Ã‚¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚¬Ã ƒÆ€¹Ã€¦Ã¢â€šÂ¬Ã…€œD ,E,J,K,LÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆÀšÃ‚¢Ãƒ ¢â€šÂ¬Ã€¦Ã‚¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚¬Ã ƒÆÀšÃ‚¢ÃƒÂ¢Ã¢â‚¬Å¡Ã‚¬Ã€¦Ã‚¾Ã ĉ‚¬Å¡Ãƒ€šÃ‚¢ and then finally another sheet to be renamed to ÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆÀšÃ‚¢Ãƒ ¢â€šÂ¬Ã€¦Ã‚¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚¬Ã ƒÆ€¹Ã€¦Ã¢â€šÂ¬Ã…€œR eport 3ÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆÀšÃ‚¢Ãƒ ¢â€šÂ¬Ã€¦Ã‚¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚¬Ã ƒÆÀšÃ‚¢ÃƒÂ¢Ã¢â‚¬Å¡Ã‚¬Ã€¦Ã‚¾Ã ĉ‚¬Å¡Ãƒ€šÃ‚¢ and then copy across columns ÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆÀšÃ‚¢Ãƒ ¢â€šÂ¬Ã€¦Ã‚¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚¬Ã ƒÆ€¹Ã€¦Ã¢â€šÂ¬Ã…€œA A, AB, ACÃÆÀ €„¢ÃƒÆ’€šÃ€šÃ‚¢ÃƒÆÀšÃ‚¢Ãƒ ¢â€šÂ¬Ã€¦Ã‚¡Ãƒâ‚¬Å¡Ãƒ€šÃ‚¬Ã ƒÆÀšÃ‚¢ÃƒÂ¢Ã¢â‚¬Å¡Ã‚¬Ã€¦Ã‚¾Ã ĉ‚¬Å¡Ãƒ€šÃ‚¢ . And I might need to repeat this a couple more times. Thanks in advance. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Macro to split data
Hi,
Dont worry about it, i just sorted it now. All it needed was a windows update. I've also sorted the macro prob. thanks for help. "Dave Peterson" wrote: What are you trying and what error do you see? Gemz wrote: Hi, it doesnt seem to make a difference what file i open, every excel file i open it just doesnt let me do anything in the macro menu, it just keeps reporting error. i have even tried on a fresh new excel sheet and still no difference... thanks "Dave Peterson" wrote: It sounds like your personal.xls file is really corrupted. I'd throw away that copy of the file and put a backup version in its place. No thanks to the offer of sending me a file. Gemz wrote: Hi, It appears to have enabled the personal.xls but it doesnt let me do anything, everytime i go to play the macro it keeps reporting error and recovers my docs and then repeats the whole process everytime i want to go and play the macros. please help!! i have a tonne of macros in the personal.xls book. Also, any advise on getting the macro working? because it still isnt. Would you like me to send you my workbook so you can see? but this can only be done on your personal email... thanks for your help! "Dave Peterson" wrote: Depending on the version of excel you're running, maybe your personal.xls file was marked as bad and was disabled. Open excel Help|About MS Excel|Click the Disabled Items button Try to re-enable it. Gemz wrote: Hi, I didnt change anything, below is my code. Just to explain what i am doing: 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 the AAA,BBB,CCC,DDD Are the names i want to give to 4 new sheets in the workbook. XXX is the current sheet with all the data which will split into each of the AAA,BBB,CCC,DDD depending on the criteria they meet. As the code states certain columns from sheet XXX would be copied into AAA then BBB etc. I used the original code given by Joel and just substituted the names in there for mine.. i dont know what i have done wrong! although the bit in Joels code which said RPT1, RPT2 etc.. i was unsure of here and just re-stated AAA,BBB etc. Also, now somehow my macros in the personal.xls book have disappeared! it reported and error and then they just disappeared? i dont have the option of selecting from personal.xls book. i know this is unrelated to the above but is there any way i can sort this? Thanks alot. "Dave Peterson" wrote: I don't see anything in your code that would do this. I assumed that Sheets("xxx") was part of the workbook that owned the code--that it belonged under the "with Thisworkbook" line. Is that correct? And did you change anything else in your code? If you did, it's time to repost it. Gemz wrote: Hi Dave, I changed the line to: With .Sheets("XXX") but now when i run the macro it runs but highlights the above line and the tab in my spreadsheet which was called XXX changes to DDD (which is the last thing specified in my code). I dont know why thats happening! Thanks for your help. "Dave Peterson" wrote: 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. |
| All times are GMT +1. The time now is 07:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com