My first macro

[malpa]

Hi,

I've never done it. I´ve been reading a book, but I have some troubles.

I have 30 files.xls

Autopista - Tándem_AUTINT947_2005-09-01_2005-09-30.xls
Autopista - Tándem_MORA_2005-09-01_2005-09-30.xls
Autopista - Tándem_MORINT947_2005-09-01_2005-09-30.xls
Autopista - Tándem_MSABI_2005-09-01_2005-09-30.xls
...

I want to do this

1. Open each file then add a sheet at the begin of this file.
2. capture of each sheet the column called total (E1:E24)and paste it in the sheet added one behind another. each file contains at least 30 sheets.

file1.xls

new sheet1
sheet2 sheet3 sheet4 ....... sheet31
time Total Total Total
12:00 am 26,25 ... ....
01:00 .. 10,11 .... ....
...... .. .... ....
...... .. 5,39
...... .. 51,56
...... .. 39,78
10:00 pm 34,86 .... ... ....


file2.txt
new sheet
sheet2 sheet3 sheet4 ....... sheet31
time Total Total Total
12:00 am 26,25 ... ....
01:00 .. 10,11 .... ....
...... .. .... ....
...... .. 5,39
...... .. 51,56
...... .. 39,78
10:00 pm 34,86 .... ... ....


Sub ListarARchivos()
'
' Macro1 Macro
' Macro grabada el 01/11/2005 por usuario
'
Dim miFila As Integer
Dim miArchivo As String
Dim miHoja As Worksheet

miFila = 1
miArchivo = Dir("*.xls")
Do Until miArchivo = " "
Cells(miFila, 1) = miArchivo
Workbooks.Open Filename:= _
"C:\2005\PERFILES DE TRAFICO\200509_perfilesDiarios\miArchivo" #this doesn´t work
Sheets.Add
Range("B2").Select
Sheets(1).Select
Sheets(2).Select
Range("B1:B24").Select # time
Selection.Copy
Sheets("Hoja4").Select
ActiveSheet.Paste
For i = 2 To Worksheets.Count
Sheets(i).Select
Range("E1:E24").Select # total
Application.CutCopyMode = False
Selection.Copy
Sheets("Hoja4").Select
range.offset(0,1).select # this doesn´t work
ActiveSheet.Paste
Next i
miFila = miFila + 1
miArchivo = Dir
Loop

Thanks

malpa

 

[PHV]

Replace this:
"C:\2005\PERFILES DE TRAFICO\200509_perfilesDiarios\miArchivo" #this doesn´t work
With this:
"C:\2005\PERFILES DE TRAFICO\200509_perfilesDiarios\" & miArchivo

And take a look in the FAQ area to discover at least two ways to retrieve the last used row of a range.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[malpa]

Thanks so much.

It works.


malpa.