Copy Paste Values Depending on Header Name AND Multiple Criteria VBA

sensitivechins

New Member
Joined
Mar 17, 2022
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
First post after being a lurker.

I have a file (data is all made up) (Test Client File.xlsm) that has a list of dropdowns and based on those dropdowns, range B18:J32 pull from the Data tab. For example, if C3, C5, and C6 are set to Data Analytics, APAC, and Netflix, respectively, range B18:J32 will pull in all employees who meet those criteria. Column I represents a comparison period and Column J represents the New Period. In Column L, I have an update column. Here, a user will put in the amount, which will then be pulled into the Source tab. When they're finished making entries, they would hit the Update button and a VBA code would run, taking the data in the Source tab and copy pasting values into the Destination tab, depending on the header name. Current VBA code below.

VBA Code:
Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long, header As Range, foundHeader As Range, lCol As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Source")
    Set desWS = Sheets("Destination")
    LastRow = srcWS.Cells.Find(Range("I1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = desWS.Cells(1, Columns.Count).End(xlToLeft).Column
    For Each header In desWS.Range(desWS.Cells(1, 1), desWS.Cells(1, lCol))
        Set foundHeader = srcWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundHeader Is Nothing Then
            srcWS.Range(srcWS.Cells(1, foundHeader.Column), srcWS.Cells(LastRow, foundHeader.Column)).Copy
            desWS.Cells(1, header.Column).PasteSpecial xlPasteValues
        End If
    Next header
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

While this code does in fact take the Source data and copy pastes value to the Destination tab, the only issue I run into is that if someone changed the selections to a different Team, Geography, and Client and then entered their amounts and pressed Update, it would override what's already been done for the previous team.

Is there a way to add criteria so that the code will only copy paste values to the data that's in B18:J32? So in this example, (Team: Data Analytics, Geography: APAC, Client: Netflix) only Paola Schultz's amount of $200 will be copy paste valued. And if you were to change the dropdowns and update, it would only update for those employees and not override the entire column in the Destination tab?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,215,216
Messages
6,123,669
Members
449,114
Latest member
aides

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top