Excel - Macro help please!!!
Closed
SuzyDataAnalyst
Posts
2
Registration date
Thursday 13 June 2013
Status
Member
Last seen
14 June 2013
-
14 Jun 2013 à 09:49
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 17 Jun 2013 à 11:54
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 17 Jun 2013 à 11:54
Related:
- Excel - Macro help please!!!
- Excel mod apk for pc - Download - Spreadsheets
- Excel online macros - Guide
- Vat calculation excel - Guide
- Kernel for excel repair - Download - Backup and recovery
- Excel run macro on open - Guide
3 responses
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
16 Jun 2013 à 09:36
16 Jun 2013 à 09:36
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 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
17 Jun 2013 à 11:54
17 Jun 2013 à 11:54
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
