Results 1 to 2 of 2

Thread: Copy and Paste in New File to Send in E-Mail Macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2018
    Posts
    70
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Cool Copy and Paste in New File to Send in E-Mail Macro

    I wanted to create a macro that the user can enter into a message box the day of the month. The macro would continue to run that would copy the corresponding week from a series of columns and paste in a new excel file that would be attached to an e-mail and mailed. Can this be done??? Thank you.

    TooZippy

  2. #2
    Board Regular
    Join Date
    Dec 2018
    Posts
    70
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Copy and Paste in New File to Send in E-Mail Macro

    Quote Originally Posted by TooZippy View Post
    I wanted to create a macro that the user can enter into a message box the day of the month. The macro would continue to run that would copy the corresponding week from a series of columns and paste in a new excel file that would be attached to an e-mail and mailed. Can this be done??? Thank you.

    TooZippy
    I was able to get this macro that would partially do what I want. However, I want it to put a header in the new file that I am pasting the data into but the macro is in the source file. Can I run the macro in the source file to put the header in the destination file? Here are the macro's..

    Code:
    Private Sub CommandButton1_Click()
    
    Dim newWB As Workbook, currentWB AsWorkbook
    Dim newS As Worksheet, currentS AsWorksheet
    
    'Copy the data you need
    Set currentWB = ThisWorkbook
    Set currentS = currentWB.Sheets("Sheet1")
    currentS .Range("A:M").Select
    Selection.Copy
    
    'Create a new file that will receive thedata
    Set newWB = Workbooks.Add
    With newWB
    Set newS = newWB.Sheets("Sheet1")
    newS.Range("A1").PasteSpecial Paste:=xlPasteValues,Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    'Save in CSV
    Application.DisplayAlerts = False
    .SaveAs Filename:="C:\Temporary.csv", FileFormat:=xlCSV
    Application.DisplayAlerts = True
    End With
    End Sub
    
    SubAddHeaders()
    
    Worksheets("Sheet1").Activate
    Dim headers() As Variant
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim i As Integer
    
    'Inserting a Row at at Row 1
    Worksheets("Sheet1").Range("A1").EntireRow.Insert
    
    Application.ScreenUpdating = False 'turnthis off for the macro to run a little faster
    
    headers() = Array(“ “, “ “, "1st","2nd", "3rd", "4th", "5th", "6th","7th", "8th", "9th",  "10th", "11th", "12th", ”13th”, ”14th”, ”15th”, ”16th”,”17th”, ”18th”, ”19th”, ”20th”, ”21st”, ”22nd”, ”23rd”, ”24th”, ”25th”, ”26th”,”27th”, ”28th”, ”29th”, ”30th”, ”31st”)
    With Sheet1
    .Rows(1).Value = "" 'Thiswill clear out row 1
    For i = LBound(headers()) To UBound(headers())
    .Cells(1, 1 + i).Value = headers(i)
    Next i
    .Rows(1).Font.Bold = True
    End With
    
    Application.ScreenUpdating = True 'turnit back on
    
    End Sub
    Thank you,
    TooZippy

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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