Copy paste non consecutive cells into a different sheet

M

mlamew66

New member
Joined
Apr 22, 2019
Messages
9
Reaction score
0
Points
0
Excel Version(s)
2003
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.
 

Attachments

  • to upload.xlsm
    10.2 KB · Views: 12
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
 
Not familiar with VBA, so will give it a try on the weekend and see what happens.
 
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
 
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.
 
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 2[SUP]nd[/SUP] 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?
 

Attachments

  • to upload.xlsm
    12.9 KB · Views: 6
Last edited by a moderator:
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
 

Attachments

  • ExcelGuru10117upload.xlsm
    22.8 KB · Views: 8
Last edited:
You must log in or register to reply here.
Back
Top