sensitivechins
New Member
- Joined
- Mar 17, 2022
- Messages
- 1
- Office Version
- 2016
- Platform
- 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.
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?
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?