Copy-Pasting Dynamically Using Case Statements And Named Ranges

[jaylotheman]

I want to do a simple copy and paste from one tab of my workbook to another.

I created 24 named ranges (not efficient I know), 12 for the copies and 12 for the pastes, again all a part of 12 cases.

The copy is somewhat static, it will always be one row of cells, the reason I have it to be cases is because depending on what one cell says it will either copy all 12 cells, 11 cells, 10 cells, 9 cells all the way to just 1 cell.

The paste is more dynamic, it will always paste either 12 cells, 11 cells, 10 cells, 9 cells all the way to just 1 cell based on the same cell and what it's value is.
The tricky part for me and where I am now getting stuck is, in the paste the actual area in the workbook where the paste has to happen is dynamic, it can change from time to time depending on a user's selection.

I need to do a lookup:
If it can find a value of "1000601690" in column C it will paste the values in cells R to AC of that row, but again this won't necessarily always be a paste of 12 values, there will be 12 cases, sometimes it would paste R to AC, sometimes S to AC, sometimes T to AC all the way to just cell AC itself. If it cannot find that value then no copy-paste should occur.
Here's what I have so far, I'm struggling how to incorporate the lookup aspect, I can do it if it's just a straight copy and paste with named ranges...any help would be appreciated thanks! (have posted here as well with no help thus far: excel vba - Case Statement Using Dynamic Named Range - Stack Overflow)

I've also included a link to an Example file (it should have the named ranges in tact and show that I'm trying to copy from one tab to another tab, in the last tab the location of that value could change from one row to the other but it will always be in the column (hence the dynamic nature): https://drive.google.com/file/d/0B1mb0wVkbQcUbDZYTlBBN1RZb2M/view?usp=sharing

<code>Sub ApplyExpatBenAI() Dim wb As Workbook Dim ws0 As Worksheet Dim ws1 As Worksheet Dim ws6 As Worksheet Dim sorceRng1 As String Dim destRng1 As String Set wb = ThisWorkbook Set ws0 = wb.Sheets("Selection") Set ws1 = wb.Sheets("Non-Union Empl HC") Set ws6 = wb.Sheets("G&A Detail") Select Case ws0.Range("D6") Case "BUD" sorceRng1 = "CopyExpatBenAIBUD": destRng1 = "PasteExpatBenAIBUD" Case "F00" sorceRng1 = "CopyExpatBenAIBUD": destRng1 = "PasteExpatBenAIBUD" Case "F01" sorceRng1 = "CopyExpatBenAIOneEleven": destRng1 = "PasteExpatBenAIOneEleven" Case "F02" sorceRng1 = "CopyExpatBenAITwoTen": destRng1 = "PasteExpatBenAITwoTen" Case "F03" sorceRng1 = "CopyExpatBenAIThreeNine": destRng1 = "PasteExpatBenAIThreeNine" Case "F04" sorceRng1 = "CopyExpatBenAIFourEight": destRng1 = "PasteExpatBenAIFourEight" Case "F05" sorceRng1 = "CopyExpatBenAIFiveSeven": destRng1 = "PasteExpatBenFiveSeven" Case "F06" sorceRng1 = "CopyExpatBenAISixSix": destRng1 = "PasteExpatBenAISixSix" Case "F07" sorceRng1 = "CopyExpatBenAISevenFive": destRng1 = "PasteExpatBenAISevenFive" Case "F08" sorceRng1 = "CopyExpatBenAIEightFour": destRng1 = "PasteExpatBenAIEightFour" Case "F09" sorceRng1 = "CopyExpatBenAINineThree": destRng1 = "PasteExpatBenAINineThree" Case "F10" sorceRng1 = "CopyExpatBenAITenTwo": destRng1 = "PasteExpatBenAITenTwo" Case "F11" sorceRng1 = "CopyExpatBenAIElevenOne": destRng1 = "PasteExpatBenAIElevenOne" Case Else Exit Sub End Select ws1.Range(sorceRng1).Copy ws6.Range(destRng1).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True Range("R128").SelectEnd Sub</code></pre>

 

[pbornemeier]

What is the relationship between the copy data and the lookup?
What is the relationship between the paste data and the lookup
If only column AC is being pasted is R-AB empty or must data in the corresponding cells at the paste site be preserved?
Are the named ranges dynamic? or do they refer to a single fixed cell or single fixed range?