VBA - Copying and Pasting - Running Error

Question

Please see my code below. I have a list of data in columns A to R in sheet7. I need to copy the data from row 1 columns A-F to the first 12 rows of sheet6, starting in row E. After doing this I need to transpose the data in sheet7 from row 1 columns G-R to sheet6 to column Q (rows 1-12).

Thanks in advance. Question is when running code I am getting a "Run-time error 13, Type Mismatch". When using F8 to locate issue I cannot see where it occurs. I get past the msgbox part of code.

This needs to be repeated many times to complete all data.

Sub copy1()
    Dim i As Long
    Dim j As Long
    j = 2
    MsgBox (j)

    For i = 2 To 1000
        Worksheets(Sheet7).Activate
        Range(Cells(i, 1), Cells(i, 6)).Select
        Selection.copy
        Worksheets(Sheet6).Activate
        Range(Cells(j, 5), Cells(j + 11, 5)).PasteSpecial
        Worksheets(Sheet7).Activate
        Range(Cells(i, 7), Cells(i, 18)).Select
        Selection.copy
        Worksheets(Sheet6).Activate
        Range(Cells(j, 17).Address).PasteSpecial Transpose:=True
        j = j + 12
    Next i    
End Sub

What is your question? what is working, what is going wrong? Did you get any errors and where? This is not a "here is my code … fix it" website. Note: Always use `Long` Excel has more rows than integer can handle. — Pᴇʜ, Feb 19 '18 at 14:37
When you say `worksheet2` do you mean `Sheet6`? Also [ask] and [mcve]. — Darren Bartrup-Cook, Feb 19 '18 at 14:47
Worksheet names need to be in `""` so `Worksheets(Sheet7)` should be `Worksheets("Sheet7")` same for `Sheet6`. I also recommend to find a tutorial on how to avoid `.Select` and `.Activate` there are many on Google. Also using `Option Explicit` would be a very good idea. — Pᴇʜ, Feb 19 '18 at 15:36

Vityata · Answer 1 · 2018-02-19T14:50:05.103

0

The problem with the code is that Range needs either two cells or an address of the cell to work. And you need Range for the PasteSpecial part. See yourself, how the following would not work:

Sub TestMe()
    Range(Cells(1, 1)).Select
End Sub

But this would work nicely (all selecting A1):

Sub TestMe()
    Range(Cells(1, 1), Cells(1, 1)).Select
    Range(Cells(1, 1).Address).Select
    Range("A1").Select
    Range([A1].Address).Select
    Cells(1, 1).Select
    Cells(1).Select
    [A1].Select
End Sub

Thus, change your code from: Range(Cells(j, 17)).PasteSpecial Transpose:=True

To: Range(Cells(j, 17).Address).PasteSpecial Transpose:=True

Further interesting topics:

edited Feb 19 '18 at 14:50

answered Feb 19 '18 at 14:44

Vityata

42,633
8
55
100

1

The conversion from a range (`Cells(j, 17)`) to a string (`Cells(j, 17).Address`) and back to a range (`Range(Cells(j, 17).Address)`) is pointless. Just use `Cells(j, 17).PasteSpecial Transpose:=True` – chris neilsen Feb 20 '18 at 02:08
1

@chrisneilsen - not exactly pointless, the idea is to show what happens in the `Range()` and why `Range(Cells(1,1))` does not work. – Vityata Feb 20 '18 at 09:11

score 0 · Answer 2 · answered Feb 19 '18 at 14:51

0

Your code appears to have some potential issues. Here is an improved version of it. Since you did not mention the exact issues, see if this one works..

Sub copy1()
    Dim i As Long
    Dim j As Integer
    j = 2
    MsgBox (j)

    For i = 2 To 1000
        Worksheets(Sheet7).Range(Cells(i, 1), Cells(i, 6)).copy
        Worksheets(Sheet6).Range(Cells(j, 5), Cells(j + 11, 5)).PasteSpecial xlPasteValues
        Worksheets(Sheet7).Range(Cells(i, 7), Cells(i, 18)).copy
        Worksheets(Sheet6).Range(Cells(j, 17),Cells(j, 17)).PasteSpecial xlPasteValues Transpose:=True
        j = j + 12
    Next i   
    Application.CutCopyMode = False
End Sub

answered Feb 19 '18 at 14:51

shash

246
1
7

1

Consider declaring the parents completely. E.g. writing `Worksheets(Sheet7).Range(Worksheets(Sheet7).Cells(i, 1), Worksheets(Sheet7).Cells(i, 6)).copy`, otherwise the code considers the activesheet. To understand what I mean, try to run your code by activating `Sheet1` first. – Vityata Feb 19 '18 at 14:53
@shash, I have tried this code however I still get a "Run-time error 13, Type Mismatch". – James Feb 19 '18 at 15:04
1

This cannot work `Worksheets(Sheet7)` because of missing `""` I hardly recommend to always use `Option Explicit` then you would have immediately seen this error. In your code `Sheet7` and `Sheet6` are variables and both are empty and cast to `0` in this case so it is the same like writing `Worksheets(0)`. – Pᴇʜ Feb 19 '18 at 15:35
Peh - Thanks for the feedback and apologies for the basic questions... I am new to this (as you can probably see by my code). I have now turned on Option Explicit. – James Feb 19 '18 at 18:26
Peh - I have updated the code as you have said and I'm now struggling with a "Subscript out of range" error 9. any advice? – James Feb 19 '18 at 18:28
PEH, thanks for the correction, I overlooked that thinking it 'Sheet7' was a string variable. James, one possible reason for this error could be mismatch in the name of the sheets. The sheet that you refer to has to be named exact same way i.e."Sheet7". Another way to refer to sheets is by using something like ThisWorkbook.Sheets(i) where i is an integer index of the sheet you're looking for. – shash Feb 19 '18 at 19:16
James can you please mark the things that helped you, as useful. – shash Feb 21 '18 at 14:37
The issue was that I missed out the ".Address" when defining the range... – James Mar 20 '18 at 09:48

VBA - Copying and Pasting - Running Error

2 Answers2