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)]