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

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

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

So effectively, your code has no problems and there was no reason to post?

I believe you are wrong on the last point as a minimum, but if you aren't
having any problems, then so be it.

--
Regards,
Tom Ogilvy


"JVLin" wrote in message
...
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

I managed to fix the problem by incorporating the SourceFile and TargetFile
definition into the step leading up to the sub. (Note: also changed
xlPasteValuesAndNumberFormats).

The code simplifies to:

'Copy FieldNamesRange to FieldNamesDestination
rgFieldNamesRange.copy
rgFieldNamesDestination.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Transpose:=stTranspose
'Copy DataRange to DataDestination
rgDataRange.copy
i = rgDataRange.Rows.Count
j = rgDataRange.Columns.Count
If stBelow = True Then
If stTranspose = True Then
rgDataDestination.Cells((Run - 1) * j + 1, 1).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Transpose:=stTranspose
Else
rgDataDestination.Cells((Run - 1) * i + 1, 1).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Transpose:=stTranspose
End If
Else
If stTranspose = True Then
rgDataDestination.Cells(1, (Run - 1) * i + 1).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Transpose:=stTranspose
Else
rgDataDestination.Cells(1, (Run - 1) * j + 1).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Transpose:=stTranspose
End If
End If
Application.CutCopyMode = False

Regards,
JvL