VBA: compose a set of copy/paste special instructions

[Matt_314]

Hello all,

this is my first attempt at VBA, I have some basic experience with Matlab and little programming experience, so please bear with me if I fail to use the proper terms.
I want to automate a series of operations on several sheets in the same workbook.

Can you tell me what my code should look like, or give me a hint on where to start?

The ...operations? commands?, which need to be executed are the following, in this order. I'm describing the operations I do by hand:

*start*

1. Set the values in Sheet 1:
   1. Cell A1=100
   2. Cell A2=0
2. In Sheet 2, copy the cells in the range A1:C10
3. In Sheet 2, paste special the values+number format and skip blanks to D1
   
   *end of first iteration*
   
   
4. Set the values in Sheet 1:
   1. Cell A1=0
   2. Cell A2=100
5. Repeat 2.
6. In Sheet 2, paste special the values+number format and skip blanks in D2
   
   *end of second iteration*
   
   
7. Set the values in Sheet 1:
   1. Cell A1=0
   2. Cell A2=0
8. Repeat 2.
9. In Sheet 2, paste special the values+number format and skip blanks in D3
   
   *end of third iteration*
   
   
10. In Sheet 2, copy the cells in the range D1:F10
11. In Sheet 3, paste special the values+number format and skip blanks in A1

*end*

I hope this is clear enough for someone to tell me if this is possible and what educational material I should be studying to learn to write this.


Cheers and many thanks,
Matt

 

[JackDanIce]

Hey awesome, you found your solution!

For your adjustment to the code I suggested, this is only part I'd change, just on a style/preference basis, though I don't think there's any beneficial gain:

    'Final output
    With Sheets("RESULTS")
        .Range("O7:V64").Copy
        Sheets("Diagrams").Range("C5:J62").PasteSpecial xlPasteValuesAndNumberFormats, skipblanks:=True
        .Range("D2").Copy
        Sheets("Diagrams").Range("B1").PasteSpecial xlPasteValuesAndNumberFormats
    End With