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]

Welcome to the board. This is untested so suggest making a backup of your file before adding this code and running, however, try:

Sub Main()

    Dim x   As Long
    Dim a   As Variant
   
    a = Array(Array(100, 0), Array(0, 100), Array(0, 0))
   
    Application.ScreenUpdating = False
   
    For x = LBound(a) To UBound(a)
        Gen_Results a(x), Sheets("Sheet2").Cells(x + 1, 4).Resize(10, 3)
    Next x
   
    My_Output Sheets("Sheet2").Cells(1, 4).Resize(10, 3)
               
    Application.ScreenUpdating = True
   
    Erase a
   
End Sub

Private Sub Gen_Results(ByRef inputs As Variant, ByRef rng As Range)

    With Sheets("Sheet1")
        .Cells(1, 1).Resize(2).Value = Application.Transpose(inputs)
    End With
   
    With rng
        .Value = .Offset(, -3).Value
        .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    End With
   
End Sub

Private Sub My_Output(ByRef rng As Range)

    rng.Copy
    With Sheets("Sheet3").Cells(1, 1).Resize(rng.Rows.Count, rng.Columns.Count)
        .PasteSpecial xlPasteValuesAndNumberFormats
        .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    End With

    Application.CutCopyMode = False
   
End Sub

 

[JLGWhiz]

It looks to me like the copy and paste of range A1:C10 on sheet 2 will over write nine rows of previous posting on each iteration when pasted into D1:D3.
But here is some code.

Sub t() 'Change the 't' to a name you like.
    With Sheets("Sheet1")
        .Range("A1") = 100: .Range("A2") = 0
    End With
    With Sheets("Sheet2")
        .Range("A1:C10").Copy
        .Range("D2").PasteSpecial xlPasteValuesAndNumberFormat, SkipBlanks:=True
    End With
    With Sheets("Sheet1")
        .Range("A1") = 0: .Range("A2") = 100
    End With
    With Sheets("sheet2")
        .Range("A1:C10").Copy
        .Range("D2").PasteSpecial xlPasteValuesAndNumberFormat, SkipBlanks:=True
    End With
    With Sheets("Sheet1")
        .Range("A1") = 0: .Range("A2") = 0
    End With
    With Sheets("Sheet2")
        .Range("A1:C10").Copy
        .Range("D3").PasteSpecial xlPasteValuesAndNumberFormat, SkipBlanks:=True
        .Range("D1:F10").Copy
        Sheets("Sheet3").Range("A1").PasteSpecial xlPasteValuesAndNumberFormat, SkipBlanks:=True
        Application.CutCopyMode = False
    End With
End Sub

 

[JackDanIce]

@JLGWhiz hey, each iteration is being put into D1, D2 then D3, your code copies to D2 for the first 3 interations.

I'm guessing the copy+paste, remove blanks is what avoids over-writing but that was my concern too, OP to confirm...

 

[Matt_314]

@JackDanIce and @JLGWhiz , wow! Thank you so much for your quick and thorough replies!
@JackDanIce, indeed the remove blanks is to avoid over-writing. I tried to figure out how to use your code, but couldn't fully understand it unfortunately ?

@JLGWhiz, thank you for the care you put into using my syntax, I appreciate it and was able to understand your code better.

In my first post, I had used mock-up values. I have adjusted the code to the actual ranges and values (I also changed xlPasteValuesAndNumberFormat to xlPasteValuesAndNumberFormats) - but am getting the error: "Subscript out of range (Error 9)". I looked into the documentation at Subscript out of range (Error 9) but cannot figure what applies to my case and thus how I can fix this.

Do you have any idea what could be causing this error and how to fix it?

Once again many thanks for any hint - I'm attaching a screenshot just in case.

Sub test()
    With Sheets("Sheet1")
        .Range("K42") = 0: .Range("K43") = 1000 'wind scenario
    End With
    With Sheets("Sheet15")
        .Range("C7:J62").Copy
        .Range("O7").PasteSpecial xlPasteValuesAndNumberFormats, SkipBlanks:=True
    End With
    With Sheets("Sheet1")
        .Range("K42") = 0: .Range("K43") = 0 'PV scenario
    End With
    With Sheets("Sheet15")
        .Range("C7:J62").Copy
        .Range("O8").PasteSpecial xlPasteValuesAndNumberFormats, SkipBlanks:=True
    End With
    With Sheets("Sheet1")
        .Range("K42") = 1000: .Range("K43") = 0 'grid scenario
    End With
    With Sheets("Sheet15")
        .Range("C7:J62").Copy
        .Range("O9").PasteSpecial xlPasteValuesAndNumberFormats, SkipBlanks:=True

        'rest of instructions

        .Range("O7:V64").Copy
        Sheets("Sheet16").Range("C5").PasteSpecial xlPasteValuesAndNumberFormats, SkipBlanks:=True
    End With
    With Sheets("Sheet15")
        .Range("D2").Copy
        Sheets("Sheet16").PasteSpecial xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False
    End With
End Sub

Thanks again and cheers,
Matt

 

[JackDanIce]

Replace all of the code with below, where I've added comments and changed some of the code to hopefully make readability easier and to amend as needed:

Option Explicit

Sub Main()

    'Variables
    Dim x   As Long
    Dim a   As Variant: a = Array(Array(1000, 0), Array(0, 1000), Array(0, 0))
   
    'Turn screenupdating off
    Application.ScreenUpdating = False
   
    'Loop over array containing A1 and A2 values and put into Sheet1
    For x = LBound(a) To UBound(a)
        Gen_Results a(x), x + 7
    Next x
   
    'Final output
    With Sheets("Sheet15")
        .Range("O7:V64").Copy
        Sheets("Sheet16").Range("C5").PasteSpecial xlPasteValuesAndNumberFormats, skipblanks:=True
        .Range("D2").Copy
        Sheets("Sheet16").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats, skipblanks:=True
    End With
   
    'Reset settings
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
   
    'Tidy up
    Erase a
   
End Sub

Private Sub Gen_Results(ByRef inputs As Variant, ByRef xRow As Long)
'Updates Sheet1 with values

    With Sheets("Sheet1")
        .Range("K42:K43").Value = Application.Transpose(inputs)
    End With
       
    With Sheets("Sheet15")
        .Range("C7:J62").Copy
        .Range("O" & xRow).PasteSpecial xlPasteValuesAndNumberFormats, skipblanks:=True
    End With
   
End Sub

 

[Matt_314]

@JackDanIce, thank you so much! It is clearer now.

I'm getting the same error message - am attaching a screenshot. Any idea what's causing it and how I may sole it?

Thank you
Matt

 

[JLGWhiz]

It is telling you it cannot find a sheet named "Sheet15". I cannot see the tab name but I see that you do have a sheet code name Sheet15, is it possible that the Tab name is different?

 

[JackDanIce]

If you expand the project window, you can just about make out Sheet15("xxxxxxx") (your picture doesn't show what's inside the brackets)

One worksheet object is Sheet15 which is the object name and "xxxxxxx" is what is shown to the user as name of the sheet in the Excel window.

E.g. Person1, Person2, Person3 can be used to identify 3 people, but they all have their own unique distinct names.
You can then either identify them by index name e.g. Person1, Person2, Person3 or their actual name, e.g. Andy, Bill, Colin. Andy is an attribute of Person1, Bill of Person2, etc.

Your code needs to either use the "xxxxxxx" part (the person's name), e.g. Sheets("xxxxxxx").Range("A1") or the object name, e.g. Sheet15.Range("A1") (person's index name or if you don't know their name - this is useful when you know your User is going to be changing the sheet name but you need to identify THAT specific sheet, regardless of the tab name).

Check your syntax and adjust the code to resolve the error.

 

[Matt_314]

@JackDanIce and @JLGWhiz , thank you so much for your time and for putting in extra effort to explain certain nuances. I got both codes to work and am including them here for future reference. You got me motivated to learn more!

I adjusted the order of the variables, in order for the calculated values to be placed in the right cells in the "Diagrams" Sheet.

The codes are:

Option Explicit

Sub GenerateDiagrams()

    'Variables
    Dim x   As Long
    Dim a   As Variant: a = Array(Array(0, 1000), Array(0, 0), Array(1000, 0))
   
    'Turn screenupdating off
    Application.ScreenUpdating = False
   
    'Loop over array containing A1 and A2 values and put into Sheet1
    For x = LBound(a) To UBound(a)
        Gen_Results a(x), x + 7
    Next x
   
    'Final output
    With Sheets("RESULTS")
        .Range("O7:V64").Copy
        Sheets("Diagrams").Range("C5").PasteSpecial xlPasteValuesAndNumberFormats, skipblanks:=True
    End With
    With Sheets("RESULTS")
        .Range("D2").Copy
        Sheets("Diagrams").Range("B1").PasteSpecial xlPasteValuesAndNumberFormats
    End With
   
    'Reset settings
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
   
    'Tidy up
    Erase a
   
End Sub

Private Sub Gen_Results(ByRef inputs As Variant, ByRef xRow As Long)
'Updates Sheet1 with values

    With Sheets("User Input pane")
        .Range("K42:K43").Value = Application.Transpose(inputs)
    End With
       
    With Sheets("RESULTS")
        .Range("C7:J62").Copy
        .Range("O" & xRow).PasteSpecial xlPasteValuesAndNumberFormats, skipblanks:=True
    End With
   
End Sub

and

Sub GenerateDiagrams()
    With Sheets("User Input pane")
        .Range("K42") = 0: .Range("K43") = 1000 'wind scenario
    End With
    With Sheets("RESULTS")
        .Range("C7:J62").Copy
        .Range("O7").PasteSpecial xlPasteValuesAndNumberFormats, SkipBlanks:=True
    End With
    With Sheets("User Input pane")
        .Range("K42") = 0: .Range("K43") = 0 'PV scenario
    End With
    With Sheets("RESULTS")
        .Range("C7:J62").Copy
        .Range("O8").PasteSpecial xlPasteValuesAndNumberFormats, SkipBlanks:=True
    End With
    With Sheets("User Input pane")
        .Range("K42") = 1000: .Range("K43") = 0 'grid scenario
    End With
    With Sheets("RESULTS")
        .Range("C7:J62").Copy
        .Range("O9").PasteSpecial xlPasteValuesAndNumberFormats, SkipBlanks:=True

        'rest of instructions

        .Range("O7:V64").Copy
        Sheets("Diagrams").Range("C5").PasteSpecial xlPasteValuesAndNumberFormats, SkipBlanks:=True
    End With
    With Sheets("RESULTS")
        .Range("D2").Copy
    End With
    With Sheets("Diagrams")
        .Range("B1").PasteSpecial xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False
    End With
End Sub

Thanks again and please PM me if there's anything I can do in return!