Problem with macro bringing in extra rows on summary sheet

[52kfm]

I've written a code to bring in data from multiple worksheets and display it onto a summary sheet. (Thanks to all of the code examples on here by the way, they are invaluable for self teaching VBA noobs like me).

The code clears the data sheet of all data bar the headers then brings in specific cells from a range of worksheets, in this example there are 8 but other sheets have upto 120 discrete results coming into the summary for each worksheet. Up to 400 worksheets are being looked at each time round.

The problem is it also brings in between 3 and 6 rows of nonsense data in the top rows of the summary sheet, hence the instruction to delete rows 2-7 in the code. But this is not working either as I'm coding on a machine using Excel 2007 and getting one result but the code is running on a machine using excel 2003 and pulling a different number of rows nonsense data. Can anyone suggest why its not pulling in only the data I'm looking for and a fix to this code to stop it happening because I'm getting variable results depending on the machine I'm using and it's driving me nutty.

Thanks in advance

Sub Summarise_Master_Data()
On Error Resume Next
Sheets("Master").Select
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim rng As Range
    Dim i As Integer, counter As Integer
    'Set the range to evaluate for clearing all bar the column headers.
    Set rng = Sheets("Master").Range("a2:k1000")
    'initialize i to 1
    i = 1
    'Loop for a count of 1 to the number of rows
    'in the range that you want to clear.
    For counter = 1 To rng.Rows.Count
        'If cell i in the range contains a blank,
        'delete the row.
        'Else increment i
        'this clears all the rows leaving a blank sheet
        If rng.Cells(i) <> "" Then
            rng.Cells(i).Cells.Delete
        Else
            i = i + 1
        End If
 
    Next
 
    For Each ws In Worksheets
    If ws.Name <> "General" Or ws.Name <> "Internal" Or ws.Name <> "External" Or ws.Name <> "HHSRS" Or ws.Name <> "List" Or ws.Name <> "Template" Or ws.Name <> "Master" Or ws.Name <> "Lists" Then
        With Sheets("Master").Cells(Rows.Count, 1).End(xlUp)(2)
            .Value = ws.Range("a1")
            .Offset(, 1).Value = ws.Range("e4")
            .Offset(, 2).Value = ws.Range("e5")
            .Offset(, 3).Value = ws.Range("e6")
            .Offset(, 4).Value = ws.Range("e7")
            .Offset(, 7).Value = ws.Range("e8")
            .Offset(, 8).Value = ws.Range("e9")
            .Offset(, 9).Value = ws.Range("g125")
            .Offset(, 10).Value = ws.Range("c125")
 
 
 
        End With
    End If
Next ws
    Rows("2:7").Select
    Selection.Delete Shift:=xlUp
Dim lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
For k = 1 To lRow
    Cells(k, 1) = Cells(k, 2) & " " & Cells(k, 3)
Next k
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Full Address"
Application.ScreenUpdating = True
End Sub

 

[Eurekaonide]

Hi

I'm no guru here, but I do have some code doing a similar thing and I use the following;

Dim NextRowCount
 
TempCount = 1
 
RowCount1 = 31 '31 is the starting row on the summary sheet
 
'then after all your code has executed
 
rowcount1 = rowcount1+1
 
'and/or
 
NextRowCount = RowCount1+1

This tends to put the next set of code onto the next line, sometimes I want a blank row between data so I just up the count RowCount1+2 etc etc.

I can also have several different RowCounts for where I'm merging lots of data from several sheets in a similar area.

Hope it helps

 

[52kfm]

Thanks Eurekaonide
Where in the code example would I replace my code with yours, I see the logic of your reply but I can't get it to play nicely, It seems to be affecting the columns the data is returning to.
Thanks