Doing a copy-paste offest between 2 workbooks
 

Here is what I am doing: Counting each of the files in the directory,
then I am taking the heading in cell A2 a pasting it into my workbook
(that is serving as a data collection worksheet). Each time I offset
the heading by 7 since the range of data I am copying in is 7 rows. I
can't seem to figure out how to get the range to copy over and offset
itself. Any help is appreciated!

Sub TravelCompile()
Dim WB$(30)
Set sPg = ThisWorkbook.Sheets("Summary")
FileLocation = "O:\FINANCE\Finance190\Common\Planning&Analysi s
\Monthly Summary\May 2007\Travel"
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set the folder that contains all the files
Set Folder = objFSO.getfolder(FileLocation)
'Create a file collection
Set Location = Folder.Files
i = 0
For Each File In Location
If Right(File, 3) = "xls" Then
i = i + 1
WB$(i) = Mid(File, Len(FileLocation & "\") + 1, Len(File))
End If
Next
For x = 1 To i
Set cur = Workbooks.Open(Filename:=FileLocation & "\" & WB$
(x))
Set fPg = cur.Sheets(1)
With sPg
With .Range("A7")
.Offset((x - 1) * 7, 0) = fPg.Range("A2")
End With
With fPg.Range("G96:AE103")
.Select
End With
Selection.Copy
.Windows("TravelFees Template Macro.xls").Activate
.Range("b7").Select
.ActiveSheet.Paste
End With
'fPg.PrintOut
cur.Close
Next x
End Sub