Hi, I've been reading this forum for a while, but this is my first post. 
I've created in macro in Excel 2003 that uses Application.FileSearch to open all Excel files in a specified directory. I've used a variable (vinputfile) to hold name of the open file and the macro then copies cells from vinputfile, activates a master sheet (from which the macro is run), and pastes the copied data into the mastersheet. The copy and paste process is then repeated switching between vinput file and the mastersheet until all the relevant information has been copied into the mastersheet, at which point the 'vinputfile' is closed. the macro then repeats the process for each Excel file in turn, before finally formatting the master sheet.
Everything works fine in Excel 2003, but the macro also needs to run on Excel 2007. I realise that Application.FileSeach can't be used with 2007 so I've looked for alternatives online and although there are lots of examples, I'm a novice with VBA I can't seem to get any of them to work with my code!
My current working Excel 2003 macro is below (i've cut out most out the copy and paste part as it just repeats itself with different cell reference).
Any help would be greatly appreciated as I've been battling with this for about a week now and I just seem to be going round in circles.
Many thanks,
Steph
I've created in macro in Excel 2003 that uses Application.FileSearch to open all Excel files in a specified directory. I've used a variable (vinputfile) to hold name of the open file and the macro then copies cells from vinputfile, activates a master sheet (from which the macro is run), and pastes the copied data into the mastersheet. The copy and paste process is then repeated switching between vinput file and the mastersheet until all the relevant information has been copied into the mastersheet, at which point the 'vinputfile' is closed. the macro then repeats the process for each Excel file in turn, before finally formatting the master sheet.
Everything works fine in Excel 2003, but the macro also needs to run on Excel 2007. I realise that Application.FileSeach can't be used with 2007 so I've looked for alternatives online and although there are lots of examples, I'm a novice with VBA I can't seem to get any of them to work with my code!
My current working Excel 2003 macro is below (i've cut out most out the copy and paste part as it just repeats itself with different cell reference).
Code:
Sub Copy_Forms_To_Master()
'-----------------------------------------------------------------
'
' Copy_Forms_To_Master Macro
'
' Keyboard Shortcut: Ctrl+r
'
' Macro will look for Excel files in a specified directory.
' The contents of the spreadsheet will be copied and pasted into
' the master sheet (which contains the macro).
'
'-----------------------------------------------------------------
Dim i As Integer
Dim vinputfile As String 'Use variable to hold filename
'return to first blank row from bottom of sheet to ensure that any 'existing data is not overwritten.
Range("A65536").End(xlUp).Offset(1).Select
'Do not display page breaks as this slows performance of the macro
ActiveSheet.DisplayPageBreaks = False
'Open all workbooks in specified folder.
With Application.FileSearch
'Specify directory for input spreadsheets
.LookIn = "G:\My Documents\Excel\Processing"
'Only look for Excel files
.FileType = msoFileTypeExcelWorkbooks
'Excel workbook(s) have been found
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
'Stop screen flicker of workbooks being opened
Application.ScreenUpdating = False
Workbooks.Open (.FoundFiles(i))
v = Split(.FoundFiles(i), "\")
fname = v(UBound(v))
vinputfile = fname
'----------------------------------------------------
'Copy from input form(s) and paste into master sheet
'----------------------------------------------------
Range("C12:I12").Select
Selection.Copy
Windows("Master.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
ActiveCell.Offset(0, 1).Select
'most of copy and paste edited out
Windows(vinputfile).Activate
Range("C199:I199").Select
Selection.Copy
Windows("Master.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
ActiveCell.Offset(0, 1).Select
ActiveWorkbook.Save 'SL: Save the data copied to the master sheet
'*******************************
Windows(vinputfile).Activate
'Prevent'Do you want to save changes' message being displayed
Application.DisplayAlerts = False
ActiveWorkbook.Close
'Reset Application.DisplayAlert property
Application.DisplayAlerts = True
'Clear out filename variable
vinputfile = ""
'---------------------------------------------------------
'Loop through macro with next file in specified directory
Next i
'There are no wb's
Else
'Display message if there are no Excel files in directory
MsgBox "There are no workbooks to open", vbOKOnly
End If
End With
'---------------------------------------------------------
'Resize worksheet columns and remove borders in master sheet
Columns("A:CQ").Select
Columns("A:CQ").EntireColumn.AutoFit
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveWorkbook.Save
End SubAny help would be greatly appreciated as I've been battling with this for about a week now and I just seem to be going round in circles.
Many thanks,
Steph
