Thread: Copy and Paste to location based on cell value

Hello,

I'm trying to copy a range of data from one location and paste it to another location, with the paste location based on a cell reference. For instance, in the attached workbook I've input "a" into cell C4 on the "Control" sheet. What I need is a macro that will copy and paste the data in cells C6:C11 on the "Control" sheet below the corresponding "a" in the "Output" sheet.

Thanks in advance for the help!

Hello CBron12,

Welcome tot he Forum!

I have added a button and attached the macro below to it. This has all been added to the attached worksheet.

Sub CopyAndPaste() 

  Dim DstCol As String
  Dim DstRng As Range
  Dim SrcRng As Range
  
    Set SrcRng = Worksheets("Control").Range("C6:C11")
    
    DstCol = Worksheets("Control").Range("C4").Value
    
    Set DstRng = Worksheets("Output").Cells(4, DstCol).Offset(0, 1)

    SrcRng.Copy
    DstRng.PasteSpecial Paste:=xlPasteAll
    
    Application.CutCopyMode = False
    
End Sub

Hello CBron12,

Here is the new macro code. I haved this to the attached workbook.

Sub CopyAndPaste()

  Dim DstCol As Variant
  Dim DstRng As Range
  Dim SrcRng As Range
  Dim Symbol As String
  
    Set SrcRng = Worksheets("Control").Range("C6:C11")
    
    Symbol = Worksheets("Control").Range("C4").Value
    
    On Error Resume Next
      DstCol = WorksheetFunction.Match(Symbol, Worksheets("Output").Range("3:3"), 0)
      If Err <> 0 Then
         MsgBox "Stock symbol '" & Symbol & " not found."
         Exit Sub
      End If
    On Error GoTo 0
    
    Set DstRng = Worksheets("Output").Cells(4, DstCol).Offset(0, 1)

    SrcRng.Copy
    DstRng.PasteSpecial Paste:=xlPasteAll
    
    Application.CutCopyMode = False
    
End Sub

Hello Cbron12,

I just noticed that I didn't remove the Offset statement. Here is the corrected code and workbook.

Sub CopyAndPaste()

  Dim DstCol As Variant
  Dim DstRng As Range
  Dim SrcRng As Range
  Dim Symbol As String
  
    Set SrcRng = Worksheets("Control").Range("C6:C11")
    
    Symbol = Worksheets("Control").Range("C4").Value
    
    On Error Resume Next
      DstCol = WorksheetFunction.Match(Symbol, Worksheets("Output").Range("3:3"), 0)
      If Err <> 0 Then
         MsgBox "Stock symbol '" & Symbol & " not found."
         Exit Sub
      End If
    On Error GoTo 0
    
    Set DstRng = Worksheets("Output").Cells(4, DstCol)

    SrcRng.Copy
    DstRng.PasteSpecial Paste:=xlPasteAll
    
    Application.CutCopyMode = False
    
End Sub