Runtime Error 1004 on 2nd run

[deedii]

Hello everybody,

Im quite confused on my code below. The first time you run it it works fine the 2nd attempt always had the error. "pastespecial method of range class failed". Could you please enlighten me? TIA.

Sub Rep1()

Dim wbReport As Workbook
Dim wbThis As Workbook
Dim wbRep As String
    wbRep = Application.GetOpenFilename(FileFilter:="Excel workbook (*.xls),*.xls", Title:="Open Report")
    Set wbReport = Application.Workbooks.Open(wbRep)
    wbReport.Activate

If ActiveSheet.Name = "Report1" Then
    Sheets("Report1").Select
    Cells.Select
    ActiveSheet.Range("$A$1:$AD$201").RemoveDuplicates Columns:=5, Header:=xlYes
    
    Range("D2:D500").Select
    Selection.Copy
    ThisWorkbook.Activate
 
    Worksheets("Input").Activate
    Sheets("Input").Range("C2:C500").PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    With Range("D2")
        .FormulaR1C1 = "=MID(RC[-1],LEN(RC[-1])-9,7)"
        .AutoFill Destination:=Range("D2:D100"), Type:=xlFillDefault
    End With
    
    wbReport.Close True
    Exit Sub
Else
    MsgBox "Invalid Report. Please select the correct file.", vbCritical, "Error"
    wbReport.Close True

End If

End Sub

[Kenneth Hobs]

You can step through code with F8 to debug one line at a time. Try before your pastespecial:

Debug.Print ActiveWorkbook.Name, ActiveSheet.Name, Selection.Address

The results of Debug.Print after a run will be in the Immediate window.

[mancubus]

perhaps...

Sub Rep1()
     
    Dim wbReport As Workbook
    Dim wbRep As String
    
    wbRep = Application.GetOpenFilename(FileFilter:="Excel workbook (*.xls),*.xls", Title:="Open Report")
    Set wbReport = Application.Workbooks.Open(wbRep)
    
    With wbReport
        If .ActiveSheet.Name <> "Report1" Then
            MsgBox "Invalid Report. Please select the correct file.", vbCritical, "Error"
            .Close False
            Exit Sub
        End If
        .Worksheets("Report1").Cells(1).CurrentRegion.RemoveDuplicates Columns:=5, Header:=xlYes
    End With
    
    With ThisWorkbook.Worksheets("Input")
        .Range("C2:C500").Value = wbReport.Worksheets("Report1").Range("D2:D500").Value
        .Range("D2:D500").Formula = "=MID(C2,LEN(C2)-9,7)"
        .Range("D2:D500").Value = .Range("D2:D500").Value
    End With
    
    wbReport.Close True
     
End Sub

[Paul_Hossler]

Whenever I need to have 2 WBs open, I found I make less (fewer?) mistakes by assigning to WB variables, and never relying on ActiveWorkbook or ThisWorkbook or ActiveSheet

 
Option Explicit
Sub Rep1()
     
    Dim wbReport As Workbook, wbThis As Workbook
    Dim wsReport As Worksheet, wsInput As Worksheet
    
    Dim wbRepName As String
    
    
    Set wbThis = ThisWorkbook
    Set wsInput = wbThis.Worksheets("Input")
    
    wbRepName = Application.GetOpenFilename(FileFilter:="Excel workbook (*.xls),*.xls", Title:="Open Report")
    If LCase(wbRepName) = "false" Then
        Exit Sub
    End If
    
    Set wbReport = Application.Workbooks.Open(wbRepName)
    
    Set wsReport = ActiveSheet
    
    If wsReport.Name <> "Report1" Then
        MsgBox "Invalid Report. Please select the correct file.", vbCritical, "Error"
        wbReport.Close True
        Exit Sub
    End If
        
    wsReport.Range("$A$1:$AD$201").RemoveDuplicates Columns:=5, Header:=xlYes
     
    wsReport.Range("D2:D500").Select
    Selection.Copy
    
    wbThis.Activate
    wsInput.Select
    wsInput.Range("C2:C500").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
     
    With wsInput.Range("D2")
        .FormulaR1C1 = "=MID(RC[-1],LEN(RC[-1])-9,7)"
        .AutoFill Destination:=Range("D2:D100"), Type:=xlFillDefault
    End With
     
    wbReport.Close True
     
    wbThis.Activate
    
End Sub

Paul

[deedii]

Hi all,

Thanks so much for the speedy response.
I will try to test and feedback on the results.

Kind regards,
deedii

[mancubus]

hi deedii.

since i did not use pastespecial method, it should throw different err msg. what err msg it throws and at which line is it?

btw, uploading your workbook(s) with changed, representative data will help us.

[deedii]

Hi mancubus,

I edited my reply as I just adjusted some codes with the one provided. I will then look into it if the additional routine work. I will let you know the soonest. <hugs

[deedii]

Now I got it working using Paul modified code, so the answer really is to put the sheet/book into a variables rather than using THIS command. I tried your code mancubus it also makes the same result just changed the range of the cell. Thanks so much guys.
Anyway I was just wondering how can I just display the number of result with the equal number on the item? I the below code it trims the mid data, copy the formula downward until it reaches D100.
What I am thinking is that whether its possible to just display the result equal to the number of data in C2 column downward.

With wsInput.Range("D2") 
        .FormulaR1C1 = "=MID(RC[-1],LEN(RC[-1])-9,7)" 
        .AutoFill Destination:=Range("D2:D100"), Type:=xlFillDefault 
End With

The original result im getting is

000000169432003	0169432
000000170050001	0170050
000000113653002	0113653
000000127814004	0127814
000000156493001	0156493
000000125982005	0125982
000000141982001	0141982
000000141985001	0141985
000000093209001	0093209
	#VALUE!
	#VALUE!
	#VALUE!
	#VALUE!
	#VALUE!
	#VALUE!
	#VALUE!

The #value keep on populating the column until D100, however the data in column C only until 000000093209001. I was wondering if possible to have the expected result like below.

000000169432003	0169432
000000170050001	0170050
000000113653002	0113653
000000127814004	0127814
000000156493001	0156493
000000125982005	0125982
000000141982001	0141982
000000141985001	0141985
000000093209001	0093209

[snb]

Sub M_snb() 
    With Workbooks.Open(Application.GetOpenFilename(FileFilter:="Excel workbook (*.xls),*.xls", Title:="Open Report") ) 
         .Sheets("Report1").Range("$A$1:$AD$201").RemoveDuplicates Columns:=5, Header:=xlYes 
       
        ThisWorkbook.sheets("Input").Range("C2:C500")= .Sheets("Report1").Range("D2:D500").Value 
        .Close True 
    End With 

    ThisWorkbook.sheets("Input").Range("D2:D500")= [if(input!C2:C500="","",mid(input!C2:C500,len(input!C2:C200)-9,7))]         
End Sub

[deedii]

Wow snb, I really admire you how yo write the code so concisely. I will try this later or tomorrow and give you guys a feedback.
How about if I use this formula, how can I incorporate it to that code? "=RIGHT(C2,7)" Is this correct? Anyway what does "," means?

ThisWorkbook.sheets("Input").Range("D2:D500")= [if(input!C2:C500="","",right(input!C2:C500, 7)]