VBA - Unexpected row insert

Question

OBJECTIVE

Copy a group of rows from multiple worksheets and insert into a CONSOLIDATED worksheet.

APPROACH

Go to CONSOLIDATED worksheet and remove pre-existing information
Use an IF statement to find relevant worksheets where we intend to pull data from
Copy relevant columns and rows on each worksheet (e.g A6:G(lastrow))
Append copied data to CONSOLIDATED worksheet !!!ERROR

(ugly) CODE

Sub consolidateConvert()
Dim ws As Worksheet

'Set CONSOLIDATED as the active worksheet
Application.ScreenUpdating = False
Sheets("CONSOLIDATED").Activate


'Clear previous content from active sheet
ActiveSheet.Range("A1:G10000").ClearContents

'Iterate through workbooks, except for CONSOLIDATED, TITLE, and PIVOT worksheets
For Each ws In Worksheets
  If ws.Name <> "CONSOLIDATED" And ws.Name <> "PIVOT" And ws.Name <> "TITLE" _
     And ws.Name <> "APPENDIX - CURRENCY CONVERTER" And ws.Name <> "MACRO" Then

'Find last row of current worksheet
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

'Copy current worksheet cells and insert into CONSOLIDATED worksheet
    ws.Range("A6:G" & lastRow).Copy
    ActiveSheet.Range("A2").End(xlUp).Insert shift:=xlDown
  End If
Next ws

Call currencyConvert
Call addHeaders

currencyConvert is a function that isn't relevant to this particular question. However, I've added addHeaders function below:

Sub addHeaders()
Dim ws As Worksheet
Dim headers() As Variant

'Define worksheet and desired headers
Set ws = ThisWorkbook.Sheets("CONSOLIDATED")
headers() = Array("Fiscal Year", "Month", "Fiscal Month", "Month Year", "Unit", "Project", "Local Expense", "Base Expense")

'Insert row for header placement
Rows(1).Insert shift:=xlShiftDown

'Insert headers
With ws
  For i = LBound(headers()) To UBound(headers())
    .Cells(1, 1 + i).Value = headers(i)
  Next i
End With
End Sub

OUTPUT

Below is a screenshot of the unexpected output. Rows 2-7 are unexpected and include some random text strings that aren't present anywhere else within the workbook. Strings may be some weird inheritance issue in VBA but not quite sure (hence the question(s) below).

QUESTIONS

As mentioned before, Rows 2-7 are added unintentionally and are accompanied with some weird strings. Does anyone have any insight on why ~6 rows are being added (when only 1 should be added - see addHeaders())? Additionally, what is the origin of the unintended strings ("CatalogNickname", "EnvironmentKey", etc.)?

Has nothing to do with ws.Range("A6:G" & lastRow).Copy ActiveSheet.Range("A2").End(xlUp).Insert shift:=xlDown ?? — tjb1
– tjb1, Commented Nov 30, 2016 at 17:52
@tjb1 - no (I do not think so), that portion is looking at the other sheets, copying relevant cells and columns, and then inserting the cells into the consolidated worksheet. — jonplaca
– jonplaca, Commented Nov 30, 2016 at 17:56
I ran your code and did not have any unintended rows added to the CONSOLIDATED worksheet at all. It seemed to work fine. Perhaps you have other UDFs or an add-in or something else at work? — PeterT
– PeterT, Commented Nov 30, 2016 at 18:10
I am wondering something about this line: ws.Range("A6:G" & lastRow).Copy. Does lastRow ever return a value of <6? When I put ? Range("A6:G1").Address in the immediate window it returns $A$1:$G$6. — Brian
– Brian, Commented Nov 30, 2016 at 18:56
And are there 6 worksheets you are looping through? It may be copying a blank row for each worksheet. — Brian
– Brian, Commented Nov 30, 2016 at 18:59

user3598756 · Accepted Answer · 2016-11-30 21:36:23Z

1

your issues derives from use of Activate/ActiveSheet

you have to abandon this coding habit, that can subtly mislead you, and use fully qualified range references to assure you're acting on wanted workbook/worksheet range

here follows a refactoring of your code with such fully qualified range references and a "Value to Value" range copy instead of a Copy/Insert to considerably speed things up:

Option Explicit

Sub consolidateConvert()
    Dim ws As Worksheet
    Dim lastRow As Long

    With Worksheets("CONSOLIDATED") '<--| reference "CONSOLIDATED" worksheet
        .UsedRange.ClearContents '<--| clear its content

        'Iterate through workbooks
        For Each ws In Worksheets
            Select Case ws.Name
                Case "CONSOLIDATED", "PIVOT", "TITLE", "APPENDIX - CURRENCY CONVERTER", "MACRO" ' <--| discard "CONSOLIDATED", "TITLE", "PIVOT", "APPENDIX - CURRENCY CONVERTER" and "MACRO" worksheets
                    ' do nothing
                Case Else
                    'Find last row of current worksheet
                    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).row
                    'Copy current worksheet cells and insert into CONSOLIDATED worksheet
                    .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(lastRow - 5, 7).Value = ws.Range("A6:G" & lastRow).Value '<--| just copy values and speed thing up!
            End Select
        Next ws
        addHeaders .Name '<--| call AddHeaders passing reference worksheet name (i.e. "CONSOLIDATED")
    End With

    currencyConvert '<--| if it acts on "CONSOLIDATED" sheet, you may want to "treat" it as 'addHeaders': take it into 'End With' and pass it '.Name' as a parameter 

End Sub

Sub addHeaders(shtName As String)
    Dim headers As Variant

    headers = Array("Fiscal Year", "Month", "Fiscal Month", "Month Year", "Unit", "Project", "Local Expense", "Base Expense") '<--| Define desired headers
    ThisWorkbook.Worksheets(shtName).Range("A1").Resize(, UBound(headers) - LBound(headers) + 1).Value = headers '<--| write headers from cell A1 rightwards
End Sub

answered Nov 30, 2016 at 21:36

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jonplaca Over a year ago

Wow, this is noticeably faster. However, at .Cells(.Rows.Count,1).End(xlUp).Offset(1)... '<--| just copy values and speed things up! I get an Object Defined Error: 1004. Yet, despite this error, the values are properly pasted (however, the submodule just quits and doesn't proceed onto addHeaders .Name. I believe this might be an issue w.r.t ws.Range("A6:G" & lastRow).. but always find these errors the most difficult :(

jonplaca Over a year ago

"1" which shouldn't be the case since all ranges > 1. Note, when the function runs, it pastes all the proper information into CONSOLIDATED, but leaves 1, blank row at the top of the CONSOLIDATED sheet (prior to adding headers). I started to think that perhaps the function was also running on the CONSOLIDATE page in it's final stage, resulting in lastRow = 1, but the Select Case ws.Name...do nothing statement should exempt the CONSOLIDATED worksheet from being effected.

user3598756 Over a year ago

this means that the current ws column "E" has no data, thus resulting in lastRow = 1. Check ws.name when it errors out and see why that worksheet has no data in column "E"

Collectives™ on Stack Overflow

VBA - Unexpected row insert

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related