VBA to merge worksheets (only populated rows)

[akscooby]

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

 

[akscooby]

Sorry, meant to post this in VBA section...

 

[Gord Dibben]

You are being bitten by Excel's "usedrange".

It is not always what you think it is.

For explanation and code to reset the usedrange see Debra Dalgleish's site.

http://www.contextures.on.ca/xlfaqApp.html#Unused

Just call DeleteUnused at beginning of your macro.


Gord Dibben MS Excel MVP

 

[akscooby via OfficeKB.com]

Thanks but have tried both macro and manual methods of ensuring the end of
the data, the funny thing is it appears to be only the last few rows of
those that have values in columns other than A, not every row like that.

Quite happy to dump this macro completely if someone can help put together
one which does what this almost does properly i.e.

1) Ignores the first few rows where the headings are on the consolidated
sheet
2) Removes everything already on it.
3) Checks all other specified sheets (or workbooks) for row where a value
exists in column A.
4) Copies only these rows (value and format) to the consolidated page.
5) Does not copy across the first few rows which are headings only.

 

[akscooby via OfficeKB.com]

Thanks but have tried both macro and manual methods of ensuring the end of
the data, the funny thing is it appears to be only the last few rows of
those that have values in columns other than A, not every row like that.

Quite happy to dump this macro completely if someone can help put together
one which does what this almost does properly i.e.

1) Ignores the first few rows where the headings are on the consolidated
sheet
2) Removes everything already on it.
3) Checks all other specified sheets (or workbooks) for row where a value
exists in column A.
4) Copies only these rows (value and format) to the consolidated page.
5) Does not copy across the first few rows which are headings only.