Copy-Pasting Dynamically Using Case Statements And Named Ranges

jaylotheman

New Member
Joined
Oct 5, 2011
Messages
7
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

Well-known Member
Joined
May 24, 2005
Messages
3,709
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?
 

Forum statistics

Threads
1,081,546
Messages
5,359,455
Members
400,529
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top