Copy range of cells using variable criteria and named ranges

[Cpetersoc]

I am trying to write some simple VBA that will find a range of cells on one worksheet, to go to another worksheet to paste values of that range based on another range of cells.

The method I’d like to adopt to reference the ranges is Range(Cells(R,C)),as it seems to provide the flexibility I need for the ranges, which may change. Additionally, I am using Named Ranges where I can in order to preserve references should rows or columns shift in the evolution of my spreadsheet.

That said, I’d like to: 1) Find an anchor cell [Named Range1], which is the upper left of the range; 2) Select the range where R = Named Range2, and C = Named Range1’s column value + Named Range3. For instance, Named Range1’s address is $B$5, Named Range2 = 11, and Named Range3 = 6. The goal is that the selected range for this example is $B$5:$G$15. If any of these Named Ranges [variables] change, the resulting selected range will change. Each of the named ranges are scoped to the workbook. Once the range is selected, I would like the macro to go to a second anchor [Named Range4], and paste values.

I’ve tried searching for an answer to this, found many similar questions, but my complete lack of programming skill and ignorance of VBA syntax and lexicon has just stopped me from gaining any traction. Without even attempting to code. I have an example spreadsheet, but apparently can't post it, so maybe can email ...

Any help the community can provide will I’m sure be invaluable.

 

[Cpetersoc]

I believe I got this resolved from another forum, HaHoBe ... many thanks.

Sub EF1049117_Revised()
    Dim rngToCopy As Range
    Dim rngToPaste As Range
    Set rngToCopy = Range("Named_Range_Anchor").Resize(Range("Named_Range_Last_Row"), Range("Named_Range_Last_Column"))
    Set rngToPaste = Range("Named_Range_Anchor_Destination")

    rngToCopy.Copy
    Sheets("Paste").Activate
    rngToPaste.PasteSpecial Paste:=xlPasteValues
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Locked = True
End Sub

 

[DocAElstein]

I believe I got this resolved from another forum, HaHoBe ... many thanks.

.......

. Thanks for letting us know. That is appreciated: I had your thread saved as one to possibly try to solve. So it was very helpful to know you have a working solution. And thanks for reproducing the solution.

.
. By the way, You explained your requirement quite well, But it was not easy to anyone trying to help you exactly what you want.

I am trying to write some simple VBA that will find a range of cells on one worksheet, to go to another worksheet to paste values of that range based on another range of cells................ I have an example spreadsheet, but apparently can't post it, so maybe can email ...
............

. Some members might give you there email over a personal messages, but I think it is reasonable to expect you to get familiar with getting your requirement over better:

. As always, “A (Good!) Picture paints a thousand words”

. . So again Just some helpful advice for the next time: Try to provide Tables that can be copied into a spreadsheet showing example data but also importantly exactly how the final output should look like in the Excel File based on your actual example data.


. There are various ways to do this. The first is preferred by this Forum for excel files as then everyone can see wot is going on quickly.. The Third method I prefer. - Then one can get on straight away with writing a code for you in the file you provide.

. 1 If you can, try uploading this, https://onedrive.live.com/?cid=8cffd...CE27E813%21189 instructions here MrExcel HTML Maker . This free Excel add-In is good for screen shots here of spreadsheets. Then everyone can quickly see what is going on and follow the Thread easily.
Or
. 2 Up left in the Thread editor is a table icon. Click that, create an appropriately sized table and fill it in. (To get this icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)
Or
. 3 Supply us with example Excel files (Can of course be shortened, or made - up data in case any info is sensitive)
. For example send over this free thing: Box Net,
Remember to select Share after uploading and give us the link they provide.



Alan
Gruß aus Bayern

P.s. Welcome to the Board

P.s 2. May I ask which Forum you got the answer from – I have seen HaHoBe on German Forums I think?

 

[Cpetersoc]

I appreciate the advice on displaying examples ... I'm not a frequent enough user so I think MrExcel didn't give me the privilege of uploading files, and then I got stuck on how to show what I was talking about. I've got this for the future. The other forum was Excel Forum. Thanks again!

 

[DocAElstein]

I appreciate the advice on displaying examples ... I'm not a frequent enough user so I think MrExcel didn't give me the privilege of uploading files, and then I got stuck on how to show what I was talking about. I've got this for the future. The other forum was Excel Forum. Thanks again!

Ok. Remember things like Box Net, -once you have it set up it is almost as quick as an attachment. Some regulars like and advise uploading a file, some hate it!, or prefer you use the MrExcel HTML Spreadsheet maker and code tags etc. Just pot luck really. (I sometimes do both)
. I tried Excel Forum a few times, but did not work too well for me. Found here lots better. But maybe pot luck again.
Alan

Just for completeness as I think it is liked for multi Forum Posting: Here is the link I think to that Thread in Excel Forum

Copy range of cells using variable criteria and named ranges