Macro Find Last Row and Add Data

Question

Each week I pull data for a series of items and generate a pivot table. Some weeks, no data exists for one or more of these items. When this is the case, that item won't appear on the report.

To avoid manually checking that no items have been accidentally excluded from the pivot table, I want to manually add "dummy" items to the data used in the pivot table.

I am pretty confident I can identify which dummy items to add by using COUNTIF between the master item sheet and the raw data. My challenge is that the number of rows in the pivot table varies week to week, so I need to 1) identify the last row of the raw data report, and 2) insert dummy rows below the last line.

Can anyone suggest a strategy using the example below?

Items in Data

AAA    
BBB    
DDD    
FFF

Items Not in Data to Manually Insert

CCC    
EEE

I am not getting any errors, but this isn't working.

Sub DUMMY_ITEMS()
'
' DUMMY_ITEMS Macro
Sheets("Operations").Select
Range("H2:V73").Select
Selection.Copy
Sheets("Raw Data").Select
Dim LastRow As Long
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub

If you are [finding the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920), check out the link. Adding data is not that hard. — L42, Jan 23 '17 at 22:17
So I'm not getting any errors, but this isn't working. Any help is really appreciated! 'Sub DUMMY_ITEMS() ' ' DUMMY_ITEMS Macro Sheets("Operations").Select Range("H2:V73").Select Selection.Copy Sheets("Raw Data").Select Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub' @l42; @tackgnol — ERKSMTY, Jan 24 '17 at 23:03

Tackgnol · Accepted Answer · 2017-01-25T18:54:33.853

Thanks for your code in the comment here is the fixed version:

Sub DUMMY_ITEMS()

Dim operationsSheet As Worksheet
Dim rawDataSheet As Worksheet
Dim copyRange As Range
Dim LastRow As Long

Set operationsSheet = Sheets("Operations")
Set rawDataSheet = Sheets("Raw Data")

operationsSheet.Range("H2:V73").Copy

With rawDataSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
rawDataSheet.Cells(LastRow, 1).PasteSpecial xlPasteValues

End Sub

I would however highly recommend you look into the Offset solution below to have a dynamic pivot data source.

On top of all that there is a neat trick to always keep your pivot table up to speed: Create a named range of a name rData

=OFFSET($A$1;;;COUNTA(A:A);COUNTA(1:1))

Where A1 is the begging of your PivotTable, COUNTA(A:A) counts the amount of rows it needs to extend the range to (pick any column that is filled in for all records), and COUNTA(1:1) counts the amount of headers. Put rData as pivot source. rData will extend any time you add a row or column. No macros needed.

I usually assign the full table to a data range

Set currentData = ActiveWorksheet.Range("A1").CurrentRegion

Where Range("A1") is the beggining of the dataset

with currentData

   lastRow = .rows(.rows.count).row

end with

May not be the most optimal way to do it but works for me Then you can thing underneath using

ActiveWorksheet.Cells(lastRow+1,1).Value = "CCC"
ActiveWorksheet.Cells(lastRow+2,1).Value = "EEE"

Or you can use offset

   Set rangeToFill = ActiveWorksheet.Cells(lastRow,1)
   rangeToFill.offset(1,0).value ="CCC"
   rangeToFill.offset(2,0).value ="EEE"

Hope this is of some help.

Macro Find Last Row and Add Data

1 Answers1