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 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 - Jun 17, 2013 at 11:54 AM
Hello,

I have a monthly report with multiple clients which varies month to month. I need to figure out how to create a New Work Book from my sorted & subtotaled Data that I have on my "Master" sheet.

Expl:


1st Row=
1st clients name & Subtotals $4563.00

Columns A - K =
Invoice Age (Invoice Date)/ Invoice #/File #/Client Claim #/Insured/Client Company Name/Invoice Outstanding Balance Invoice Amount Billed/Invoice Amount Paid/Client Claims Rep (First/Last)/Invoice Date

I found the Macro to have all the sorted Data go to its own Tab but it would be even easier if I could find a way to get them into their own Book.


Please Help! Thanks!!!

-Suzy


3 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
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
0
SuzyDataAnalyst
Jun 17, 2013 at 09:49 AM
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
0
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
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:
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
0