Eu sou absolutamente novo no vba. Quero copiar certos valores nas células de duas guias ("Ações", "Obrigações") em uma terceira ("ZSM") com o seguinte código.

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

Isso funciona bem até que eu queira modificar o código de uma maneira que meu código vba copie os valores das minhas fórmulas nas duas guias ("Ações, títulos") na minha terceira guia ("ZSM"). Eu realmente só quero que o valor que a fórmula devolva de fórmulas como "= J5 * K24" seja copiado. Isso não funcionou, embora eu tenha modificado o código da seguinte maneira (alterações marcadas com "### 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

Alguma ideia? Li um pouco sobre o método PasteSpecial, mas não o pude aplicar ao meu problema nesta fase.

Obrigado pela sua ajuda! Eu realmente aprecio seu apoio.

EDIT: Screenshots conforme solicitado Atenção: A coluna ISIN deve estar lá apenas uma vez na guia "ZSM". Deve ser possível estender colunas e linhas.