Automation Error in VBA

S

Swoootie

Board Regular
Joined
Jun 28, 2012
Messages
66
Hi.

I have been attempting to perform a vlookup between 2 sheets and have just encountered the following error message:
Run-Time Error '-2147221080 (800401a8)':
Automation Error

What I am attempting to do is reference an eternal workbook where the information is trimmed and copied and pasted as values to match the destination cells and manually this works. When I try to hard code this using VBA, the error message pops up at the point at which the vlookup code is executed (so the trimming of the cells and copying across works fine).

A cut version of my code is below:

Option Explicit

Sub PORTMON()
Dim DT As Date
'INPUT BOX TAKES DATE FROM USER
DT = Application.InputBox("Enter a Date as MMM YYYY")
DT = Format(DT, "MMM YYYY")

Dim PATH As String
Dim WKBOOKATT2 As Workbook
Dim FILENAME2 As String

PATH = "C:\Checks\"

FILENAME6 = "ACC_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
Set WKBOOKATT2 = Workbooks.Open(PATH & "ACC_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")

Dim APATT1 As Variant

Workbooks.Open (SASPATH & "ACC_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Range("F2").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-5])"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F14")
Range("F2:F14").Select
Range("B2:B14").Select
Selection.Copy
Range("G2").Select
ActiveSheet.Paste
Range("F2:F14").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows( _
"Acc Main.xls"). _
Activate
Sheets("Stability").Select
Range("D20").Select
'error occurs here
APATT1 = Application.VLOOKUP(Range("A20").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("F2:G15"), 2, False)
Range("D20").VALUE = APATT1

Has anyone come across anything like this before?

Please let me know if you require any further details from me.

Thanks
 
Sort by date Sort by votes
Is this all the code you have in this workbook?

Do you have any add-ins loaded?
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi Norie,

This was the trimmed down version of the code, i.e. the code required to perform the actions I needed as part of the automation process. The full code is a lot longer.

Would it help if I post this code?

I'll have to change some references within the code due to confidentiality issues (as this is for a work project).

A brief outline of what I'm doing is that there is one main spreadsheet where a user enters a date and data is pulled from different sources (related to that date) to populate the sheet. In this part of the code, mini versions of the main sheet are created using external files and then the mini versions are used to build up the main sheet. In other words the multiple spreadsheets populate 6 sheets and those 6 sheets are used to populate a summary tab in the main sheet.

What are the "add-ins" in VBA?

I hope this makes sense, but please let me know of anything else you may need to know.

Thank you so much for your reply.
 
Upvote 0
Are you actually automating something?
 
Upvote 0
Almost forgot, yes please post all the code.
 
Upvote 0
Hi,

I'm automating a report that was previously created manually. I'm changing this so that it's little more than a click of a button to run.

Is there a way I can post the code for only you to have a look at rather than posting it publically?

Thanks
 
Upvote 0
What exactly do you mean by automating?

I think the error is referring to the automation of another application.

If you post the code here, less any confidential information, then you'll have more chance of getting help because more people will see it.
 
Upvote 0
Hi,

My code is pasted below (with some minor changes to names). The automation refers to the way in whch the main workbook references the other workbooks. This is the code where the error occurs, however, there is a separate (and longer) bit of code that relates to the main spreadsheet. Apologies if the code is a bit scrappy, I'm very new to VBA coding.

Thanks


'PART1 - SETTING UP
Option Explicit
Public strFileName As String
Public strFilePath As String
Public DT As Date

Sub MON()
Dim DT As Date

'PART 2 - OBTAINING DATE FROM USER AND APPLYING IT ACROSS THE WORKSHEETS
'(i)

'INPUT BOX TAKES DATE FROM USER
DT = Application.InputBox("Enter a Date as MMM YYYY")
DT = Format(DT, "MMM YYYY")

'SPECIFYING WORKBOOKS
Dim SSS As Workbook
Dim AAA As Workbook
Dim BBB As Workbook
'Dim CCC As Workbook
Dim DDD As Workbook
'Dim TTT As Workbook
Dim PACK As Workbook

Dim WKBOOKATT1 As Workbook
Dim WKBOOKATT2 As Workbook
Dim WKBOOKATT3 As Workbook
Dim WKBOOKATT4 As Workbook
Dim WKBOOKATT5 As Workbook
Dim WKBOOKATT6 As Workbook

Dim WKBOOKSTAB1 As Workbook
Dim WKBOOKSTAB2 As Workbook
Dim WKBOOKSTAB3 As Workbook
Dim WKBOOKSTAB4 As Workbook
Dim WKBOOKSTAB5 As Workbook
Dim WKBOOKSTAB6 As Workbook

Dim WKBOOKGINI1 As Workbook
Dim WKBOOKGINI2 As Workbook
Dim WKBOOKGINI3 As Workbook
Dim WKBOOKGINI4 As Workbook
Dim WKBOOKGINI5 As Workbook
Dim WKBOOKGINI6 As Workbook

Dim WKBOOKPRED1 As Workbook
Dim WKBOOKPRED2 As Workbook
Dim WKBOOKPRED3 As Workbook
Dim WKBOOKPRED4 As Workbook
Dim WKBOOKPRED5 As Workbook
Dim WKBOOKPRED6 As Workbook


Dim PACKPATH As String
Dim PATH As String
Dim SASPATH As String

Dim FILENAME1 As String
Dim FILENAME2 As String
Dim FILENAME3 As String
Dim FILENAME4 As String
Dim FILENAME5 As String
Dim FILENAME6 As String
Dim FILENAME7 As String
Dim FILENAME8 As String
Dim FILENAME9 As String
Dim FILENAME10 As String
Dim FILENAME11 As String
Dim FILENAME12 As String
Dim FILENAME13 As String
Dim FILENAME14 As String
Dim FILENAME15 As String
Dim FILENAME16 As String
Dim FILENAME17 As String
Dim FILENAME18 As String
Dim FILENAME19 As String
Dim FILENAME20 As String
Dim FILENAME21 As String
Dim FILENAME22 As String
Dim FILENAME23 As String
Dim FILENAME24 As String
'SPECIFYING PATH FOR OVERALL PACK
PACKPATH = "C:\ Blank Monitoring Pack\"
'SPECIFYING PATH WHERE INFORMATION IS TO BE POPULATED
PATH = "C:\Portfolio\"
'SPECIFYING PATH WHERE INFORMATION IS OUTPUT TO SAS
SASPATH = "C:\ Portfolio Outputs\"
'SPECIFING THE FILE NAMES OUTPUT FROM SAS
FILENAME1 = "PP_1_ATT_SSS_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME2 = "PP_1_ATT_TEST_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME3 = "PP_1_ATT_TEST_SUBPRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME4 = "PP_1_ATT_DDD_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME5 = "PP_1_STAB_SSS_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME6 = "PP_1_STAB_TEST_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME7 = "PP_1_STAB_TEST_SUBPRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME8 = "PP_1_STAB_DDD_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME9 = "PP_1_GINI_SSS_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME10 = "PP_1_GINI_TEST_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME11 = "PP_1_GINI_TEST_SUBPRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME12 = "PP_1_GINI_DDD_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
'FILENAME13 = "PP_1_ATT_TTT_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
'FILENAME14 = "PP_1_STAB_TTT_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
'FILENAME15 = "PP_1_GINI_TTT_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
'FILENAME16 = "PP_1_ATT_CCC_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
'FILENAME17 = "PP_1_STAB_CCC_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
'FILENAME18 = "PP_1_GINI_CCC_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME19 = "PP_1_ACTPREDBR_SSS_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME20 = "PP_1_ACTPREDBR_TEST_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME21 = "PP_1_ACTPREDBR_TEST_SUBPRIME" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
FILENAME22 = "PP_1_ACTPREDBR_DDD_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
'FILENAME23 = "PP_1_ACTPREDBR_TTT_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)
'FILENAME24 = "PP_1_ACTPREDBR_CCC_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4)

'OPENING WORKBOOKS TO BE REFERENCED
'Set PACK = Workbooks.Parent(PACKPATH & "Behavioural Scorecard PP1 Monitoring Pack_v1.4_INC_PORT.XLS")
On Error Resume Next
Set SSS = Workbooks.Open(PATH & "SSS PP1.XLS")
On Error GoTo 0
Set AAA = Workbooks.Open(PATH & "TEST Prime PP1.XLS")
Set BBB = Workbooks.Open(PATH & "TEST Sub Prime PP1.XLS")
'Set CCC = Workbooks.Open(PATH & "CCC PRIME PP1.XLS")
Set DDD = Workbooks.Open(PATH & "DDD PRIME PP1.XLS")
'Set TTT = Workbooks.Open(PATH & "BUY TO LET PP1.XLS")
Set WKBOOKATT1 = Workbooks.Open(SASPATH & "PP_1_ATT_SSS_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKATT2 = Workbooks.Open(SASPATH & "PP_1_ATT_TEST_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKATT3 = Workbooks.Open(SASPATH & "PP_1_ATT_TEST_SUBPRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKATT4 = Workbooks.Open(SASPATH & "PP_1_ATT_DDD_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
'Set WKBOOKATT5 = Workbooks.Open(SASPATH & "PP_1_ATT_TTT_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
'Set WKBOOKATT6 = Workbooks.Open(SASPATH & "PP_1_ATT_CCC_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")

Set WKBOOKGINI1 = Workbooks.Open(SASPATH & "PP_1_GINI_SSS_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKGINI2 = Workbooks.Open(SASPATH & "PP_1_GINI_TEST_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKGINI3 = Workbooks.Open(SASPATH & "PP_1_GINI_TEST_SUBPRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKGINI4 = Workbooks.Open(SASPATH & "PP_1_GINI_DDD_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
'Set WKBOOKGINI5 = Workbooks.Open(SASPATH & "PP_1_GINI_TTT_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
'Set WKBOOKGINI6 = Workbooks.Open(SASPATH & "PP_1_GINI_CCC_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")

Set WKBOOKSTAB1 = Workbooks.Open(SASPATH & "PP_1_STAB_SSS_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKSTAB2 = Workbooks.Open(SASPATH & "PP_1_STAB_TEST_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKSTAB3 = Workbooks.Open(SASPATH & "PP_1_STAB_TEST_SUBPRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKSTAB4 = Workbooks.Open(SASPATH & "PP_1_STAB_DDD_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
'Set WKBOOKSTAB5 = Workbooks.Open(SASPATH & "PP_1_STAB_TTT_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
'Set WKBOOKSTAB6 = Workbooks.Open(SASPATH & "PP_1_STAB_CCC_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")


Dim WKBOOKPRED1 As Workbook
Dim WKBOOKPRED2 As Workbook
Dim WKBOOKPRED3 As Workbook
Dim WKBOOKPRED4 As Workbook
Dim WKBOOKPRED5 As Workbook
Dim WKBOOKPRED6 As Workbook

Set WKBOOKPRED1 = Workbooks.Open(SASPATH & "PP_1_ACTPREDBR_SSS_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKPRED2 = Workbooks.Open(SASPATH & "PP_1_ACTPREDBR_TEST_PRIME_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKPRED3 = Workbooks.Open(SASPATH & "PP_1_ACTPREDBR_TEST_SUB_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKPRED4 = Workbooks.Open(SASPATH & "PP_1_ACTPREDBR_DDD_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKPRED5 = Workbooks.Open(SASPATH & "PP_1_ACTPREDBR_TTT_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Set WKBOOKPRED6 = Workbooks.Open(SASPATH & "PP_1_ACTPREDBR_CCC_"& Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")


'''''''''''''''''''''SSS
'ACIVATING THE CORRECT REFERENCE SHEET

Windows("SSS PP1.XLS"). _
Activate

Sheets("SSS Front Page").Range("G7").VALUE = DT
Sheets("PP1 Score Stability Indices").Range("C6") = "%" & Format(DT, "MMM YYYY")
Sheets("PP1 Score Stability Indices").Range("D6") = "Stability Index - Benchmark Vs " & Format(DT, "MMM YYYY")
Sheets("PP1 - Predictive Acc").Range("A6") = DT
Sheets("PP1 - Predictive Acc").Range("D11") = "ACT " & Format(DT, "MMM YYYY") & "BR"
Sheets("PP1 - Predictive Acc").Range("E11") = "EXP " & Format(DT, "MMM YYYY") & "BR"

'STABILITY INDEX
Dim SSSPCT As Variant
Dim SSSVAL As Variant

Windows( _
"SSS PP1.xls"). _
Activate
Sheets("PP1 Score Stability Indices").Select
ActiveWindow.SmallScroll Down:=-12
Range("O7").Select
SSSPCT = Application.VLOOKUP(Range("A7:A17").VALUE, WKBOOKSTAB1.Sheets(FILENAME5).Range("A:C"), 3, False)
Range("O7:O17").VALUE = SSSPCT
Range("N7").Select
SSSVAL = Application.VLOOKUP(Range("A7:A17").VALUE, WKBOOKSTAB1.Sheets(FILENAME5).Range("A:B"), 2, False)
Range("N7:N17").VALUE = SSSVAL


'COPYING GINI ACROSS
Workbooks("SSS PP1.XLS").Sheets("PP1 -Predictive Acc").Range("E8").VALUE = WKBOOKGINI1.Sheets(FILENAME9).Range("A2").VALUE

Windows("SSS PP1.XLS"). _
Activate
Sheets("PP1 Characteristic Stability").Range("C22").VALUE = WKBOOKATT1.Sheets(FILENAME1).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C28").VALUE = WKBOOKATT1.Sheets(FILENAME1).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C33").VALUE = WKBOOKATT1.Sheets(FILENAME1).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C39").VALUE = WKBOOKATT1.Sheets(FILENAME1).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C44").VALUE = WKBOOKATT1.Sheets(FILENAME1).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J23").VALUE = WKBOOKATT1.Sheets(FILENAME1).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J29").VALUE = WKBOOKATT1.Sheets(FILENAME1).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J34").VALUE = WKBOOKATT1.Sheets(FILENAME1).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J39").VALUE = WKBOOKATT1.Sheets(FILENAME1).Range("D2").VALUE


''''''''''''''''TEST PRIME

Windows("TEST Prime PP1.XLS"). _
Activate

Sheets("TESTord Prime Front Page").Range("G7").VALUE = DT
Sheets("PP1 Score Stability Indices").Range("C6") = "%" & Format(DT, "MMM YYYY")
Sheets("PP1 Score Stability Indices").Range("D6") = "Stability Index - Benchmark Vs " & Format(DT, "MMM YYYY")
Sheets("PP1 - Predictive Acc").Range("A6") = DT
Sheets("PP1 - Predictive Acc").Range("D11") = "ACT " & Format(DT, "MMM YYYY") & "BR"
Sheets("PP1 - Predictive Acc").Range("E11") = "EXP " & Format(DT, "MMM YYYY") & "BR"

'STABILITY INDEX
Dim APPCT As Variant
Dim APVAL As Variant

Windows( _
"TEST Prime PP1.xls"). _
Activate
Sheets("PP1 Score Stability Indices").Select
ActiveWindow.SmallScroll Down:=-12
Range("O7").Select
APPCT = Application.VLOOKUP(Range("A7:A17").VALUE, WKBOOKSTAB2.Sheets(FILENAME6).Range("A:C"), 3, False)
Range("O7:O17").VALUE = APPCT
Range("N7").Select
APVAL = Application.VLOOKUP(Range("A7:A17").VALUE, WKBOOKSTAB2.Sheets(FILENAME6).Range("A:B"), 2, False)
Range("N7:N17").VALUE = APVAL

'CHARACTERISTIC STABILITY
Dim APATT1 As Variant
Dim APATT2 As Variant
Dim APATT3 As Variant
Dim APATT4 As Variant
Dim APATT5 As Variant
Dim APATT6 As Variant
Dim APATT7 As Variant
Dim APATT8 As Variant
Dim APATT9 As Variant
Dim APATT10 As Variant
Dim APATT11 As Variant
Dim APATT12 As Variant
Dim APATT13 As Variant
Dim APATT14 As Variant
Dim APATT15 As Variant



'Windows.Workbook(WKBOOKATT2).Activate
Workbooks.Open (SASPATH & "PP_1_ATT_TEST_PRIME_" & Left(Format(DT, "MMM YYYY"), 3) & "_" & Right(Format(DT, "MMM YYYY"), 4) & ".XLS")
Range("F2").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-5])"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F14")
Range("F2:F14").Select
Range("B2:B14").Select
Selection.Copy
Range("G2").Select
ActiveSheet.Paste
Range("F2:F14").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Windows( _
"TEST Prime PP1.xls"). _
Activate
Sheets("PP1 Characteristic Stability").Select
Range("D20").Select
APATT1 = Application.VLOOKUP(Range("A20").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("F:G"), 2, False)
Range("D20").VALUE = APATT1
Range("D21").Select
APATT2 = Application.VLOOKUP(Range("A21").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("D21").VALUE = APATT2
Range("D26").Select
APATT3 = Application.VLOOKUP(Range("A20:A43").VALUE, WKBOOKATT3.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("D26").VALUE = APATT3
Range("D27").Select
APATT4 = Application.VLOOKUP(Range("A20:A43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("D27").VALUE = APATT4
Range("D32").Select
APATT5 = Application.VLOOKUP(Range("A20:A43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("D32").VALUE = APATT5
Range("D37").Select
APATT6 = Application.VLOOKUP(Range("A20:A43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("D37").VALUE = APATT6
Range("D38").Select
APATT7 = Application.VLOOKUP(Range("A20:A43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("D38").VALUE = APATT7
Range("D42").Select
APATT8 = Application.VLOOKUP(Range("A20:A43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("D42").VALUE = APATT8
Range("K19").Select
APATT9 = Application.VLOOKUP(Range("H19:H43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("K19").VALUE = APATT9
Range("K21").Select
APATT10 = Application.VLOOKUP(Range("H19:H43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("K21").VALUE = APATT10
Range("K22").Select
APATT11 = Application.VLOOKUP(Range("H19:H43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("K22").VALUE = APATT11
Range("K27").Select
APATT12 = Application.VLOOKUP(Range("H19:H43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("K27").VALUE = APATT12
Range("K28").Select
APATT13 = Application.VLOOKUP(Range("H19:H43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("K28").VALUE = APATT13
Range("K32").Select
APATT14 = Application.VLOOKUP(Range("H19:H43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("K32").VALUE = APATT14
Range("K37").Select
APATT15 = Application.VLOOKUP(Range("H19:H43").VALUE, WKBOOKATT2.Sheets(FILENAME2).Range("A:B"), 2, False)
Range("K37").VALUE = APATT15




'COPYING GINI ACROSS
Workbooks("TEST Prime PP1.XLS").Sheets("PP1 -Predictive Acc").Range("E8").VALUE = WKBOOKGINI2.Sheets(FILENAME10).Range("A2").VALUE

Windows("TEST Prime PP1.XLS"). _
Activate
Sheets("PP1 Characteristic Stability").Range("C22").VALUE = WKBOOKATT2.Sheets(FILENAME2).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C28").VALUE = WKBOOKATT2.Sheets(FILENAME2).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C33").VALUE = WKBOOKATT2.Sheets(FILENAME2).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C39").VALUE = WKBOOKATT2.Sheets(FILENAME2).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C44").VALUE = WKBOOKATT2.Sheets(FILENAME2).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J23").VALUE = WKBOOKATT2.Sheets(FILENAME2).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J29").VALUE = WKBOOKATT2.Sheets(FILENAME2).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J34").VALUE = WKBOOKATT2.Sheets(FILENAME2).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J39").VALUE = WKBOOKATT2.Sheets(FILENAME2).Range("D2").VALUE

''''''''''''''''TEST SUB

Windows("TEST Sub Prime PP1.XLS"). _
Activate

Sheets("TESTord Sub Prime Front Page").Range("G7").VALUE = DT
Sheets("PP1 Score Stability Indices").Range("C6") = "%" & Format(DT, "MMM YYYY")
Sheets("PP1 Score Stability Indices").Range("D6") = "Stability Index - Benchmark Vs " & Format(DT, "MMM YYYY")
Sheets("PP1 - Predictive Acc").Range("A6") = DT
Sheets("PP1 - Predictive Acc").Range("D11") = "ACT " & Format(DT, "MMM YYYY") & "BR"
Sheets("PP1 - Predictive Acc").Range("E11") = "EXP " & Format(DT, "MMM YYYY") & "BR"


'STABILITY INDEX
Dim ASPCT As Variant
Dim ASVAL As Variant

Windows( _
"TEST SUB Prime PP1.xls"). _
Activate
Sheets("PP1 Score Stability Indices").Select
ActiveWindow.SmallScroll Down:=-12
Range("O7").Select
ASPCT = Application.VLOOKUP(Range("A7:A17").VALUE, WKBOOKSTAB3.Sheets(FILENAME7).Range("A:C"), 3, False)
Range("O7:O17").VALUE = ASPCT
Range("N7").Select
ASVAL = Application.VLOOKUP(Range("A7:A17").VALUE, WKBOOKSTAB3.Sheets(FILENAME7).Range("A:B"), 2, False)
Range("N7:N17").VALUE = ASVAL


'COPYING GINI ACROSS
Workbooks("TEST Sub Prime PP1.XLS").Sheets("PP1- Predictive Acc").Range("E8").VALUE = WKBOOKGINI3.Sheets(FILENAME11).Range("A2").VALUE

'TOTALS FOR CHAR STABILITY
Windows("TEST Sub Prime PP1.XLS"). _
Activate
Sheets("PP1 Characteristic Stability").Range("C22").VALUE = WKBOOKATT3.Sheets(FILENAME3).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C28").VALUE = WKBOOKATT3.Sheets(FILENAME3).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C33").VALUE = WKBOOKATT3.Sheets(FILENAME3).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C39").VALUE = WKBOOKATT3.Sheets(FILENAME3).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C44").VALUE = WKBOOKATT3.Sheets(FILENAME3).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J23").VALUE = WKBOOKATT3.Sheets(FILENAME3).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J29").VALUE = WKBOOKATT3.Sheets(FILENAME3).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J34").VALUE = WKBOOKATT3.Sheets(FILENAME3).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J39").VALUE = WKBOOKATT3.Sheets(FILENAME3).Range("D2").VALUE


''''''''''''''''DDD PRIME

Windows("DDD Prime PP1.XLS"). _
Activate

Sheets("DDD Prime Front Page").Range("G7").VALUE = DT
Sheets("PP1 Score Stability Indices").Range("C6") = "%" & Format(DT, "MMM YYYY")
Sheets("PP1 Score Stability Indices").Range("D6") = "Stability Index - Benchmark Vs " & Format(DT, "MMM YYYY")
Sheets("PP1 - Predictive Acc").Range("A6") = DT
Sheets("PP1 - Predictive Acc").Range("D11") = "ACT " & Format(DT, "MMM YYYY") & "BR"
Sheets("PP1 - Predictive Acc").Range("E11") = "EXP " & Format(DT, "MMM YYYY") & "BR"

'STABILITY INDEX
Dim BPCT As Variant
Dim BSVAL As Variant

Windows( _
"DDD PRIME PP1.xls"). _
Activate
Sheets("PP1 Score Stability Indices").Select
ActiveWindow.SmallScroll Down:=-12
Range("O7").Select
BPCT = Application.VLOOKUP(Range("A7:A17").VALUE, WKBOOKSTAB4.Sheets(FILENAME8).Range("A:C"), 3, False)
Range("O7:O17").VALUE = BPCT
Range("N7").Select
BSVAL = Application.VLOOKUP(Range("A7:A17").VALUE, WKBOOKSTAB4.Sheets(FILENAME8).Range("A:B"), 2, False)
Range("N7:N17").VALUE = BSVAL


'COPYING GINI ACROSS
Workbooks("DDD Prime PP1.XLS").Sheets("PP1 -Predictive Acc").Range("E8").VALUE = WKBOOKGINI4.Sheets(FILENAME12).Range("A2").VALUE



'TOTALS FOR CHAR STABILITY
Windows("DDD Prime PP1.XLS"). _
Activate
Sheets("PP1 Characteristic Stability").Range("C22").VALUE = WKBOOKATT4.Sheets(FILENAME4).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C28").VALUE = WKBOOKATT4.Sheets(FILENAME4).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C33").VALUE = WKBOOKATT4.Sheets(FILENAME4).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C39").VALUE = WKBOOKATT4.Sheets(FILENAME4).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("C44").VALUE = WKBOOKATT4.Sheets(FILENAME4).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J23").VALUE = WKBOOKATT4.Sheets(FILENAME4).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J29").VALUE = WKBOOKATT4.Sheets(FILENAME4).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J34").VALUE = WKBOOKATT4.Sheets(FILENAME4).Range("D2").VALUE
Sheets("PP1 Characteristic Stability").Range("J39").VALUE = WKBOOKATT4.Sheets(FILENAME4).Range("D2").VALUE

'CLOSE ALL REFERENCED WORKBOOKS
WKBOOKATT1.Close
WKBOOKATT2.Close
WKBOOKATT3.Close
WKBOOKATT4.Close
'WKBOOKATT5.Close
'WKBOOKATT6.Close
WKBOOKSTAB1.Close
WKBOOKSTAB2.Close
WKBOOKSTAB3.Close
WKBOOKSTAB4.Close
'WKBOOKSTAB5.Close
'WKBOOKSTAB6.Close
WKBOOKGINI1.Close
WKBOOKGINI2.Close
WKBOOKGINI3.Close
WKBOOKGINI4.Close
'WKBOOKGINI5.Close
'WKBOOKGINI6.Close

'SAVING AND CLOSING SSS MONITORING
Windows("SSS PP1.XLS"). _
Activate
ChDir _
"C: \Blank Monitoring Pack"
ActiveWorkbook.SaveAs FILENAME:= _
"C:\ Blank Monitoring Pack \PP1 Monitoring Pack - SSS_" & Format(DT, "MMM YYYY") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'ActiveWorkbook.Close


'SAVING AND CLOSING TESTORD PRIME MONITOIRNG
Windows("TEST Prime PP1.XLS"). _
Activate
ChDir _
"C:\ Blank Monitoring Pack\"
ActiveWorkbook.SaveAs FILENAME:= _
"C:\PP1\ PP1 Monitoring Pack - TESTORD PRIME_" & Format(DT, "MMM YYYY")& ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'ActiveWorkbook.Close

'SAVING AND CLOSING TESTORD SUB PRIME MONITOIRNG
Windows("TEST Sub Prime PP1.XLS"). _
Activate
ChDir _
"C:\Blank Monitoring Pack"
ActiveWorkbook.SaveAs FILENAME:= _
"C:\Monitoring Packs\PP1\Behavioural Scorecard PP1 Monitoring Pack - TESTORD SUB PRIME_"& Format(DT, "MMM YYYY") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'ActiveWorkbook.Close


'SAVING AND CLOSING DDD PRIME MONITOIRNG
Windows("DDD Prime PP1.XLS"). _
Activate
ChDir _
"C:\Blank Monitoring Pack"
ActiveWorkbook.SaveAs FILENAME:= _
"C:\PP1\PP1 Monitoring Pack - DDD PRIME_" & Format(DT, "MMM YYYY") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'ActiveWorkbook.Close

End Sub
 
Upvote 0
I can definitely see one problem - VLOOKUP can't be used to lookup multiple values like you have here.

Code: 
ASPCT = Application.VLOOKUP(Range("A7:A17").VALUE, WKBOOKSTAB3.Sheets(FILENAME7).Range("A:C"), 3, False)

That wouldn't cause the error you posted.

One thing that could is opening so many workbooks, 24+ I think but I lost count, at one time.

Do you actually need to do that?

You don't even seem to do anything with some of the workbooks you've opened apart from close them again and some of them you don't close.

For example WKBOOKPRED1 (which is declared twice!!) is opened, you do nothing with it and don't close it.

There's also a lot of selecting/activating that isn't needed and just confuses things.

I'll take a closer look at the code.

PS Please use codes tags - if you click the link in my signature below this post you'll find out how to do it. Other forums use the same/similar tag.
 
Upvote 0
Hi Norie,

Thanks for your reply. This code is not complete and all the opened spreadseets are required to feed into the main spreadsheet. The ones that I don't do anything with / don't close are eventually going to be used. Unfortunately because of the way the statistical code (in SAS) outputs I cannot limit the number of spreadsheets referenced.

Also, I've used a similar vlookup previously in another bit of code. Could you please explain what part of the vlookup is incorrect.

Please let me know if you require any further details.

Thanks again.
 
Upvote 0
Do you need to have all the workbooks open at the same time?

If you need to open a workbook to get data then open, get the data and close it.

Also, if you aren't going to use a workbook don't open it.

The more workbooks you have open at one time is going to slow things down and cause problems.

As for the VLOOKUP I don't think I've ever seen one that lookup up multiple values.

How does it work? What is it you are trying to look up? Does it return multiple values?

When I try it on a worksheet I get a result that doesn't make sense.

Also, it only returned a value if I used a range of 7 or more cells, any less and I got a #VALUE! error.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
I have a "Runtime error 440 : The object not support this method" in my macro
Replies
5
Views
427
Joe4
Joe4
K
  • Question
Modifying Existing VBA Code to add 2 additional data columns
Replies
0
Views
148
keranali
K
M
  • Question
automate the sheet
Replies
0
Views
220
mwvirk
M
L
  • Question
Copy daily changing rows from a workbook paste to another workbook
Replies
1
Views
479
OaklandJim
O
M
  • Solved
Could you help me please with "run time error 1004 size property of the font class"
Replies
4
Views
361
DanteAmor
DanteAmor

Forum statistics

Threads
1,215,150
Messages
6,123,312
Members
449,094
Latest member
Chestertim

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top