Macro Variable defination problem

Board Regular
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.

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.

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

you sir.. are my hero

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

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

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.

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?

1,221,410
Messages
6,159,720
Members
451,587
Latest member
srice33

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.

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

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