Macro Help

[Lee Swain]

I have daily excel files for 4 years worth of data that I need to
consolidate into 1 workbook. Each workbook contains 5 days worth of data so
I only have to open every 5th file. The data is in the same range in each
files (F16:J37) with dates across the top so I need to copy the data and
transpose it to the consolidated workbook.

I've created a macro to select the range and a macro to paste / special /
transpose but I'd like to be able to run a macro on 1 file to do the lot.
Is this possible?

Cheers

Lee

 

[Harald Staff]

Hi Lee

See
http://www.contextures.com/xlfaqMac.html#LoopBooks

HTH. Best wishes Hrald

 

[abmorgan]

Yes.

1. setup a list of files the maco is to work through in a new sheet. if
the filenames are quite consistent it shouldn't be to hard.
2. use the workbooks.open code to open the first file in the list. it
might be something like
activeworkbook.name = home
sheets("Files").select
range("A1").select
do until isempty(activecell)
workbooks.open "C:\Files" & Activecell.value & ".xls"

'then use your code to copy the data you need into a new sheet into the
home file
windows(home).activate
'paste etc, ensuring you select the cell ready for new info to be
copied in.

sheets("Files").select
activecell.offset(1,0).select
Loop 'moves one down the list and repeats until there are no files
left


hope this makes some sense.
the key to this is looping and structure.
if you have a consistent structure. ie every file is very similar you
should be fine with looping.

somethinglikeant

 

[Lee Swain]

Sorry guys but my macro writing skills are pretty crap and I can't figure
this out. Something I clearly need to work on.

My files are all called Rep2061.xls, Rep2062.xls etc and they're all located
in the same directory and the range I need to select is F16:J16. The macros
I've prepared are as follows:

' Macro1 Macro
' Macro recorded 08/05/2006 by Lee Swain
'
' Keyboard Shortcut: Ctrl+a
'
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
End Sub

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 08/05/2006 by Lee Swain
'
' Keyboard Shortcut: Ctrl+q
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
End Sub

I'm sure I should be able to combine these macros into 1 but I haven't been
able to do so yet.

Cheers for your help.

Lee