• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run Time Error 13 Type Mismatch

roypabitra

roypabitra

Member
Respected Sir,

I have the following code which previously worked perfectly but now I got the Run time error 13 Type Mismatch. Please help me how to solve

Code: 
Sub copy_paste_data_from_one_sheet_to_another1()
'Let's start at row 2. Row 1 has headers
x = 9
Dim myName As String
Dim myNameF As String
Dim cPart As Range
Dim wk3 As Worksheet
Dim wk4 As Worksheet
Dim i As Range
Dim i2 As Range
Dim oCol As Long
 
'Dim nextRow As Long
'Dim oCol As Long
 
'Dim myCopy As Range
'Dim myTest As Range
'Dim myTest1 As Range
 
'Set inputWks = Worksheets("Input")
'Set historyWks = Worksheets("KOLKATA")
 
Set wk3 = Worksheets("ledger")
Set wk4 = Worksheets("Pmt")
 
wk4.Activate
'LastRow = Range("D65536").End(xlUp).Row + 1
Range("A10" & ":L65536").ClearContents
 
wk3.Activate
Application.CutCopyMode = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.Range("A10:L19999").Value = ActiveSheet.Range("A10:L19999").Value
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
wk4.Activate
myNameF = wk4.Range("inputt")
myName = UCase(myNameF)
 
'Start the loop
Do While Cells(x, 8) <> ""
'Look for name
If Cells(x, 8) = myName Then
'copy the row if it contains 'myName'
wk3.Rows(x).Copy
'Go to sheet2. Activate it. We want the data here
wk4.Activate
'Find the first empty row in sheet2
erow = wk4.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).Row
'Paste the data here
Application.DisplayAlerts = False
ActiveSheet.Paste Destination:=wk4.Rows(erow)
End If
'go to sheet1 again and actvate it
wk3.Activate
'Loop through the other rows with data
x = x + 1
Loop
wk3.Activate
Range("a5:L5").Select
Range(Selection, Selection.End(xlToRight)).Select
'Range(Selection, Selection.End(xlDown)).Select
 
Selection.Copy
'Workbooks.Add
Range("a10:A19999").Select
ActiveSheet.Paste
 
wk4.Activate
End Sub
Thanks & Regards
Pabitra Roy
 
Hi roypabitra,

Would you also upload your workbook to check this issue. It looks like the error is due to the missing/mismatch of the worksheet names. Uploading the file will help to spot the issue correctly.
 
Respected Sir,

As desired by you I am trying to uploading the actual database file but it fails to uploaded due to uploaded file is too large.

Thanks & Regards

Pabitra
 
Hi

I didn't try the code but i think the error line which he got that is may be

Code: 
myNameF = wk4.Range("Input")

i think it is clear for you

Ps: this Msg send from mobile that's why i can't check but i guess the error code

Thanks
 
Dear All my Friends and Master,

When I get the error and debug then open the VB and cursor goes to Do While Cells(x, 8) <> ""

So I think error on Do While Cells(x, 8) <> ""

Thanks & Regards

Pabitra
 
Hi Pabitra ,

I do not know what exactly your code is supposed to do ; I have not gone through it , so I cannot say what it is doing.

However , one point just caught my notice ; if you see the code which I am reproducing below :
Code: 
wk4.Activate
myNameF = wk4.Range("inputt")
myName = UCase(myNameF)
 
'Start the loop
Do While Cells(x, 8) <> ""
'Look for name
  If Cells(x, 8) = myName Then
'copy the row if it contains 'myName'
      wk3.Rows(x).Copy
'Go to sheet2. Activate it. We want the data here
      wk4.Activate
'Find the first empty row in sheet2
      erow = wk4.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).Row
'Paste the data here
      Application.DisplayAlerts = False
      ActiveSheet.Paste Destination:=wk4.Rows(erow)
  End If
'go to sheet1 again and actvate it
  wk3.Activate
'Loop through the other rows with data
  x = x + 1
Loop

when you enter the loop , the activesheet is wk4 , but thereafter you are activating wk3 before the next iteration ; you should include :

wk3.Activate

just before you enter the loop.

When the error occurs , what is the value of x ?

You can either hover the mouse over the variable within the code , or you can type :

?x

in the Immediate window , and see what is displayed.

Narayan
 
Hi Roypabitra

What I like to do when ever I see code laid out in this way and the task is to debug, is a process I will describe as ‘Rework’. Take the code and start again. Roypabitra you are only trying to move data based on a condition.

Firstly Roypabitra
Have a read of the following, it is good gear;

http://www.ozgrid.com/forum/showthread.php?t=177019

You will never write or incorporate code like the above again. Point 7 from memory.

Here is some code which should replace your code. You won’t need any of the alerts turned off as it will perform nicely when compared to a looping construct.

Code: 
Option Explicit
'Sheet4 is PMT, Sheet3 is Ledger.
Sub testo()
    Range("H7", Range("H65536").End(xlUp)).AutoFilter 1, "GiddyUP"
    Range("A8", Range("H65536").End(xlUp)).Copy Sheet4.Range("A65536").End(xlUp)(2)
    [h7].AutoFilter
    [a7].CurrentRegion.Copy Sheet1.[a10]
End Sub


Here is a file now just change “GIDDYUP” with the variable you are looking to move, not sure if sheet1 is applicable but replace that too if not and you are in business my friend. Also when you get a chance to post on forums as file is gold for people trying to assist you.

I realise this post is not the norm but it is Friday I have been to the pub and just drowned in my 5th Carona and I am in a giddy Up kind of mood!!!!

Happy Weekend Everybody!!!!


Smallman
 

Attachments

  • 1GiddyGiddy.xlsm
    20.1 KB · Views: 3
Sometimes it is nice to visit Mexico. Serious respect to our Mexican brothers out there for creating a thoroughly refreshing nectar. Ah Carlsberg, my visits to Denmark have been memorable. Most of my recent air miles have been been to Belgium. A fruit flavoured combination that leaves you begging for more.
 
Smallman said:
I realise this post is not the norm but it is Friday I have been to the pub and just drowned in my 5th Carona and I am in a giddy Up kind of mood!!!!
Click to expand...
@SirJB7
Hi, myself!
So long...
Aha! Now everything's clear, this clarified all my doubts, I've been always wondering why...
Regards!
 
You must log in or register to reply here.
Back
Top