Need help to modify the VBA code

[jeremiah_j2k]

Hello All,

I'm working on a tracker that splits data from multiple rows into several new worksheets. I'm done with that part already and the challenge I have now is how to send the newly created worksheet via email. Please see the attached file for your reference.

Thanks in advance for any assistance that will be provided.

Sub SplitData()

Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
xTitleId = "Backlog Tracker"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
SplitRow = Application.InputBox("Split Row Num", xTitleId, 5, Type:=1)
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
resizeCount = SplitRow
If (WorkRng.Rows.Count - xRow.Row + 9) < SplitRow Then resizeCount = WorkRng.Rows.Count - xRow.Row + 1
xRow.Resize(resizeCount).Copy
Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
Application.ActiveSheet.Range("A1").PasteSpecial
Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Backlog_Report_Generator_ver4.xlsm

[Admin]

Hi

Welcome to forum !

See if this helps you.

http://www.excelfox.com/forum/f2/exc...ail+attachment

[snb]

Sub M_snb()
    Set sn = Application.InputBox("Range", , , , , , , 8)
    y = Application.InputBox("Split Row Num", , 5)

    ReDim sp(sn.Rows.Count \ y)
    For j = 0 To UBound(sp)
      sp(j) = sn.Rows(j * 5 + 1).Resize(y)
    Next
    
    For j = 0 To UBound(sp)
       Sheets.Add.Cells(1).Resize(UBound(sp(j)), UBound(sp(j), 2)) = sp(j)
    Next
End Sub

[jeremiah_j2k]

Hello Admin... thanks for the suggested forum topic... I already have a vba code to send out worksheet via email on my tracker.. but thank you for the reply

Hi

Welcome to forum !

See if this helps you.

http://www.excelfox.com/forum/f2/exc...ail+attachment

[jeremiah_j2k]

Hello snb,

Thanks for your immediate response. Can you help me modify my code? As of now, it can split the data from rows into multiple worksheet but is it possible for that new worksheet to be sent via email?

For i = 1 To WorkRng.Rows.Count Step SplitRow
resizeCount = SplitRow
If (WorkRng.Rows.Count - xRow.Row + 9) < SplitRow Then resizeCount = WorkRng.Rows.Count - xRow.Row + 1
xRow.Resize(resizeCount).Copy
Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
Application.ActiveSheet.Range("A1").PasteSpecial
Set xRow = xRow.Offset(SplitRow)

<need an email code on this area that will send the worksheet added by this line (Application.Worksheets.Add) to the desired recipient via email>

 Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

[snb]

I don't see any relationship to the suggestion I made in #3

[jeremiah_j2k]

Hello snb,

sorry for the confusion. I used the code that you have provided and it worked perfectly as needed. However, I need to send out the worksheet that contains the data from the rows that were splited by the vba command but I'm not sure if its possible to add a button to the worksheet to send it via email.

[snb]

Where can VBA find the emailadresses the workbooks have to be sent to ?

[jeremiah_j2k]

we can put a dropdown list to a cell reference and declare its position in the VBA code... for example


With OutMail
.To = Range("I5")