Macro to cut and paste to master file

njenkins

New Member
Joined
Nov 24, 2012
Messages
23
i am sent a spreadsheet each day of performance. I have a recorded macro that deletes all the data not relevant to me which leaves me with 5 rows/10 columns of data. The columns are constant each day. I would like the macro to proceed in copying that days data onto a separate master spreadsheet of performance.
So each day I am emailed the spreadsheet, I can look at the data and then run the macro which will copy the relevant bit onto a master spreadsheet on my computer. The master spread sheet will then be a resource of past performance with new rows added each day by the macro.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,309
Office Version
365
Platform
Windows
Try this method ..


This code goes in Master workbook
Code:
Sub CopyData()
    Dim cel As Range, rng As Range

   [COLOR=#ff0000] Insert YOUR code to delete unwanted stuff here and ensure that latest day's sheet is active after the data is deleted[/COLOR]

[COLOR=#006400]'copy from where ?[/COLOR]
    Set rng = ActiveSheet.Range("A1").Resize(5, 10)    [I][COLOR=#ff0000]' amend A1 to the first cell of copied range (or specify actual range)[/COLOR][/I]

[COLOR=#006400]'paste to which cell ?[/COLOR]
    Set cel = ThisWorkbook.Sheets("[COLOR=#ff0000]Master[/COLOR]").Range("A" & .Rows.Count).End(xlUp).Offset(1)

[COLOR=#006400]'do the copy and paste[/COLOR]
    rng.Copy cel

End Sub
The above code will copy data from A1:J5 in the active sheet to the next empty cell in column A in sheet named "Master" in the workbook containing the code
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,309
Office Version
365
Platform
Windows
Just spotted a tiny error

Remove the .
Code:
Set cel = ThisWorkbook.Sheets("Master").Range("A" & [SIZE=4][COLOR=#ff0000].[/COLOR][/SIZE]Rows.Count).End(xlUp).Offset(1)

Code:
Set cel = ThisWorkbook.Sheets("Master").Range("A" & Rows.Count).End(xlUp).Offset(1)
 

njenkins

New Member
Joined
Nov 24, 2012
Messages
23
thank you
can i paste it in a separate workbook - as i will be running this on separate workbooks sent to me daily and id just like to extract my data to a master workbook
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,309
Office Version
365
Platform
Windows
can i paste it in a separate workbook - as i will be running this on separate workbooks sent to me daily and id just like to extract my data to a master workbook

I have a recorded macro that deletes all the data not relevant to me which leaves me with 5 rows/10 columns of data.

Modify your recorded macro to open the master workbook and do what you need, something like this ..

Code:
    Dim cel As Range, rng As Range, wb As Workbook

    [COLOR=#ff0000]'YOUR current code goes here
    'ENSURE that sheet containing data to be copied is now active[/COLOR]

[COLOR=#006400]'copy from where ?[/COLOR]
    Set rng = ActiveSheet.Range("A1").Resize(5, 10)    [COLOR=#006400]'amend to match your data[/COLOR]

[COLOR=#006400]'open master workbook[/COLOR]
    Set wb = Workbooks.Open("C:\Full\Path\MasterFileNmae.xlsx")    [COLOR=#006400]'amend to correct path and file name[/COLOR]

[COLOR=#006400]'paste to which cell ?[/COLOR]
    Set cel = wb.Sheets("Name of Sheet").Range("A" & .Rows.Count).End(xlUp).Offset(1)    [COLOR=#006400]'amend to match your data[/COLOR]

[COLOR=#006400]'do the copy and paste[/COLOR]
    rng.Copy cel

[COLOR=#006400]'save and close master workbook[/COLOR]
    wb.Save
    wb.Close False
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,459
Messages
5,414,647
Members
403,540
Latest member
mmorejon1215

This Week's Hot Topics

Top