excel vba error 13 type mismatch

[mansor]

Hi!

Completely new to vba, I’m trying to write a code to copy rows from one workbook to another(s).
The code clears the worksheet of the destination workbook and then copies the rows that began with the desired country.
The two ranged variables Country and cell are country names.
The code clears and paste the correct rows to the destination workbook but when a new country (cell) appears it gives the error 13.
The code is:
Sub Air_emissions()
Set MyRange = Workbooks("4 Ag air emissions.xlsm").Sheets("Sheet1").Range("A1:A1937")
For Each Country In Sheets("Countries").Range("B1:B30")
Workbooks(Country & ".xlsm").Sheets("air_emiss").Cells.Clear
Workbooks("4 Ag air emissions.xlsm").Sheets("Sheet1").Activate
For Each cell In MyRange
If cell.Value2 = "geo" Or cell.Value2 = Country.Value2 Then
cell.EntireRow.Copy
Workbooks(Country & ".xlsm").Sheets("air_emiss").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow.PasteSpecial xlPasteValues
End If
Next
Next Country
End Sub

Could you please help?
Thanks in advance for your attention,
Best regards,
Ricardo

 

[chicagocomputerclasses]

try replacing this:
If cell.Value2 = "geo" Or cell.Value2 = Country.Value2 Then

with this:
If cell = "geo" Or cell = Country Then



and replace this:
End If
Next
Next Country
End Sub

with this:
End If
Next cell
Next Country
End Sub

 

[mansor]

Hi,

Thank you Chicago but the problem remains.
After copying and paste the first country to the destination file it gives the mismatch error.
How can i upload the file so you can test it?
Thanks for all your help and attention
Ricardo

 

[chicagocomputerclasses]

what workbook is your B1:B30 country range on?

 

[mansor]

Is the 4 Ag air emissions.xlsm and has 30 country names.
Thanks for your help.

 

[chicagocomputerclasses]

first, do the workbooks with countrynames.xlsm exist and are they open?
if yes try this

Sub Air_emissions()
Set MyRange = Workbooks("4 Ag air emissions.xlsm").Sheets("Sheet1").Range("A1:A1937")
For Each Country In Workbooks("4 Ag air emissions.xlsm").Sheets("Countries").Range("B1:B30")
Workbooks(Country & ".xlsm").Sheets("air_emiss").Cells.Clear
Workbooks("4 Ag air emissions.xlsm").Sheets("Sheet1").Activate
For Each cell In MyRange
If cell = "geo" Or cell = Country Then
cell.EntireRow.Copy
Workbooks(Country & ".xlsm").Sheets("air_emiss").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow.PasteSpecial xlPasteValues
Workbooks("4 Ag air emissions.xlsm").Sheets("Sheet1").Activate
End If
Next cell
Next Country
End Sub

 

[mansor]

Hi Chicago,

Thanks again for your help but, the problem is the same.
He copies the geo line and all 50 lines from Áustria ( to the Austria workbook) but when the belgium row appear, it gives the error.
More thoughts?

 

[mansor]

Hello Chicago,

Can you please give me na extra help
Thanks for your attention,
Best regards,
Ricardo

 

[chicagocomputerclasses]

Give this one a try and see what happens.

Sub Air_emissions()
On Error Resume Next
Set MyRange = Workbooks("4 Ag air emissions.xlsm").Sheets("Sheet1").Range("A1:A1937")
For Each Country In Workbooks("4 Ag air emissions.xlsm").Sheets("Countries").Range("B1:B30")
Workbooks(Country & ".xlsm").Sheets("air_emiss").Cells.Clear
Workbooks("4 Ag air emissions.xlsm").Sheets("Sheet1").Activate
For Each cell In MyRange
If cell = "geo" Or cell = Country Then
cell.EntireRow.Copy
Workbooks(Country & ".xlsm").Sheets("air_emiss").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow.PasteSpecial xlPasteValues
Workbooks("4 Ag air emissions.xlsm").Sheets("Sheet1").Activate
End If
Next cell
Next Country
End Sub

I would have to see your file to troubleshoot this. Try to use a dropbox or something and share the file/link and I'll look into it. I think this has to do something with the data itself or files not existing. Do you have belgium.xlsm file open?

 

[mansor]

Hi Chicago,

Thanks for all your help.
Made some new files and the first code worked perfectly.
The problema was a #N/A cell in MyRange so the error finding the country name.
Problem solved 8)
Have a nice big weekend,
Ricardo