VBA Help - there's got to be a clever approach

[RaviWildcat]

Hi Everyone - I'm pretty rusty with my VBA so I appreciate your help!

1. I've got a master table that looks like this. This will contain a list of all my users and dates they are active

Team	Name	Date
Central Perk	Monica Geller	5/20/2024
Central Perk	Monica Geller	5/21/2024
Central Perk	Monica Geller	5/22/2024
Central Perk	Monica Geller	5/23/2024

I am creating a separate input table where I can add names and dates for people I'll be adding to my master table. So, if my input table looks like this

Team	Name	Date
Central Perk	Ross Geller	5/20/2024
Central Perk	Ross Geller	5/21/2024
..	..	..
..	..	..

I'd like to run my vba macro (or series of macros) to add the last two rows of the input table to the master table and get this:

Team	Name	Date
Central Perk	Monica Geller	5/20/2024
Central Perk	Monica Geller	5/21/2024
Central Perk	Monica Geller	5/22/2024
Central Perk	Monica Geller	5/23/2024
Central Perk	Ross Geller	5/20/2024
Central Perk	Ross Geller	5/21/2024

If I was to do this by hand, I'd simply manually select all the items on the input table that are not '..", and then navigate to the bottom of the master table and paste it in.

I tried recording the steps to select the team column on the input table, search for text containing "..", then moving the cursor up and selecting all the rows on top before pasting into the master table. I also tried saving the input table as a table, applying a filter to select the information I want and then pasting to the end of the master table.

Is there a clever way to append this data?

Thank you!

Ravi

t

Any suggestions?

 

[Cubist]

Try on a copy. Adapt the ranges as needed.

Sub InsertDataToMaster()
    Dim wsMaster As Worksheet
    Dim wsInput As Worksheet
    Dim lastRowMaster As Long
    Dim lastRowInput As Long
    Dim nextRowMaster As Long
    Dim inputRange As Range
    Dim masterRange As Range

    Set wsMaster = ThisWorkbook.Sheets("Master")
    Set wsInput = ThisWorkbook.Sheets("Input")
    nextRowMaster = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row + 1
   
    Set inputRange = wsInput.Range("A2").CurrentRegion
    Set inputRange = inputRange.Offset(1, 0).Resize(inputRange.Rows.Count - 1)

    Set masterRange = wsMaster.Cells(nextRowMaster, 1)
    masterRange.Resize(inputRange.Rows.Count, inputRange.Columns.Count).Value = inputRange.Value
   
End Sub

 

[RaviWildcat]

I will give this a try! Gracias!

 

[RaviWildcat]

Worked great thank you !