Aggregating data

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" <JVLin@discussions.microsoft.com> wrote in message
news:3FB24CE2-4892-424C-A161-F9F21CCEAA51@microsoft.com...
> 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" <JVLin@discussions.microsoft.com> wrote in message
> news:3FB24CE2-4892-424C-A161-F9F21CCEAA51@microsoft.com...
> > 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" <JVLin@discussions.microsoft.com> wrote in message
news:2BD82CA0-96CB-4E23-9E32-04AB7952ED96@microsoft.com...
> 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" <JVLin@discussions.microsoft.com> wrote in message
> > news:3FB24CE2-4892-424C-A161-F9F21CCEAA51@microsoft.com...
> > > 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
> > >
> >
> >
> >

Tom, you're right about the i and j and different shaped ranges. However,
because I'm using ranges that are all the same size and the data is pasted to
correct ranges, this is not where the problem lies. The problem is that when
one set of data is pasted, the previous set is deleted.

And of course I wouldn't post if there was no problem. I've described the
problem, but if you don't want to take the time to think it through, don't
waste mine.

So, Tom the most helpful thing you can do in my view, is not reply to my
questions: I'm not finding your comments helpful.

Regards,
jvl

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