Macro Variable defination problem

[Lynxador]

i have a problem... one that im sure th answer is easy im just not seeing it. i am a fairly novice coder, so i do go about things the long way until i can at least get that down befor i move to more advanced stuff.

Sub copy_into_window()
'
' Macro7 Macro
' Macro recorded 18/01/2005 by Nick Kotila
'

'
    Let x = 1
    Let y = ""
    
    Do Until x = 23
        If x = 1 Then y = Windows("Hamilton Epicenter.xls").Activate
        If x = 2 Then y = Windows("L'assomption Epicenter.xls").Activate
        If x = 3 Then y = Windows("Laval Epicenter.xls").Activate
        If x = 4 Then y = Windows("Longueuil Epicenter.xls").Activate
        If x = 5 Then y = Windows("Mirabel Epicenter.xls").Activate
        If x = 6 Then y = Windows("Montreal Epicenter.xls").Activate
        If x = 7 Then y = Windows("Montreal-Nord Epicenter.xls").Activate
        If x = 8 Then y = Windows("Montreal-West Epicenter.xls").Activate
        If x = 9 Then y = Windows("Repentinguy-Le Gardeur Epicenter.xls").Activate
        If x = 10 Then y = Windows("Saint-Jean-sur-Richelieu Epicenter.xls").Activate
        If x = 11 Then y = Windows("Ste Dorthee Epicenter.xls").Activate
        If x = 12 Then y = Windows("St-Eustache Epicenter.xls").Activate
        If x = 13 Then y = Windows("St-Hubert Epicenter.xls").Activate
        If x = 14 Then y = Windows("Stoney Creek Epicenter.xls").Activate
        If x = 15 Then y = Windows("Terrebonne Epicenter.xls").Activate
        If x = 16 Then y = Windows("Victoriaville Epicenter.xls").Activate
        If x = 17 Then y = Windows("Granby Epicenter.xls").Activate
        If x = 18 Then y = Windows("Cowansville Epicenter.xls").Activate
        If x = 19 Then y = Windows("Chomedy Epicenter.xls").Activate
        If x = 20 Then y = Windows("Boisbriand Epicenter.xls").Activate
        If x = 21 Then y = Windows("Auteuil Epicenter.xls").Activate
        If x = 22 Then y = Windows("Ancaster Waterdown Dundus Epicenter.xls").Activate
        Windows("NEWSALESLIST.XLS").Activate
        Cells.Select
        Selection.copy
        y
        Sheets("Sheet2").Select
        Cells.Select
        ActiveSheet.Paste
        Application.Run "PERSONAL.XLS!Copydata"
        Let x = x + 1
    Loop

The problem i am having.. is you see the y all the way at the bottom, i know that doesn't work.. just im not sure how to define it so that it displays the worksheet funtion i set it too.

any help would be much appreciated.

 

[martinee]

The code runs through all this, but because it always ends with the last line of code, it will always return the last possible Y value. Perhaps if you explained what you inteded the code to do, we could help you some more. Consider revising your loop. HTH.

 

[Lynxador]

well... basically im trying to make a macro.. that takes a page, copies it over to every file i have in 1 folder on sheet 2.. than run a macro i ahve already built on each file.

 

[martinee]

See if this works at all. I'm not entirely familiar with this method you are using, but maybe this works?

Sub copy_data_window()
Dim x As Integer

For x = 1 To 22 Step 1
        If x = 1 Then Windows("Hamilton Epicenter.xls").Activate
        If x = 2 Then Windows("L'assomption Epicenter.xls").Activate
        If x = 3 Then Windows("Laval Epicenter.xls").Activate
        If x = 4 Then Windows("Longueuil Epicenter.xls").Activate
        If x = 5 Then Windows("Mirabel Epicenter.xls").Activate
        If x = 6 Then Windows("Montreal Epicenter.xls").Activate
        If x = 7 Then Windows("Montreal-Nord Epicenter.xls").Activate
        If x = 8 Then Windows("Montreal-West Epicenter.xls").Activate
        If x = 9 Then Windows("Repentinguy-Le Gardeur Epicenter.xls").Activate
        If x = 10 Then Windows("Saint-Jean-sur-Richelieu Epicenter.xls").Activate
        If x = 11 Then Windows("Ste Dorthee Epicenter.xls").Activate
        If x = 12 Then Windows("St-Eustache Epicenter.xls").Activate
        If x = 13 Then Windows("St-Hubert Epicenter.xls").Activate
        If x = 14 Then Windows("Stoney Creek Epicenter.xls").Activate
        If x = 15 Then Windows("Terrebonne Epicenter.xls").Activate
        If x = 16 Then Windows("Victoriaville Epicenter.xls").Activate
        If x = 17 Then Windows("Granby Epicenter.xls").Activate
        If x = 18 Then Windows("Cowansville Epicenter.xls").Activate
        If x = 19 Then Windows("Chomedy Epicenter.xls").Activate
        If x = 20 Then Windows("Boisbriand Epicenter.xls").Activate
        If x = 21 Then Windows("Auteuil Epicenter.xls").Activate
        If x = 22 Then Windows("Ancaster Waterdown Dundus Epicenter.xls").Activate
        Windows("NEWSALESLIST.XLS").Cells.Copy _
            ActiveWorkbook.Sheets("Sheet2").Range("A1")
        Application.Run "PERSONAL.XLS!Copydata"
Next x

End Sub

HTH

 

[Lynxador]

you sir.. are my hero

 

[Lynxador]

well actually.. runtime error 438: object doesn't support this property or method

than hilighted in yellow

Windows("NEWSALESLIST.XLS").Cells.copy _
            ActiveWorkbook.Sheets("Sheet2").Range("A1")

 

[martinee]

Try:

Thisworkbook.Cells.Copy ActiveWorkbook.Sheets("Sheet2").Range("A1")

HTH.

 

[Lynxador]

i have only 1 more question... is there a way to stop or pause the macro at teh end of the loop for like 2 seconds, to let the other macro run? i think im having a problem of the sheets being skipped because the other macro is still working.

 

[martinee]

I'm quite unsure of this, again, I am not familiar with this method as I don't use a personal.xls file. Perhaps somebody else does?