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

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
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
 

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
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:
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][COLOR=#000000]Private Sub CommandButton1_Click()[/COLOR][/FONT]

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

Forum statistics

Threads
1,082,586
Messages
5,366,476
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top