Excel - Macro help please!!!
Closed
SuzyDataAnalyst
Posts
2
Registration date
Thursday June 13, 2013
Status
Member
Last seen
June 14, 2013
-
Jun 14, 2013 at 09:49 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 17, 2013 at 11:54 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 17, 2013 at 11:54 AM
Related:
- Excel - Macro help please!!!
- Spell number in excel without macro - Guide
- Macros in excel download - Download - Spreadsheets
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Excel marksheet - Guide
3 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 16, 2013 at 09:36 AM
Jun 16, 2013 at 09:36 AM
Suzy, if you have that macro, then all you need is to tweak that macro to say instead of new sheet, create a new workbook, and add to that sheet. If you are still lost, could you post that macro so someone can help you in tweaking it
Thank you rizcisa1, I did try to tweak it but I'm not sure where to put new workbook, I'm new at this macro stuff. I cant get this macro to work now. Im not sure what I have done wrong this time...
The macro I have is:
Sub SplitData()
mycount = 0
myrow = 0
Do
mycount = mycount + 1
oldrow = myrow + 1
Sheets("Master").Select
Do
myrow = myrow + 1
Loop Until Sheets("Master").Range("A" & myrow) = ""
Sheets.Add
ActiveSheet.Name = "Data" & mycount
Sheets("Master").Select
Rows(oldrow & ":" & myrow).Select
Selection.Copy
Sheets("Data" & mycount).Select
Range("A1").Select
ActiveSheet.Paste
Loop Until Sheets("Master").Range("A" & myrow + 1) = ""
End Sub
The macro I have is:
Sub SplitData()
mycount = 0
myrow = 0
Do
mycount = mycount + 1
oldrow = myrow + 1
Sheets("Master").Select
Do
myrow = myrow + 1
Loop Until Sheets("Master").Range("A" & myrow) = ""
Sheets.Add
ActiveSheet.Name = "Data" & mycount
Sheets("Master").Select
Rows(oldrow & ":" & myrow).Select
Selection.Copy
Sheets("Data" & mycount).Select
Range("A1").Select
ActiveSheet.Paste
Loop Until Sheets("Master").Range("A" & myrow + 1) = ""
End Sub
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jun 17, 2013 at 11:54 AM
Jun 17, 2013 at 11:54 AM
Hi SuzyDataAnalyst,
First some advise:
When you use Copy/Paste in your code then end your code using the following line:
Application.CutCopyMode=False
To clear Excel's memory.
Your first loop will never loop since it has the same condition as the second loop.
So the first "Do" and "Loop Until Sheets("Master").Range("A" & myrow + 1) = """ are unnecessary.
Back to your query. I am assuming you sort mannually and then run the code.
The following code will ask you to input the name of the new workbook, then the name of the sheet.
Once data is copied your new workbook is saved.
Here is the code:
Let us know if adjustments are desired.
Best regards,
Trowa
First some advise:
When you use Copy/Paste in your code then end your code using the following line:
Application.CutCopyMode=False
To clear Excel's memory.
Your first loop will never loop since it has the same condition as the second loop.
So the first "Do" and "Loop Until Sheets("Master").Range("A" & myrow + 1) = """ are unnecessary.
Back to your query. I am assuming you sort mannually and then run the code.
The following code will ask you to input the name of the new workbook, then the name of the sheet.
Once data is copied your new workbook is saved.
Here is the code:
Sub SplitData()
Dim MyBookName, MySheetName As String
MyBookName = InputBox("Please give the name of the new Workbook") & ".xls" 'the extention depends on which Excel version you are using
MySheetName = InputBox("Please give the name of the new Worksheet")
Sheets("Master").Range("A1").CurrentRegion.Copy
Workbooks.Add
Worksheets.Add
ActiveSheet.Name = MySheetName
Range("A1").PasteSpecial
ActiveWorkbook.SaveAs Filename:="C:\Lotus\" & MyBookName 'adjust path to match yours.
Application.CutCopyMode = False
End Sub
Let us know if adjustments are desired.
Best regards,
Trowa
