Results 1 to 9 of 9

Thread: Need help to modify the VBA code

  1. #1
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0

    Need help to modify the VBA code

    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.

    Code:
    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
    Last edited by Admin; 10-20-2014 at 01:23 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Welcome to forum !

    See if this helps you.

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

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Code:
    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

  4. #4
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    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

    Quote Originally Posted by Admin View Post
    Hi

    Welcome to forum !

    See if this helps you.

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

  5. #5
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    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?


    Code:
    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
    Last edited by jeremiah_j2k; 10-22-2014 at 12:13 PM.

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    I don't see any relationship to the suggestion I made in #3

  7. #7
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    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.

  8. #8
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Where can VBA find the emailadresses the workbooks have to be sent to ?

  9. #9
    Junior Member
    Join Date
    Oct 2014
    Posts
    26
    Rep Power
    0
    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")

Similar Threads

  1. Try to modify printer defaults from access-vba
    By relui in forum Access Help
    Replies: 0
    Last Post: 10-24-2013, 02:56 PM
  2. Replies: 1
    Last Post: 08-23-2013, 06:33 PM
  3. Replies: 15
    Last Post: 08-23-2013, 12:03 PM
  4. Shorten VBA Code By Removing Redundant Superfluous Code
    By paul_pearson in forum Excel Help
    Replies: 2
    Last Post: 08-15-2013, 09:09 PM
  5. Council to modify code
    By PcMax in forum Excel Help
    Replies: 4
    Last Post: 02-05-2012, 11:03 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •