Hi there!
I have a project that I am working on with two workbooks. In one workbook, I have a sheet (ExtractedData) with a table. I have a hidden helper column that combines column C with column G since concatenated value is unique. In the other workbook, I have a sheet (ModelGHGRPData) which has a very similar table. All columns are the same except there are 4 additional columns that perform calculations. I have a hidden helper column in this table as well, similarly combining column A and C. This is the master database that I need to add data from Extracted Data to.
I found a code snippet that helps me identify new rows and adds them to the ModelGHGRPData table. I am new to VBA, so please forgive the not-so-elegant way that I have managed to do this! However, using EntireRow brings over all empty columns past the table, which results in a huge worksheet for ModelGHGRPData, which is very inconvenient. Is there a way to get only the table row over to ModelGHGRPData (excluding my hidden helper column)? Additionally, if there's a better way than making a hidden helper, I am all ears.
I have a project that I am working on with two workbooks. In one workbook, I have a sheet (ExtractedData) with a table. I have a hidden helper column that combines column C with column G since concatenated value is unique. In the other workbook, I have a sheet (ModelGHGRPData) which has a very similar table. All columns are the same except there are 4 additional columns that perform calculations. I have a hidden helper column in this table as well, similarly combining column A and C. This is the master database that I need to add data from Extracted Data to.
I found a code snippet that helps me identify new rows and adds them to the ModelGHGRPData table. I am new to VBA, so please forgive the not-so-elegant way that I have managed to do this! However, using EntireRow brings over all empty columns past the table, which results in a huge worksheet for ModelGHGRPData, which is very inconvenient. Is there a way to get only the table row over to ModelGHGRPData (excluding my hidden helper column)? Additionally, if there's a better way than making a hidden helper, I am all ears.
VBA Code:
Option Explicit
Sub PasteNewRows() 'This macro pastes new data into the cement model
Dim cl As Range, rng As Range
Dim ExtractedData As Worksheet, ModelGHGRPData As Worksheet
Dim CementModel As Workbook
Dim LastRowModel As Long, LastRowExtract As Long, LastRowTable As Long
Dim newrow As ListRow
Dim datatable As ListObject
Dim IsFound As Boolean
Dim Msg, Style, Title, Response
Dim p As Integer, q As Integer
Dim newcoltool As ListColumn, newcolmodel As ListColumn
Set datatable = ModelGHGRPData.ListObjects(1) 'setting the GHGRP data table in the GHGRP Data tab in the model
Set newrow = datatable.ListRows.Add ' Adding a new row to the table
With newrow.Range 'Inserts a new row with the formulas from data already in the model
.Offset(-2).Copy 'Copying the formatting from a previous row in the table
.Cells(1).PasteSpecial Paste:=xlPasteFormulas 'Pasting the formulas to the new, blank row
Application.CutCopyMode = False ' Clear the clipboard
End With
'Add a helper column to the Extracted Data tab that combines report year and GHGRP ID
Set newcoltool = ExtractedData.ListObjects(1).ListColumns.Add
LastRowExtract = ExtractedData.Cells(Cells.Rows.Count, "A").End(xlUp).row
With newcoltool
.Range.EntireColumn.Hidden = True
For p = 2 To LastRowExtract
.Range(p) = ExtractedData.Cells(p, 3) & ExtractedData.Cells(p, 7) 'Column 3 is report year, column 7 is GHGRP ID
Next p
End With
'Add a helper column to the GHGRP Data tab table in the cement model that combines report year and GHGRP ID
Set newcolmodel = ModelGHGRPData.ListObjects(1).ListColumns.Add
LastRowModel = ModelGHGRPData.Cells(Cells.Rows.Count, "A").End(xlUp).row 'Defines the last row of data in the Model GHGRP data
With newcolmodel
For q = 2 To LastRowModel
.Range(q) = ModelGHGRPData.Cells(q, 3) & ModelGHGRPData.Cells(q, 7)
Next q
End With
'Creates a dictionary
With CreateObject("scripting.dictionary")
For Each cl In ModelGHGRPData.Range("AV2", ModelGHGRPData.Range("AV" & Rows.Count).End(xlUp)) 'from the helper column (Report year and GHGRP ID) in the cement model
.Item(cl.Value) = Empty
Next cl
For Each cl In ExtractedData.Range("AK2", ExtractedData.Range("AK" & Rows.Count).End(xlUp)) 'Loops through Extracted Data helper column
If Not .Exists(cl.Value) Then
If rng Is Nothing Then Set rng = cl Else Set rng = Union(rng, cl)
End If
Next cl
End With
'If the data in the helper column of the extracted data is NOT already in the model (i.e., new data), it will be pasted into the model
If Not rng Is Nothing Then
rng.EntireRow.SpecialCells(xlCellTypeVisible).Copy
ModelGHGRPData.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues 'Paste the values of the row into the last row of the GHGRP data table
End If
With newcolmodel
.Delete
End With
With newcoltool
.Range.EntireColumn.Hidden = False
.Delete
End With
