Hi Tom,

thanks for your answers.

I think the problem lies in the way I've used the rg*Destinations. They are
indeed set every time the sub's run, but I don't want the data I've copied
across in the prior run to be deleted. I've been thinking of naming the
destination ranges with an increment, but this seems laborious.

The use of run, i & j seems unproblematic, since the correct ranges are
selected to paste data into.

Re Run increments: Run is incremented with each new range that's copied and
I test to ensure multiple source sheets are selected, otherwise there's no
point in aggregating.

Re i and j to determine an offset: i and j are determined by the size of the
DataRange being copied (rows.count & columns.count respectively), so account
is taken of differently shaped source ranges.

jvl

"Tom Ogilvy" wrote:

It looks like placement all hinges on the value of Run and rgDataDestination
rgDataDestination is set each time the procedure is run. If it never
changes in terms of the cell it points to, then that shouldn't be a problem.

the other problem would be if run is never incremented.

Now i and j are used to determine an offset. However, if the size of the
data being copied is different each time, then this could be problematic
since this assumes equal size data.

--
Regards,
Tom Ogilvy




"JVLin" wrote in message
...
Hi,

I've put the below code together to aggregate data from a number of
workbooks. (The items not explicitly defined are inputs into the sub,
collected from a userform.)

Problem is: the data copied and pasted from the first source workbook gets
deleted when data from the second source workbook is copied and pasted
and
so on. In otherwords, instead of getting a target file with the data
aggregated, only the data from the last source workbook is showing. Is
this
because I use 'Set range'???

Many thanks in advance for your help.

Regards,
JvLin

Dim SourceFile As Workbook
Dim rgFieldNamesRange As Range
Dim rgDataRange As Range
Dim stTargetFile As String
Dim TargetFile As Workbook
Dim rgFieldNamesDestination As Range
Dim rgDataDestination As Range
Dim i As Integer
Dim j As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False

If Wkb.IsWkbOpen(stFullName) = False Then
Set SourceFile = Workbooks.Open(FileName:=stFullName, UpdateLinks:=0)
Else
Set SourceFile = Workbooks(stFileName)
End If
If Wkb.IsWkbOpen(stAnalysisFile) = False Then
Set TargetFile = Workbooks.Open(FileName:=stAnalysisFile,
UpdateLinks:=0)
Else
stTargetFile = Wkb.GetFileName(stAnalysisFile)
Set TargetFile = Workbooks(stTargetFile)
End If

With SourceFile.Sheets(stSheetName)
Set rgFieldNamesRange = .Range(stFieldNamesRange)
Set rgDataRange = .Range(stDataRange)
End With
With TargetFile.Sheets(stSheetName)
Set rgFieldNamesDestination = .Range(stFieldNamesDestination)
Set rgDataDestination = .Range(stDataDestination)
End With

If Run = 1 Then
rgFieldNamesRange.copy
rgFieldNamesDestination.PasteSpecial Transpose:=stTranspose
End If

rgDataRange.copy
If Run = 1 Then
rgDataDestination.PasteSpecial Transpose:=stTranspose
Else
i = rgDataRange.Rows.Count
j = rgDataRange.Columns.Count
If stBelow = True Then
If stTranspose = True Then
rgDataDestination.Offset((Run - 1) * j + 1, 0).PasteSpecial
Transpose:=stTranspose
Else
rgDataDestination.Offset((Run - 1) * i + 1, 0).PasteSpecial
Transpose:=stTranspose
End If
Else
If stTranspose = True Then
rgDataDestination.Offset(0, (Run - 1) * i + 1).PasteSpecial
Transpose:=stTranspose
Else
rgDataDestination.Offset(0, (Run - 1) * j + 1).PasteSpecial
Transpose:=stTranspose
End If
End If
End If

Application.CutCopyMode = False
Workbooks(stFileName).Saved = True
Workbooks(stFileName).Close

Application.DisplayAlerts = True
Application.ScreenUpdating = True