Thread: Copy and Paste by VBA

Hi, I need of a Macro to copy and past some cells on excel, i mean:
I have 2 sheets: in the first one i have some data/numbers that I updat every week, so I have for every week different numbers. I have to copy these numbers on the second sheet, but i don't want to paste the numbers on the cells of the previous week. I tried to record a macro using excel and i got:

Sub PROVA1()
' PROVA1 Macro
    Range("P2:P10").Select
    Selection.Copy
    Sheets("Historical Trend").Select
    Application.Run "BLPLinkReset"
    Range("M2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  
  Sheets("Summary&Data").Select
    Application.Run "BLPLinkReset"
End Sub

The problem is that using this macro I override the cells everyweek: i need that every week the new numbers have to be paste on the next column in the second sheet.

Example
Sheet 1
Column 1 (09/19/11)
1.2
2.9
3.4
4.5
5.6

Sheet 2
Column 1 (09/05/11) Column 2 (09/12/11) Column 3 (09/19/11)
2.5 ||| 2.4
2.6 ||| 3.5
7.4 ||| 2.8
8.9 ||| 5.2
5.6 ||| 9

As you can see I want to copy and past the value for the updated numbers. If I use the macro i've created i will every week write on the same interval.
The macro should recognizes the blank cells (without any number) which follows written cells with number (in the example i want to paste the value on the column 3, and the following week on the column 4. etc...).

Then I want also to copy from the second sheet the last 4 columns written with numbers on the first sheet: it's a dynamic copy and past because i want that the range of the copy and past would follow the updated cells.

Thanks in advance.

Damiele

Welcome to the forum dumbago.

Please take a few moments to read the forum rules. Then add Code tags to your code sample.


Regards

Originally Posted by watersev

please check attachment, run code "test"

Hi it doesn't work, because the macro copies only the last column but it is not added to the sheet 2.

The macro you gave me yesterday works alone, but i wasn't able to use that macro in my file.

If you have time, please help me.

This is the file: as you can see i wanna thgat every week the sheet2 (historical trend) grows for one column (the yellow one) that is taken from the sheet 1 (Summary&Data). I also want that in sheet 1 (Summary&Data) there should be a summary of the last 4 weeks of data (the grey colored) plus the actual week, the yellow one that is linked to other cells in the sheet. The steps are: I have every week different numbers linked to other cells in sheet1, in the yellow column. I have to copy this column in sheet2 (historical trend) in order to create a historical database: so i need o update every week the sheet2 with the number in the yellow column in sheet1. Then i have to copy and paste the last 4 columns (grey), except for the last i have copied before (the yellow), in the table in the sheet1. That is. May you help me? The firs macro you gave me worked so well, but the problem was that i wasn't able to use it in my file (that i cannnot give to you because is an important work's file) that i have replicated only for the part i need to upload.

One thing more: i wuold like to update also a graph for the Histocal Trend sheet which change automatically the range (every week the number of the cell in the graph grows). But this is the last and less important problem. The more important is the previous.

thanks again