Excel - Macro help please!!!

Closed
Report
Posts
2
Registration date
Thursday June 13, 2013
Status
Member
Last seen
June 14, 2013
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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

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
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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