Paste Values instead of formulas with PasteSpecial - VBANewbie

Question

I am absolutely new to vba. I want to copy certain values in cells from two tabs ("Equities", "Bonds") into a third one ("ZSM") with the following code.

Sub AllesAufEinmal()

    Call Spalten
    Call Wertpapiere
    Call Daten

End Sub

Sub Spalten()
'
' Spalten Macro
'
    Sheets("Equities").Select
    Range("A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("A4").Select
    ActiveSheet.Paste
    Range("A4").Select
    Sheets("Bonds").Select
    Range("B4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ZSM").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveSheet.Paste
    Range("A4").Select
End Sub

Sub Wertpapiere()
'
' Wertpapiere Macro
'

'
    Sheets("Equities").Select
    Range("A5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("A5").Select
    ActiveSheet.Paste
    Range("A5").Select
    Sheets("Bonds").Select
    Range("A5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
    Range("A5").Select
End Sub

Sub Daten()
'
' Daten Macro
'

'
    Sheets("Equities").Select
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("B5").Select
    ActiveSheet.Paste
    Sheets("Bonds").Select
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("B5").Select
    Selection.End(xlDown).Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(1, 1).Select
    ActiveSheet.Paste


End Sub

That works fine until I wanted to modify the code in a way so that my vba code copies the values from my formulas in the two tabs ("Equities, Bonds") into my third tab ("ZSM"). I really only want the value the formula gives back from formulas like "= J5*K24" to be copied. That did not work even though I modified the code the following way (changes marked with "###here"):

Sub AllesAufEinmal()

    Call Spalten
    Call Wertpapiere
    Call Daten

End Sub

Sub Spalten()
'
' Spalten Macro
'
    Sheets("Equities").Select
    Range("A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("A4").Select
    ActiveSheet.Paste
    Range("A4").Select
    Sheets("Bonds").Select
    Range("B4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ZSM").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveSheet.Paste
    Range("A4").Select
End Sub

Sub Wertpapiere()
'
' Wertpapiere Macro
'

'
    Sheets("Equities").Select
    Range("A5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("A5").Select
    ActiveSheet.Paste
    Range("A5").Select
    Sheets("Bonds").Select
    Range("A5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
    Range("A5").Select
End Sub

Sub Daten()
'
' Daten Macro
'

'
    Sheets("Equities").Select
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("B5").Select
    ActiveSheet.PasteSpecial                ###here
    Sheets("Bonds").Select
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("ZSM").Select
    Range("B5").Select
    Selection.End(xlDown).Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(1, 1).Select
    ActiveSheet.PasteSpecial           ###here


End Sub

Any ideas? I read a bit about the PasteSpecial Methode but could not apply it to my problem at this stage.

Thank your for your help! I would really appreciate your support.

EDIT: Screenshots as requested Attention: The column ISIN should only be there once in tab "ZSM". It should be possible to extend columns and rows.

[`Range.PasteSpecial`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.PasteSpecial) and [`Worksheet.PasteSpecial`](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.PasteSpecial) are *completely* different actions, and cannot be swapped as easily as `Range.Paste` and `Worksheet.Paste` can. — Chronocidal, Sep 19 '18 at 11:40

score 5 · Accepted Answer · 2018-09-19T15:23:47.303

5

Using the direct value transfer methods described in your last question, I've come up with this.

Each part of the transfer is labelled so you can split the individual routines apart as needed.

Option Explicit

Sub AllesAufEinmal()

    Dim tws As Worksheet

    Set tws = Worksheets("ZSM")

    Call Spalten(tws)
    'Call Wertpapiere(tws)
    'Call Daten(tws)

End Sub

Sub Spalten(zsm As Worksheet)
' Spalten Macro

    'headers, ISIN and data from from Equities
    With Worksheets("Equities")
        With .Range(.Cells(.Rows.Count, "A").End(xlUp), .Cells(4, .Columns.Count).End(xlToLeft))
            zsm.Cells(4, "A").Resize(.Rows.Count, .Columns.Count) = .Value
        End With
    End With

    'headers from Bonds
    With Worksheets("Bonds")
        With .Range(.Cells(4, "B"), .Cells(4, .Columns.Count).End(xlToLeft))
            zsm.Cells(4, zsm.Columns.Count).End(xlToLeft).Offset(0, 1).Resize(.Rows.Count, .Columns.Count) = .Value
        End With
    End With

    'ISIN from Bonds
    With Worksheets("Bonds")
        With .Range(.Cells(5, "A"), .Cells(.Rows.Count, "A").End(xlUp))
            zsm.Cells(zsm.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Value
        End With
    End With

    'data from Bonds
    With Worksheets("Bonds")
        With .Range(.Cells(.Rows.Count, "B").End(xlUp), .Cells(5, .Columns.Count).End(xlToLeft))
            zsm.Cells(zsm.Cells(zsm.Rows.Count, "B").End(xlUp).Row, _
                      zsm.Cells(5, zsm.Columns.Count).End(xlToLeft).Column). _
                Offset(1, 1).Resize(.Rows.Count, .Columns.Count) = .Value
        End With
    End With

End Sub

'Best practice' dictates that you should avoid Select and provide proper parent worksheet references. To this end, I've passed the target worksheet reference to each 'helper' sub procedure as a parameter.

edited Sep 19 '18 at 15:23

answered Sep 19 '18 at 08:56

It cannot be combined because I have other programmes runnig which forbid a combination. Unfortunately, that does not work. Not all rows get transfered and not all values get transferred...Any idea how I can show you what your code with my values does? – Peter Sep 19 '18 at 10:48
1

I don't often ask for screenshots but it would help to see a limited amount of data (particularly troublesome areas) and expected results. I honestly cannot determine what is wrong from your narrative of your problem nor your your code. – Sep 19 '18 at 14:07
yeah sure. How can I send you screenshots? :) – Peter Sep 19 '18 at 14:22
1

You can edit your question and upload them to be visible there. – Sep 19 '18 at 14:23
Done. Does that help? Thanks a lot for your time. I really appreciate it! – Peter Sep 19 '18 at 14:41
Thank you very much! You helped me a lot. I am also curious what you have exactly done. As I said I am new to vba and want to understand the logic behnd it. Can you explain the code a bit and why you chose the following approach to solve it @Jeeped ? – Peter Sep 19 '18 at 15:42
1

a) Avoid Select whenever possible. b) Avoid the clipboard whenever possible. c) Define a range by referencing two opposite corners, not by extending the current Selection. d) Looking from the bottom up and from the extreme right up and left is less prone to errors than looking down and to the right. – Sep 19 '18 at 15:53
Thanks a lot!! What do mean by clipboard? The recording?? Any good websites or tutorials I could use to pick vba up fast? :) – Peter Sep 19 '18 at 16:34
2

@Peter By "Avoid the clipboard" Jeeped means it's better to use `DestRange = SourceRange.Value` or `SourceRange.Copy Destination:=DestRange` to move the data directly, instead of `SourceRange.Copy: DestRange.Paste` to Copy it to the Clipboard and then Paste it – Chronocidal Sep 20 '18 at 09:28

J.Mapz · Answer 2 · 2018-09-19T09:24:09.600

3

You can try replacing those Activesheet.PasteSpecial as:

Selection.PasteSpecial Paste:=xlPasteValues

This will paste your selected range as just values.

edited Sep 19 '18 at 09:24

answered Sep 19 '18 at 08:18

J.Mapz

511
2
12

1

Have you tried `Selection.PasteSpecial Paste:=xlPasteValues` instead? – J.Mapz Sep 19 '18 at 09:24
@Peter *How* does it "not work"? Does it give an error message? Paste the wrong data? Paste data in the wrong place? Crash your computer? Summon Cthulhu? Have you tried stepping through with `F8` to see on which line it stops working as expected? – Chronocidal Sep 19 '18 at 11:46
I am sorry. It says "Run-time error '424' Object required". Any ideas? I google it myself but just cannot figure out a solution. – Peter Sep 19 '18 at 12:51
1

@Peter The sounds like your `Selection` is not valid for Pasting to. Fully Qualified ranges should be able to avoid this, e.g. `Sheets("ZSM").Range("B5").PasteSpecial Paste:=xlPasteValues` – Chronocidal Sep 20 '18 at 09:30

score 3 · Answer 3 · answered Sep 19 '18 at 08:21

3

You should use xlPasteValues. Example:

Range("B5").PasteSpecial xlPasteValues

If you prefer formulas you could use xlPasteFormulas.

I strongly advise to read this article on how to avoid using Select:

How to avoid using Select in Excel VBA

answered Sep 19 '18 at 08:21

Pawel Czyz

1,651
4
17
21

That does work, but only for my first "Select part" because the first paste-Part always starts at B5. The second paste part from my tab "Bonds" does not start at a certain cell because it depends on the size of the table from tab "Equities". Any ideas how I can format my formula after ActiveCell.Offset(1, 1).Select // ActiveSheet.PasteSpecial ###here – Peter Sep 19 '18 at 08:43
You should find the last row in a more dynamic way. Here is a great article on how you could do it: https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba – Pawel Czyz Sep 19 '18 at 08:55
Selection.End(xlDown).Select // Selection.End(xlToRight).Select // LasActiveCell.Offset(1, 1).Select // Range("???").PasteSpecial xlPasteValues // I read the article. I figured out how to find the last row in a dynamic way (see first code lines). But I do not know how to pass the last dynamic row/column as a range to my Methode "???". Any ideas? End Sub – Peter Sep 19 '18 at 09:19
You should hold it in a variable of type long. Something like: Dim lastRow as Long Please check Jeeped answer - he did all the work for you :) – Pawel Czyz Sep 19 '18 at 09:24

Paste Values instead of formulas with PasteSpecial - VBANewbie

3 Answers3

Linked