Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Unanswered: Copying values from one tab to another

    I'm sure this is covered somewhere in the forum, but anyway...I would like to be able to take several values in a column, and paste them into a row on a different tab within the same worksheet (and put the code behind a command button). For instance:

    tab 1
    Column A
    row1 abcdef
    row2 new
    row3 35.46
    row4 $500.00

    upon clicking the button...

    a. find the next blank row on tab 2
    b. start at Column A
    c. paste values from tab 1 as
    ||ColumnA|ColumnB|ColumnC|ColumnD
    row?|abcdef|new|35.46|$500.00

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Copying Data

    Hi

    This is one way, depending on what you readly want to do.

    Code:
    Sub CopyDataToSheet2()
        Dim Thisrange As Range
        Dim cel As Range
        Dim vRow As Variant
        Dim j As Integer
        Dim iRow As Integer
        
        Set Thisrange = Selection
        
        If Thisrange.Columns.Count > 1 Then
            MsgBox "Only columns selection is permitted."
            Exit Sub
        End If
        
        With Sheet2
            .Select
            .Cells(65536, 1).Select
            Selection.End(xlUp).Select
            iRow = ActiveCell.Row + 1
            For Each cel In Thisrange
                j = j + 1
                .Cells(iRow, j) = cel
            Next cel
        End With
    End Sub
    This code should be pasted in a separate Code Module (not the sheet or ThisWorkbook).

    The sub should be called from the button click of a button on Sheet1 (or wherever).

    It also requires for the data to be copied to be selected (in the column only).

    HTH

    MTB

  3. #3
    Join Date
    Feb 2006
    Posts
    113
    Or like this, modify to suit
    Code:
    Sub test()
        Sheet1.Range("A1:A4").Copy
        Sheet2.Range("A65536").End(xlUp).Offset(1).PasteSpecial Transpose:=True
    End Sub
    HTH
    Fazza

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •