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 · Accepted 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

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  gt   2 And R  lt  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

[excel] VBA paste range

Examples related to excel

Examples related to vba

Examples related to range

Examples related to paste