Results 1 to 7 of 7

Thread: Copy paste non consecutive cells into a different sheet

  1. #1
    Seeker mlamew66's Avatar
    Join Date
    Apr 2019
    Posts
    9
    Articles
    0
    Excel Version
    2003

    Copy paste non consecutive cells into a different sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to copy and paste cells from one sheet to antoher (withn the same workbook), the cells I am copying are being pasted in a different sheet but not in the same solumn set up


    copy from - copy to
    sheet1 a1- Sheet2 a1
    Sheet1 b1- Sheet2 b1
    Sheet1 c1- Sheet2 e1
    Sheet1 e1- Sheet2 h1
    Sheet1 f1-Sheet2 i1


    When I click on the first cell where I am pasting in sheet2 I type =sheet1 a1. I can then copy the adjacent cell (b and c - down as many rows I need), but how can I get the non-consecutive cells to populate without having to go back and forth to sheet one, copy the other data (which I would have to do twice as there is a column in between that I do not need the data from.

    I have tried to attach a file to show what I am trying to do. I hope it uploads

    The file will be used with excel 2003.
    Attached Files Attached Files

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    166
    Articles
    0
    Excel Version
    2019
    VBA solution

    Code:
    Option Explicit
    
    
    Sub foo()
        Dim i As Long, lr As Long, lr2 As Long
        Dim s1 As Worksheet, s2 As Worksheet
        Set s1 = Sheets("copy from")
        Set s2 = Sheets("Copy to")
        lr = s1.Range("A" & Rows.Count).End(xlUp).Row
        For i = 2 To lr
            lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
            s1.Range("A" & i & ":B" & i).Copy s2.Range("A" & lr2 + 1)
            s1.Range("C" & i).Copy s2.Range("E" & lr2 + 1)
            s1.Range("E" & i & ":F" & i).Copy s2.Range("H" & lr2 + 1)
        Next i
        Application.CutCopyMode = False
        MsgBox "complete"
    End Sub

  3. #3
    Seeker mlamew66's Avatar
    Join Date
    Apr 2019
    Posts
    9
    Articles
    0
    Excel Version
    2003
    Not familiar with VBA, so will give it a try on the weekend and see what happens.

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    166
    Articles
    0
    Excel Version
    2019
    Standard Module
    How to install your new code
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


    To run the Excel VBA code:
    Press Alt-F8 to open the macro list
    Select a macro in the list
    Click the Run button

  5. #5
    Seeker mlamew66's Avatar
    Join Date
    Apr 2019
    Posts
    9
    Articles
    0
    Excel Version
    2003
    I gave this a try and it did not do what I wanted in the end. I don't think I am explaining what I want in the end properly. So I have decided to go about it another way.

    Thank you for you help.

  6. #6
    Seeker mlamew66's Avatar
    Join Date
    Apr 2019
    Posts
    9
    Articles
    0
    Excel Version
    2003
    Here goes another try at explaining what I need to do.
    I need to copy the data in several ranges (about 10) to a different area of my sheet. Sometimes the ranges uses all rows, other times it uses a few.
    When I come to a blank cell in my first range (D29:D42) I want the next cell to have the data from the first cell in my 2nd range (O29:O42), then moving on to the next range (z29:z42) and continue across to the last of my 10 ranges.

    Would the OFFSET function do this? Is so would the reference cell be the last cell with data in it and -9,3?
    Attached Files Attached Files
    Last edited by p45cal; 2019-07-29 at 12:54 PM. Reason: disabled smilies

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,568
    Articles
    0
    Excel Version
    365
    See button in attached, click it and look at the copy to sheet. The button runs this code:
    Code:
    Sub blah()
    Set Destn = Sheets("Copy to").Range("A3")
    With Worksheets("copy from").Cells
      Set c = .Find("Date", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
      If Not c Is Nothing Then
        firstAddress = c.Address
        Do
          If c.Offset(, 2).Value = "OTN" Then
            Set RngSource = Intersect(c.CurrentRegion, .Range(c.Offset(1).Row & ":" & .Rows.Count))
            If Not RngSource Is Nothing Then
              With RngSource
                .Columns(1).Resize(, 2).Copy Destn
                .Columns(3).Copy Destn.Offset(, 4)
                .Columns(5).Resize(, 2).Copy Destn.Offset(, 7)
                Set Destn = Destn.Offset(.Rows.Count)
              End With
            End If
          End If
          Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
      End If
    End With
    End Sub
    Attached Files Attached Files
    Last edited by p45cal; 2019-07-29 at 01:01 PM.

Posting Permissions

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