Using "i" in File Names

[Losse]

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

[STEVE BELL]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Losse]

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.

[K Dales]

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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
> >
>
>
>

[Losse]

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]

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