CODEWITHSUNDEEP
excelvbavariablescopypaste
Anthony L
Anthony L

Reputation: 43

VBA Copy Paste to Row/Column number in cells

I am trying to copy certain values from Book1 and paste to specific (changing) locations in Book2. Since this location is expected to change, I want to paste based on row/column numbers that I have in already existing cells. I am new to VBA and do not know how to loop this. Any thoughts are greatly appreciated, thanks!

I tried the below, but I am thinking of turning the hard-coded values into variables somehow

Windows("Book1.xlsm").Activate
    Range("G6:G10").Select
    Selection.Copy
    Windows("Book2.xlsx").Activate
    Range("FC8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Upvotes: 0

Views: 350

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Copy Values By Assignment

Option Explicit

Sub CopyValues()
    
    ' Source
    Const swbName As String = "Book1.xlsm"
    Const swsName As String = "Sheet1"
    Const srgAddress As String = "G6:G10"
    ' Destination
    Const dwbName As String = "Book2.xlsx"
    Const dwsName As String = "Sheet1"
    Const dfCellAddress As String = "FC8"
    
    ' Source
    Dim swb As Workbook: Set swb = Workbooks(swbName)
    ' if the source workbook is the workbook containing this code, instead, use:
    'Dim swb As Workbook: Set swb = ThisWorkbook
    Dim sws As Worksheet: Set sws = swb.Worksheets(swsName)
    Dim srg As Range: Set srg = sws.Range(srgAddress)
    
    ' Destination
    Dim dwb As Workbook: Set dwb = Workbooks(dwbName)
    Dim dws As Worksheet: Set dws = dwb.Worksheets(dwsName)
    Dim dCell As Range: Set dCell = dws.Range(dfCellAddress)
    ' If the destination is to be dynamic, i.e. the data should be appended
    ' after the last row containing data, instead, use something like this:
'    With dws.Range(dfCellAddress)
'        Set dCell = dws.Cells(dws.Rows.Count, .Column).End(xlUp) ' last cell
'        If dCell.Row < .Row Then
'            Set dCell = .Cells ' use the given cell
'        Else
'            Set dCell = dfCell.Offset(1) ' use the cell below the last cell
'        End If
'    End With
    Dim drg As Range: Set drg = dCell.Resize(srg.Rows.Count)
    ' The previous line is short for the following...
    'Set drg = dCell.Resize(srg.Rows.Count, srg.Columns.Count)
    ' ... and can be used since only one column is being copied.
    
    ' Copy.
    drg.Value = srg.Value
    
    ' Inform.
    MsgBox "Values copied.", vbInformation
    
End Sub

Upvotes: 1

Related Questions