I have some basic code that we use to pull together data from several
worksheets within a single workbook. It should only pull in rows that have
data in column A and ignore the two rows used for headings from each of the
other sheet to avoid them being repeated all the way down this new page when
they are coppied across.

For the most part it works, however we noticed it seems to randomly add some
extra rows to the bottom of the consolidated list. Originally these were
blank but we now find that they are actually some of the rows from the very
first worksheet to be pulled in where no data exists in column A. No idea
why these appear at the end of the table nor why they are appearing in the
first place.

Code is as follows, any help to ensure it only copies across rows where there
is something in column A would be much appreciated. Even better would be
some pointers on how to get it to look at other workbooks hosted on
SharePoint....

(I started tying to add comments to help explain the function but not
finished yet)

Sub Merge()
Dim ws As Worksheet
'clear the current worksheet (i.e. consolidated plan) of all data except
the two header rows
ActiveSheet.UsedRange.Offset(2).Clear
'For all tabs in the workbook
For Each ws In ActiveWorkbook.Worksheets
'Exclude the ITA Internal sheet
If ws.Name < "ITA-Internal" Then
'Exclude the consolidated plan sheet
If ws.Name < ActiveSheet.Name Then
'When reviewing data to be copied exclude the first two rows
ws.UsedRange.Offset(2).Copy
'for all rows where there is a value in column A ....
With Range("A65536").End(xlUp).Offset(1, 0)
.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With
End If
End If
Next
End Sub