Copying row from a range to another file/Run time error 13-Type mismatch

[Shanghai3]

Dear Users,

I have problem with the following row in my macro:
Does anyone know what can be the problem?
Thank you for your help in advance.

Regards,

Shanghai3

omszrange.Rows(rownum).Copy Workbooks(Filename2.Cells(i, 1)).Worksheets(Tabname3.Cells(i, 1)).finishrange

 

[Joe4]

Can you please explain (in plain English) what exactly it is that you are trying to do, and what all your named ranges are set to?

Note that if you are trying to copy across workbooks, I don't think you can do it all on one line.
I think you need to copy the one range, then activate the other workbook on its own line before trying to paste it in a separate line of code.

I would recommend trying to break it down into multiple lines that:
1. Copy the range you want to copy
2. Select the Workbook you want to paste it to
3. Select the Sheet you want to paste it to
4. Select the Range you want to paste it to
5. Then paste it

 

[Shanghai3]

Dear Joe4,

Thank you very much for trying to help me.

My problem is the following which I would like to find a solution for:

I would like to copy and paste by paste special.value ranges from a file to 11 files, that are in the similar structure.

I wanted to set the ranges and files written in a Worksheet detailed here:

Filename1	Tabname1	Tabname2	finishrange
C:\Lala\Excel\Makrók\Sajat\Automatizalas\Kiindulo tablak\ksh_monthly202110.xlsx	ksh_monthly202110		C64
Filename2	Tabname3	Tabname4
C:\Lala\Excel\Makrók\Sajat\Automatizalas\Kiindulo tablak\15_2_2_1.xlsx	15.2.2.1_h	15.2.2.1_e
15_2_2_2.xlsx	15.2.2.2_h	15.2.2.2_e
15_2_2_3.xlsx	15.2.2.3_h	15.2.2.3_e
15_2_2_4.xlsx	15.2.2.4_h	15.2.2.4_e
15_2_2_5.xlsx	15.2.2.5_h	15.2.2.5_e
15_2_2_6.xlsx	15.2.2.6_h	15.2.2.6_e
15_2_2_7.xlsx	15.2.2.7_h	15.2.2.7_e
15_2_2_8.xlsx	15.2.2.8_h	15.2.2.8_e
15_2_2_9.xlsx	15.2.2.9_h	15.2.2.9_e
15_2_2_10.xlsx	15.2.2.10_h	15.2.2.10_e
15_2_2_11.xlsx	15.2.2.11_h	15.2.2.11_e

I wanted to use a loop to read in the different files, in which I wanted to copy the requested information.
Thank you for your help again in advance.

Here is the whole code:

Option Explicit
Option Base 1
    
Sub havimeteo()


    Dim Pathname As String, Filename1 As String, Tabname1 As String, Tabname2 As String
    Dim omszallomany As Workbook
    Dim rownum As Double
    Dim omszrange As Range
    Dim Filename2 As Range
    Dim finishrange As Range
    Dim Tabname3 As Range, Tabname4 As Range
    Dim i As Double
    
    
        
         Pathname = Range("A3")
         Filename1 = Range("B3")
        Tabname1 = Range("C3")
        Set Filename2 = Range("B6:B16")
        Set finishrange = Range("E3")
        Set Tabname3 = Range("C6:C16")
        Set Tabname4 = Range("D6:D16")
                
                
        
        Workbooks.Open (Filename1)
        
        
        Set omszrange = ActiveWorkbook.Worksheets(Tabname1).Range("D14:J14")
        
        
        i = 1
        
        For rownum = 1 To 11
        
            Workbooks.Open (Filename2.Cells(i, 1))
            
            
            
            omszrange.Rows(rownum).Copy Workbooks(Filename2.Cells(i, 1)).Worksheets(Tabname3.Cells(i, 1)).finishrange
            omszrange.Rows(rownum).Copy Workbooks(Filename2).Rows(rownum).Worksheets(Tabname4.Cells(i, 1)).finishrange
            
        i = i + 1
        
        Next rownum
        
        End Sub

 

[Joe4]

If you want to do a Copy/Paste Special/Values, you CANNOT do your whole copy/paste command on one line like that.
You need to do it across two lines, as shown in the "PasteSpecial Method to Paste Values, Formats, etc" section here:

How to Use VBA Macros to Copy Data to Another Workbook in Excel

Learn how to copy data from one workbook to another using VBA macros in Excel, includes copying below existing data and ranges that change size. +Video

www.excelcampus.com

 

[Shanghai3]

Dear Joe4,

Thank you for your help. I have studied the link you provided, but it is still not working.

The problematic rows are:

omszrange.Rows(rownum).Copy
            Workbooks(Filename2.Cells(i, 1)).Worksheets(Tabname3.Cells(i, 1)).Range("C63").PasteSpecial Paste:=xlPasteValues

It copies the data to the Clipboard, but does not paste it. The problem can be with Range("C63").PasteSpecial Paste:=PasteValues part, because the other part have the right
values in the macro.

Could you help me what can be wrong?

Thank you for your help in advance.

Best regards,

Shanghai3

 

[Joe4]

Dear Joe4,

Thank you for your help. I have studied the link you provided, but it is still not working.

The problematic rows are:

omszrange.Rows(rownum).Copy
            Workbooks(Filename2.Cells(i, 1)).Worksheets(Tabname3.Cells(i, 1)).Range("C63").PasteSpecial Paste:=xlPasteValues

It copies the data to the Clipboard, but does not paste it. The problem can be with Range("C63").PasteSpecial Paste:=PasteValues part, because the other part have the right
values in the macro.

Could you help me what can be wrong?

Thank you for your help in advance.

Best regards,

Shanghai3

It looks like you are copying a whole row (i.e. .Rows(rownum).Copy), but then you are trying to paste the results to cell C63.
That will never work. If you are copying the whole row, you MUST paste it column A. Trying to copy it to any other column would result in your going paste the last column on the sheet, which is not possible.

So you either need to change your code to paste to column A, or copy something less than an entire row (an entire row will ALWAYS include every single possible column on the worksheet).

 

[Shanghai3]

Dear Joe4,

No, I do not want to copy a whole row, just rows from a range with the help of a loop, but I still do not understand why it does not work.

Thank you for your help in advance.

Regards,

Shanghai3

 

[Joe4]

Look at this part right here:

omszrange.Rows(rownum).Copy

You are telling it to copy the entire row! That will copy columns A - XFD, which is a total of 16384 columns!
It is important to note that column XFD (or column number 16384) is the last possible column you are allowed in Excel.

However, if you are trying to paste the entire row, starting in column C, like you are doing here:

 Workbooks(Filename2.Cells(i, 1)).Worksheets(Tabname3.Cells(i, 1)).Range("C63").PasteSpecial Paste:=xlPasteValues

that would try to paste to to columns 3-16386 (because it MUST be the exact same size as the original range you are copying, which 16384 columns).
However, you cannot go past 16384, since that is the last column you are allowed to have in Excel.
Hence, you get an error.

Its like if you have a 10 foot board and a 10 foot hole. But you want to leave a 1 foot gap from the one side. The board won't fit in the hole because it is too long if you try to leave a gap. That is what is happenning here.

You either need to:
- Paste the results in column A so that you use every single column
or
- Shorten the number of columns you are copying, so you are not copying every single column in the row

I think you are getting hung up on nomenclature. In VBA "row" means the "entire row".
If you want to just copy certain columns, you need to specify the exact columns you want to copy in your range (and not use "rows").
If you tell us exactly what that is, we can help you edit that part of your code.

 

[Shanghai3]

Dear Joe4,

I have tried out what you proposed, but there is still the same error. So something else is problematic.
You are right, that I try to copy an entire row. But I would copy it from a range, what I defined previously:

Set omszrange = ActiveWorkbook.Worksheets(Tabname1).Range("D14:J14")

And the entire first row within this range is only the D14:J14 range within the entire row. The macro copies these cells to the clipboard properly,
but does not paste it.

I really do not know why I am wrong.

Thank you for your help in advance.

Regards,

Shanghai3

 

[Joe4]

Here is what I would recommend doing. Go back to the link I posted up in post 4, where it shows the hard-coded way of doing what you want (in the "Copy Data from one Workbook to Another Using Excel Macros") and hard-code an example of what you are trying to do (that is, replace ALL the variables you are using with hard-coded values.

Once you have that hard-coded example working, then replace one hard-coded value with a variable, and try it again.
If that works, then replace another value with a variable, etc.
Keep doing this until either you run into an error, or you have it working correctly.
If you add the variables one at a time and test them, then you when exactly which variable is causing the error for you.
If you cannot even get the basic hard-coded example working, then you know that there is an issue with your logic.

If you run into issues and cannot figure out your error, post your copy line of code along with the error message.