Two arrays within one loop

jeskit · June 23rd, 2011, 11:09 AM

Hi

I have the macro below, how i am having two issues with it. One when it gets to the line hightlighted in red it produces a "Object doesn't support this property or method" error message.

Also the second problem i am happening is regarding the line i am getting an error on. I need the loop to that it loops through the path array but then depending on the path number puts in to the Sheet array so they line up. But i am sure how to do this.

Code:

Option Explicit
Option Base 1

Sub CallConsolidationV1()

Dim CCTWB As Workbook, CCTLWB As Workbook
Dim CCTWBSht, CCTLWBCSht, CCTLWBCLSht As Worksheet
Dim strFldr, Path1, Path2, Path3, Path4, Path5, Path6, Path7, Path8 As String
Dim CCMDates, CCYDates, X, L, Y As String
Dim StartTime, EndTime As Double
Dim SuggestCMonth, SuggestCYear As Integer
Dim PathArray, SheetArray As Variant

'Define Variables
Set CCTWB = Application.Workbooks.Open("C:\Documents and Settings\SeymourJ\Desktop\CallConsolidationAnalysisTemplate.xlsx")
Set CCTWBSht = CCTWB.Sheets("Data")

strFldr = "C:\Documents and Settings\SeymourJ\Desktop"

Path1 = "P:\Bentley Global\Composite\Global\apd files\07 Configuration Documents\Support"
Path2 = "P:\HondaBikesUK\Composites\apd files\07 Configuration Documents\Support"
Path3 = "P:\HondaCarsUK\Composite\Client Customisation\apd files\07 Configuration Documents\Support"
Path4 = "R:\BMWMotarradAU\Composite\apd files\07 Configuration Documents\Support"
Path5 = "R:\HondaCarsEurope\Markets\Belgium\Composites\07 Configuration Documents\Support"
Path6 = "R:\HondaCarsEurope\Markets\Netherlands\Composites\07 Configuration Documents\Support"
Path7 = "R:\IMGCarsUK\Composites\apd files\07 Configuration Documents\Support"
Path8 = "R:\MAN_ERF_UK\Composites\apd files\07 Configuration Documents\Support"

StartTime = Timer

'Gather Consolidation Dates
CCMDates = Application.InputBox("Please enter Consolidation Month", SuggestCMonth)
    If CCMDates = "" Or 0 Then
        MsgBox "Please enter a month figure"
        CCMDates = Application.InputBox("Please enter Consolidation Month", SuggestCMonth)
    End If

CCYDates = Application.InputBox("Please enter Consolidation Year", SuggestCYear)
    If CCYDates = "" Or 0 Then
        MsgBox "Please enter a month figure"
        CCYDates = Application.InputBox("Please enter Consolidation Year", SuggestCYear)
    End If

'Add dates to template
CCTWBSht.Range("B2").Value = CCMDates
CCTWBSht.Range("B3").Value = CCYDates

'Define Path Array
PathArray = Array(Path1, Path2, Path3, Path4, Path5, Path6, Path7, Path8)
SheetArray = Array("BGBL", "HUBK", "HUK", "BMW", "HBE", "HNL", "IMG", "MAN")

'Loop though path files and copy data
For X = LBound(PathArray) To UBound(PathArray)

    Set CCTLWB = Workbooks.Open(PathArray(X) & "\" & "*TrackerLog.*")
    Set CCTLWBCSht = CCTLWB.Sheets("Commentary")

    L = WorksheetFunction.CountA(CCTLWBCSht.Range("A:A"))
    
    CCTLWBCSht.Select

    CCTLWBCSht.Range("A" & L & "N" & L).Copy Destination:=CCTWBSht.Sheets(SheetArray).Range("A1")        
Next X

EndTime = Timer

MsgBox "Macro Finished in " & Round((EndTime - StartTime) / 60, 2) & " minutes."

End Sub

gbianchi · June 23rd, 2011, 11:27 AM

Would be nice to see a red line somewhere :)

jeskit · June 23rd, 2011, 11:32 AM

Hi...sorry about that altered the text now, you should be able to see the red text now...sorry

gbianchi · June 23rd, 2011, 11:36 AM

Looks like you are trying to copy two differents formats (I mean the size are differents), so excel can't handle it..

it's that the case?

jeskit · June 23rd, 2011, 11:38 AM

The line in red should be taking the number from the worksheet function (L) and using it to give the line above the line number to copy so if L was 4 then it would be range a4:n4.

IIt should then copy that lien and paste it into cell A1 of the sheet which is defined in the sheet array

gbianchi · June 23rd, 2011, 12:06 PM

ahhh.. you are rigth.. the error is that you need a : to specify a range.. try with

CCTLWBCSht.Range("A" & L & ":N" & L).Copy

jeskit · June 23rd, 2011, 12:09 PM

hI

Thanks i had not noticed that!

However i am still getting the "Object doesn't support this property or method" error message.

jeskit · June 24th, 2011, 03:08 AM

Hi

I should have mentioned in my last post that i know why i am stilll getting that error message...Its to do with the fact i have sheet array in this line:

Code:

 Destination:=CCTWBSht.Sheets(SheetArray).Range("A1")

This is the 2nd issue i am having, i need to be able to have two arrays in the same loop, PathArray and SheetArray. But i am not sure how to do this., so i tried having putting sheetarray for the sheet name in a hope it would pick up the SheetArray Array but it hasn't.

I also figured it shouldn;t be CTTWBSht.sheets it should be CCTWB.sheets

gbianchi · June 24th, 2011, 09:00 AM

Well.. SheetArray is in fact an array, so what sheet are you expecting excel to use??

If your idea is use all of them, then you will have to manually round robin around all of them, and copy to all of them...

jeskit · June 24th, 2011, 09:15 AM

Ok

Sheet Array is what i want it to use.

What i need is a loop which which opens all the workbooks in the located in the PathArray. Then for each of them copy the data range ("A" & L & "N" & L) and paste it in to sheets listed in the Sheet Array.

I need it to do this so the data gets pasted in the correct sheet, but i want to use a loop so i dont have write the same code out multiple times.

I hope that makes sense, i am not very good at explaining things