| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete emty file names from "recent documents" Excel | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Lost "File Menu" - now it's "Edit / View / Insert.." but no "F | Excel Discussion (Misc queries) | |||
| Backup to specific folder if workbook names begins with "NSR" or "MAC" | Excel Programming | |||
| Problem- Recording macros for "file save" and "File open" | Excel Programming | |||
![Losse[_24_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
