Copy and pasting data onto a different worksheet in excel

Question

I am trying to copy data from range C2:G2 in sheet1 tab onto sheet2 tab range C4:C8. Below is my code but it just pastes the value in cell c2 of sheet 1 repeatedly onto sheet 2 range C4:C8. Can you please help?

Also , I need to copy the value of cell c12 in sheet 1 onto e4 of sheet 2, f12 in sheet 1 onto e5 of sheet 2, i12 in sheet 1 onto e6 of sheet 2, L12 in sheet 1 onto e7 of sheet 2, O12 in sheet 1 onto e8 of sheet 2

Thanks in advance

Andy

Sub ticker1() 'COPY DATA FROM ROW ONE SHEET INTO A COLUMN on another sheet
Sheets("Sheet2").Range("C4:C8").Value = Sheets("Sheet1").Range("C2:G2").Value
End Sub

It's not completely clear from your question what you are asking. However, I take it that `HistoricalDataRequest` is sheet 1 you refer to? If so, I can see you are selecting that, so the ranges will be copied from there, but you are not selecting 'sheet 2' to output it to (there are better ways to do this without having to select sheets, but we can ignore that for now). Try adding `Worksheets("The name of your sheet 2 here").Activate`, before `Range(Cells(7, currentPasteColumn), Cells(7, currentPasteColumn)).Select` — Pash101, Apr 27 '14 at 20:09
OK Thanks will give that a crack now! What is the better way to do this from your point of view? — user3565577, Apr 27 '14 at 20:32
Generally, you can bypass the whole copy/paste method (which is slow) and just do something like this: `sheets("Sheet 2").Cells(1,1).value = sheets("Sheet 1").Cells(1,1).value` that will make cell A1 in sheet 2 equal to sheet 1. You can loop through this range to output the values you need. You can also just copy a range by doing something like this: `Sheets("Sheet2").Range("A1:A25").Value = Sheets("Sheet1").Range("A21:A45").Value` — Pash101, Apr 27 '14 at 21:37
Thanks Pash. So what if I wanted to paste and skip 2 columns for example? (i.e A21, A24, A27??) — user3565577, Apr 28 '14 at 08:38
If you just want to copy individual cells, the first method would work with a for loop: `for i = 0 to 2; sheets("Sheet 2").Cells(21+(3*i),1).value = ....` etc. that would select cells A21, A24 and A27 — Pash101, Apr 28 '14 at 09:00
I have used your code above thanks. It works if I copy and paste 2 rows but if I wish to copy a row to paste to a column it just pastes the first cell repeatedly onto the column in sheet 2. Can ypu please advise? — user3565577, Apr 28 '14 at 12:58
I'm still not clear as to what you are trying to do. So people can assist further, can you please edit your original post with your new code, what output it is giving (why it is wrong) and what your desired output is? Thanks. — Pash101, Apr 28 '14 at 14:43
I've posted an answer which should sort your problem, thanks. — Pash101, Apr 29 '14 at 19:23
Brilliant thanks Pash, I will try it at work tomorrow. What if I was to copy one cell and skip 2 columns (i.e copy a1, d1, g1) and paste onto another worksheet in a column ie al to a3? I would assume you will have to somehow insert an offset facility in place? — user3565577, Apr 29 '14 at 20:59
You could use an offset. Equally valid would be something like this: `for i = 0 to 2; sheets("Sheet 2").Cells(1+i,1).value = sheets("Sheet 1").Cells(1,1+(3*i)).value... etc.`. As an aside, it will be better for your own learning if you try and come up with a solution yourself first, then ask for help if it doesn't work. — Pash101, Apr 30 '14 at 14:09

score 0 · Answer 1 · edited May 23 '17 at 11:57

Your code was very close. In fact, it may have worked. I'm not sure. Using select & activate is inevitably an issue. Avoid it..

This isn't really an answer. Just some food for thought as you're learning to program. My comments start with {--

Option Explicit ' {--option explicit forces you to declare your variables. Do this. Always.

Sub ticker()

    '{group your variable declarations together}
    Dim numberOfCellsToCopy As Integer
    Dim currentCopyColumn As Integer
    Dim currentPasteRow As Integer
    Dim i As Integer
    ' {--use a worksheet object instead of selecting}
    Dim srcWs As Worksheet
    Dim destWs As Worksheet

    'assign where to insert the columns
    currentCopyColumn = 2
    currentPasteColumn = 2
    numberOfCellsToCopy = 5

    ' {--Set worksheet objects}
    Set srcWs = ThisWorkbook.Worksheets("HistoricalDataRequest")
    Set destWs = ThisWorkbook.Worksheets("SomeOtherWorksheet")

    For i = 2 To numberOfCellsToCopy  'loop increases by 1??? {--YES. It does.}
        ' {--indent your code so it's easier to see program flow
        ' {--I have no idea what you're trying to do here.... sorry. You're only copying one cell
    '   --so I really can't figure what you're trying to accomplish}

        'Range(Cells(2, currentCopyColumn), Cells(2, currentCopyColumn)).Select 'number =row
        'Selection.Copy 'number =row
        'Range(Cells(7, currentPasteColumn), Cells(7, currentPasteColumn)).Select
        'ActiveSheet.Paste

        ' {--copy/paste can be done in one line using a range object
        '   --range uses a cell name.}
        srcWs.Range("B2").Copy Destination:=destWs.Range("B7")

        ' { --or more in line with your method like this}
        srcWs.Range(srcWs.Cells(2, currentCopyColumn)).Copy Destination:=destWs.Range(destWs.Cells(7, currentPasteColumn))

        'increase the column index values to go to the next one
        currentCopyColumn = currentCopyColumn + 1
        currentPasteColumn = currentPasteColumn + 4

    Next i ' {--next i will increment the i var for you}
End Sub

score 0 · Answer 2 · answered Apr 29 '14 at 19:22

based on your edited code, this should work:

Sheets("Sheet5").Range("C4:C8").Value = Application.WorksheetFunction.Transpose(Sheets("Sheet2").Range("C2:G2").Value)

As your arrays are in different orientations, the transpose function will align them correctly.

There are a few other ways of doing this, but this is probably the most direct (if you do not need too much flexibility with your ranges).

Copy and pasting data onto a different worksheet in excel

2 Answers2