Using "i" in File Names
 

I would like to fully automate copying the data from a few Excel file
and pasting the data from each onto separate sheets on one file.
would like to do it from 1-30per. I have no idea how to do it, but thi
is my attempt that is doomed to fail:

Do While Rng <= 30
For i = 1 To Rng
Windows(out13_T300K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "300"
Windows(out13_T600K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "600"
Windows(out13_T900K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "900"
Call Grapher
Next i
Loo

--
Loss
-----------------------------------------------------------------------
Losse's Profile: http://www.excelforum.com/member.php...fo&userid=2481
View this thread: http://www.excelforum.com/showthread.php?threadid=38802

Using "i" in File Names
 

One of the ways that I transfer data is code like this:
(watch out for word wrap, each of these should be a
single line. The line continuation _ makes this so).
Note that there is no selection which makes the code faster.

Workbooks("MyBook").Sheets("MySheet").Range("A:B") . Copy _
Destination:= Workbooks("Booknext").Range("A1")

or
Workbooks("Booknext").Range("A:B")= _
Workbooks("MyBook").Sheets("MySheet").Range("A:B")


--
steveB

Remove "AYN" from email to respond
"Losse" wrote in message
...

I would like to fully automate copying the data from a few Excel files
and pasting the data from each onto separate sheets on one file. I
would like to do it from 1-30per. I have no idea how to do it, but this
is my attempt that is doomed to fail:

Do While Rng <= 30
For i = 1 To Rng
Windows(out13_T300K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "300"
Windows(out13_T600K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "600"
Windows(out13_T900K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "900"
Call Grapher
Next i
Loop


--
Losse
------------------------------------------------------------------------
Losse's Profile:
http://www.excelforum.com/member.php...o&userid=24813
View this thread: http://www.excelforum.com/showthread...hreadid=388024

Using "i" in File Names
 

You need some sort of loop, so if your files have a naming convention it is
easy.

In addition, if you want to add to separate sheets, you can just move them

Set wb = Activeworkbook
For i = 1 To 30
Workbooks.Open Filename:="myFile " & i & ".xls"
ActiveWorkbook.Worksheets("Sheet1").Copy _
After:=wb.Worksheets(wb.Worksheets.Count)
ActiveWorkbook.Close savechanges:=False
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Losse" wrote in message
...

I would like to fully automate copying the data from a few Excel files
and pasting the data from each onto separate sheets on one file. I
would like to do it from 1-30per. I have no idea how to do it, but this
is my attempt that is doomed to fail:

Do While Rng <= 30
For i = 1 To Rng
Windows(out13_T300K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "300"
Windows(out13_T600K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "600"
Windows(out13_T900K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "900"
Call Grapher
Next i
Loop


--
Losse
------------------------------------------------------------------------
Losse's Profile:
http://www.excelforum.com/member.php...o&userid=24813
View this thread: http://www.excelforum.com/showthread...hreadid=388024

Using "i" in File Names
 

And to use a variable (Rng or i) in a workbook name, use a form like this:
Workbooks("out13_T300K_" & Rng & "per2000")
or
Windows("out13_" & i & "per2000").

***(IMPORTANT!)***
One more thing:
In your code, your While loop condition is While Rng<30, but you never
change the value of Rng. So if you go into the loop with Rng < 30 you will
loop forever (infinite loop - one sure way to make your code "hang"). You
need to adjust this but I am not sure how you want Rng to vary - should Rng
and i be the same? If so, just get rid of that While loop and use i wherever
you have Rng.
--
- K Dales


"STEVE BELL" wrote:

One of the ways that I transfer data is code like this:
(watch out for word wrap, each of these should be a
single line. The line continuation _ makes this so).
Note that there is no selection which makes the code faster.

Workbooks("MyBook").Sheets("MySheet").Range("A:B") . Copy _
Destination:= Workbooks("Booknext").Range("A1")

or
Workbooks("Booknext").Range("A:B")= _
Workbooks("MyBook").Sheets("MySheet").Range("A:B")


--
steveB

Remove "AYN" from email to respond
"Losse" wrote in message
...

I would like to fully automate copying the data from a few Excel files
and pasting the data from each onto separate sheets on one file. I
would like to do it from 1-30per. I have no idea how to do it, but this
is my attempt that is doomed to fail:

Do While Rng <= 30
For i = 1 To Rng
Windows(out13_T300K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "300"
Windows(out13_T600K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "600"
Windows(out13_T900K_Rngper2000).Activate
Columns("A:B").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows(out13_iper2000).Activate
sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = "900"
Call Grapher
Next i
Loop


--
Losse
------------------------------------------------------------------------
Losse's Profile:
http://www.excelforum.com/member.php...o&userid=24813
View this thread: http://www.excelforum.com/showthread...hreadid=388024

Using "i" in File Names
 

I did:

For i = 1 To 30
Workbooks.Open Filename:="out13_T600K_" & i & "per2000.dat"
ActiveSheet.Name = "600"
sheets.Add
ActiveSheet.Name = "300"
sheets.Add
ActiveSheet.Name = "900"
Workbooks.Open Filename:="out13_T300K_" & i & "per2000.dat"
Workbooks.Open Filename:="out13_T900K_" & i & "per2000.dat"
Workbooks("out13_T900K_" & i & "per2000.dat").sheets("out13_T900K_" & i
& "per2000").Range("A:B").Copy _
Destination:=Workbooks("out13_T600K_" & i &
"per2000").sheets("900").Range("A:B")
Workbooks("out13_T300K_" & i & "per2000.dat").sheets("out13_T300K_" & i
& "per2000").Range("A:B").Copy _
Destination:=Workbooks("out13_T600K_" & i &
"per2000").sheets("300").Range("A:B")
Next i
End Sub

and it works fine. I just need it to insert the tab/space between the
columns when it opens it.


--
Losse
------------------------------------------------------------------------
Losse's Profile: http://www.excelforum.com/member.php...o&userid=24813
View this thread: http://www.excelforum.com/showthread...hreadid=388024

Using "i" in File Names
 

In an attempt to get the columns spaced, I tried with no luck:

Workbooks.Open Filename:="out13_T600K" & i & "per2000.dat" _
, Tab:=True

How can I get the columns to separate the data?


--
Losse
------------------------------------------------------------------------
Losse's Profile: http://www.excelforum.com/member.php...o&userid=24813
View this thread: http://www.excelforum.com/showthread...hreadid=388024

Using "i" in File Names
 

It automatically opens the data so that everything is in one row. Ho
can I fix this on the macro code

--
Loss
-----------------------------------------------------------------------
Losse's Profile: http://www.excelforum.com/member.php...fo&userid=2481
View this thread: http://www.excelforum.com/showthread.php?threadid=38802