Macro Variable defination problem

Lynxador

Board Regular
Joined
Jan 6, 2005
Messages
125
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.

Code:
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
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

Board Regular
Joined
Jan 6, 2005
Messages
125
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

Well-known Member
Joined
Nov 4, 2003
Messages
960
See if this works at all. I'm not entirely familiar with this method you are using, but maybe this works?
Code:
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

Board Regular
Joined
Jan 6, 2005
Messages
125

ADVERTISEMENT

you sir.. are my hero
 

Lynxador

Board Regular
Joined
Jan 6, 2005
Messages
125
well actually.. runtime error 438: object doesn't support this property or method

than hilighted in yellow

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

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960

ADVERTISEMENT

Try:
Code:
Thisworkbook.Cells.Copy ActiveWorkbook.Sheets("Sheet2").Range("A1")
HTH.
 

Lynxador

Board Regular
Joined
Jan 6, 2005
Messages
125
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

Well-known Member
Joined
Nov 4, 2003
Messages
960
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?
 

Forum statistics

Threads
1,147,846
Messages
5,743,521
Members
423,801
Latest member
paulj4177

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
Top