Macro to cut and paste to master file

[njenkins]

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.

 

[Yongle]

Try this method ..


This code goes in Master workbook

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

 

[Yongle]

Just spotted a tiny error

Remove the .

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

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

 

[njenkins]

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]

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 ..

    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