Merging Documents

[Massy]

Hi

Following on from yesterdays post - I have a workbook with
25 Worksheets, a menu sheet and a sheet for every month in
2 years

The Month sheets are set out as follows:

BkRef Name DepDate SerDate Supplier ShName Curr Amt Notes

The Supplier column is a vlidation drop list which takes
its values from range L2:L22

ShName and Curr are lookup formulas from the supplier
field and I have copied these formulas down to Row 100.

What I need to do is merge this document (weekly) with the
main document which is set out exactly the same. Not all
Sheets will have data on them so I need to check if there
is data on the sheet before I copy it, I have pasted my
code so far below. I tried adding an if statement to check
if stament to check if lr was less than 2 but It wasnt
working. Can anyone help?

Thanks so much

Dim x As Integer
Dim lr As Long
Dim cr As Long
Dim currentwb As Workbook

Set currentwb = ActiveWorkbook
Set Main = Workbooks.Open("S:\Kingston\fa\overseas
payments\long haul\2004
\austravel\prepayments\Prepay_Main.xls")
For x = 2 To 26
Workbooks(currentwb).Activate
Worksheets(x).Activate
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:I" & lr).Copy
Workbooks("Prepay_Main.xls").Activate
Worksheets(x).Select
cr = Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.Paste
Next x

End Sub

 

[Jim Rech]

Just looking at your macro I think this improves it and adds a check for
data. I'm sure you'll have to tweak it. You'll notice that I got rid of
the "selecting" you were doing as it's not necessary and slows things down.
Also I'm copy an in-line copy rather than separate copy/pastes.

Sub Example()
Dim x As Integer
Dim lr As Long
Dim CurrentWB As Workbook
Dim DestWB As Workbook

Set CurrentWB = ActiveWorkbook
Set DestWB = Workbooks.Open("c:\test.xls")
For x = 2 To 26
With CurrentWB.Sheets(x)
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
If lr > 2 Then
.Range("A2:I" & lr).Copy _
DestWB.Sheets(x).Range("A65536").End(xlUp)
End If
End With
Next x
End Sub



--
Jim Rech
Excel MVP
| Hi
|
| Following on from yesterdays post - I have a workbook with
| 25 Worksheets, a menu sheet and a sheet for every month in
| 2 years
|
| The Month sheets are set out as follows:
|
| BkRef Name DepDate SerDate Supplier ShName Curr Amt Notes
|
| The Supplier column is a vlidation drop list which takes
| its values from range L2:L22
|
| ShName and Curr are lookup formulas from the supplier
| field and I have copied these formulas down to Row 100.
|
| What I need to do is merge this document (weekly) with the
| main document which is set out exactly the same. Not all
| Sheets will have data on them so I need to check if there
| is data on the sheet before I copy it, I have pasted my
| code so far below. I tried adding an if statement to check
| if stament to check if lr was less than 2 but It wasnt
| working. Can anyone help?
|
| Thanks so much
|
| Dim x As Integer
| Dim lr As Long
| Dim cr As Long
| Dim currentwb As Workbook
|
| Set currentwb = ActiveWorkbook
| Set Main = Workbooks.Open("S:\Kingston\fa\overseas
| payments\long haul\2004
| \austravel\prepayments\Prepay_Main.xls")
| For x = 2 To 26
| Workbooks(currentwb).Activate
| Worksheets(x).Activate
| lr = Cells(Rows.Count, "A").End(xlUp).Row
| Range("A2:I" & lr).Copy
| Workbooks("Prepay_Main.xls").Activate
| Worksheets(x).Select
| cr = Cells(Rows.Count, "A").End(xlUp).Row
| ActiveSheet.Paste
| Next x
|
| End Sub
|
|

 

[Massy]

Hi

Thanks so much for this - it works well with one slight
problem.

When I copy it to the Destwb it overwrites the column
headings. Instead of copying to row 1 can it start it row
2?

I know I need to amend this line something like this but
it wont work for me.

DestWB.Sheets(x).Range("A65536").End(xlUp).Offset(1,0)

 

[Jim Rech]

Well, I think you made the right correction. I'd change it to this since
you don't need to specify a column offset, but your change should have
worked:

DestWB.Sheets(x).Range("A65536").End(xlUp).Offset(1)

--
Jim Rech
Excel MVP
| Hi
|
| Thanks so much for this - it works well with one slight
| problem.
|
| When I copy it to the Destwb it overwrites the column
| headings. Instead of copying to row 1 can it start it row
| 2?
|
| I know I need to amend this line something like this but
| it wont work for me.
|
| DestWB.Sheets(x).Range("A65536").End(xlUp).Offset(1,0)
|
|
| >-----Original Message-----
| >Just looking at your macro I think this improves it and
| adds a check for
| >data. I'm sure you'll have to tweak it. You'll notice
| that I got rid of
| >the "selecting" you were doing as it's not necessary and
| slows things down.
| >Also I'm copy an in-line copy rather than separate
| copy/pastes.
| >
| >Sub Example()
| > Dim x As Integer
| > Dim lr As Long
| > Dim CurrentWB As Workbook
| > Dim DestWB As Workbook
| >
| > Set CurrentWB = ActiveWorkbook
| > Set DestWB = Workbooks.Open("c:\test.xls")
| > For x = 2 To 26
| > With CurrentWB.Sheets(x)
| > lr = .Cells(.Rows.Count, "A").End(xlUp).Row
| > If lr > 2 Then
| > .Range("A2:I" & lr).Copy _
| > DestWB.Sheets(x).Range("A65536").End
| (xlUp)
| > End If
| > End With
| > Next x
| >End Sub
| >
| >
|