VBA paste range

Question

I have simple goal of copying range and pasting it into another spreadsheet  The following code below gives copies  but does not paste    Sub Normalize        Dim Ticker As Range     Sheets  Sheet1   Activate     Set Ticker   Range Cells 2  1   Cells 65  1       Ticker Copy      Sheets  Sheet2   Select     Cells 1  1  Activate     Ticker PasteSpecial xlPasteAll  End Sub   Any suggestions

User · Answer

This is what I came up to when trying to copy-paste excel ranges with it's sizes and cell groups. It might be a little too specific for my problem but...:

'** 'Copies a table from one place to another 'TargetRange: where to put the new LayoutTable 'typee: If it is an Instalation Layout table(1) or Package Layout table(2) '**

Sub CopyLayout(TargetRange As Range, typee As Integer)
    Application.ScreenUpdating = False
        Dim ncolumn As Integer
        Dim nrow As Integer

        SheetLayout.Activate
    If (typee = 1) Then 'is installation
        Range("installationlayout").Copy Destination:=TargetRange '@SHEET2 TEM DE PASSAR A SER A SHEET DO PROJECT PLAN!@@@@@
    ElseIf (typee = 2) Then 'is package
        Range("PackageLayout").Copy Destination:=TargetRange '@SHEET2 TEM DE PASSAR A SER A SHEET DO PROJECT PLAN!@@@@@
    End If

    Sheet2.Select 'SHEET2 TEM DE PASSAR A SER A SHEET DO PROJECT PLAN!@@@@@

    If typee = 1 Then
       nrow = SheetLayout.Range("installationlayout").Rows.Count
       ncolumn = SheetLayout.Range("installationlayout").Columns.Count

       Call RowHeightCorrector(SheetLayout.Range("installationlayout"), TargetRange.CurrentRegion, typee, nrow, ncolumn)
    ElseIf typee = 2 Then
       nrow = SheetLayout.Range("PackageLayout").Rows.Count
       ncolumn = SheetLayout.Range("PackageLayout").Columns.Count
       Call RowHeightCorrector(SheetLayout.Range("PackageLayout"), TargetRange.CurrentRegion, typee, nrow, ncolumn)
    End If
    Range("A1").Select 'Deselect the created table

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

'** 'Receives the Pasted Table Range and rearranjes it's properties 'accordingly to the original CopiedTable 'typee: If it is an Instalation Layout table(1) or Package Layout table(2) '**

Function RowHeightCorrector(CopiedTable As Range, PastedTable As Range, typee As Integer, RowCount As Integer, ColumnCount As Integer)
    Dim R As Long, C As Long

    For R = 1 To RowCount
        PastedTable.Rows(R).RowHeight = CopiedTable.CurrentRegion.Rows(R).RowHeight
        If R >= 2 And R < RowCount Then
            PastedTable.Rows(R).Group 'Main group of the table
        End If
        If R = 2 Then
            PastedTable.Rows(R).Group 'both type of tables have a grouped section at relative position "2" of Rows
        ElseIf (R = 4 And typee = 1) Then
            PastedTable.Rows(R).Group 'If it is an installation materials table, it has two grouped sections...
        End If
    Next R

    For C = 1 To ColumnCount
        PastedTable.Columns(C).ColumnWidth = CopiedTable.CurrentRegion.Columns(C).ColumnWidth
    Next C
End Function



Sub test ()
    Call CopyLayout(Sheet2.Range("A18"), 2)
end sub

User · Answer

To literally fix your example you would use this    Sub Normalize         Dim Ticker As Range     Sheets  Sheet1   Activate     Set Ticker   Range Cells 2  1   Cells 65  1       Ticker Copy      Sheets  Sheet2   Select     Cells 1  1  PasteSpecial xlPasteAll    End Sub   To Make slight improvments on it would be to get rid of the Select and Activates    Sub Normalize       With Sheets  Sheet1            Range  Cells 2  1    Cells 65  1   Copy Sheets  Sheet2   Cells 1  1      End With End Sub   but using the clipboard takes time and resources so the best way would be to avoid a copy and paste and just set the values equal to what you want   Sub Normalize   Dim CopyFrom As Range  Set CopyFrom   Sheets  Sheet1   Range  A2    A65   Sheets  Sheet2   Range  A1   Resize CopyFrom Rows Count  Value   CopyFrom Value  End Sub   To define the CopyFrom you can use anything you want to define the range  You could use Range  A2 A65    Range  A2   A65    Range  A2    A65   all would be valid entries  also if the A2 A65 Will never change the code could be further simplified to    Sub Normalize    Sheets  Sheet2   Range  A1 A65   Value   Sheets  Sheet1   Range  A2 A66   Value  End Sub   I added the Copy from range  and the Resize property to make it slightly more dynamic in case you had other ranges you wanted to use in the future

User · Answer

I would try   Sheets  Sheet1   Activate Set Ticker   Range Cells 2  1   Cells 65  1   Ticker Copy  Worksheets  Sheet2   Range  A1   Offset 0 0  Cells Select Worksheets  Sheet2   paste

[excel] VBA paste range

Examples related to excel

Examples related to vba

Examples related to range

Examples related to paste