Loop through range names for location of dynamic paste

**AlvaroSiza** · 08-15-2011, 03:50 PM

Key points,

- I am currently importing tabular data from anywhere between 5 and 7 worksheets.
- I am grabbing the data from each and pasting (end(x1up)) to a single dump worksheet.
- I am then using a Do While Loop to analyze for instances of two strings (using inStr - the beginning and end of a selected range).
- Once this range is selected, I am stair-stepping down and to the right for my paste by a factor of 17 columns and 100 rows.

Here's the code:

Sub MoveStairStep()
    Dim iterator As Integer
    Dim columnsToTheRight As Integer
    Dim rowsDown As Integer
    Dim rowStartCutOffset As Integer
    Dim rowEndCutOffset As Integer
    Dim sheet As Worksheet
    
    Dim rStart As Range
    
    iterator = 0
    columnsToTheRight = 12 + 5
    rowsDown = 0
    
    Do While iterator < 1000
        'Set range variable
        Set rStart = Range("E66")
        'Find string Statistics in title to begin loop
        If InStr(rStart.Offset(iterator, 0).Value, "Statistics") Then
            rowStartCutOffset = iterator
        End If
        'loop until finding string Finish
        If InStr(rStart.Offset(iterator, 0).Value, "Finish") Then
            rowEndCutOffset = iterator
            rStart.Offset(rowStartCutOffset, 0).Resize(rowEndCutOffset - rowStartCutOffset + 1, 12).Select
            Selection.Cut
            ' THIS IS THE PART I NEED REVISED
            rStart.Select
            rStart.Offset(rowsDown, columnsToTheRight).Select
            ThisWorkbook.Sheets("Data").Paste
            Application.CutCopyMode = False
            columnsToTheRight = columnsToTheRight + 12 + 5
            rowsDown = rowsDown + 150
        End If
        iterator = iterator + 1
    Loop
    
End Sub

**Request**
I have since named 6 ranges that I would like to loop through to tell excel where I want each subsequent range pasted after analysis and selection.cut. The ultimate goal is to have the data pasted to the same place everytime regardless of the dynamic nature of the imported data. The ranges are A_1, B_1, C_1, A_2, B_2, and C_2.

THANK YOU!

**davegugg** · 08-15-2011, 04:48 PM

How about creating an array of the ranges, then looping through the array?

**AlvaroSiza** · 08-15-2011, 06:18 PM

Could you possibly provide me with an example of how to incorporate into the provided code?

**davegugg** · 08-16-2011, 10:19 AM

Well, I can't really tell by looking at your code where the named ranges are supposed to fit in. Here is a generic example of looping through an array of ranges.

Dim arrRng(6) As Range

    arrRng(0) = Range("A_1")
    arrRng(1) = Range("B_1")
    arrRng(2) = Range("C_1")
    arrRng(3) = Range("A_2")
    arrRng(4) = Range("B_2")
    arrRng(5) = Range("C_2")
            For i = 0 To 5
                arrRng(i).Select
                '...
            Next i

If you need more detail you'll have to provide a more detailed description of what your code should do, or post a sample workbook, which is always a good option.

**AlvaroSiza** · 08-16-2011, 10:46 AM

Thanks Dave. I have simplified down to a vanilla example. ABCD populate A1:A4. B1:B4 is a named range containing the string text of 4 other named ranges in the worksheet (C5, D6, E7, and F8, respectively. I need to loop through the first, cut it, and paste it to the range indicated in the second loop.

Ultimately C5="A", D6="B", E7="C", and F8="D"

My effort is included in Module 1.

**AlvaroSiza** · 08-16-2011, 08:24 PM

Ok, so I ran with the loops and can get the first one to work, but need assistance exiting the loop back into the outer loop. Attached for reference. Code is in module 1. I'm learning. Much appreciated!

Sub ForumExp()
Dim myrCell As Range
Dim rngCell As Range
Dim rngLocation As Range
'
For Each myrCell In Range("MyRange")
    If myrCell > 0 Then myrCell.Cut
           For Each rngCell In Range("LocationNames")
                Set rngLocation = rngCell
                Application.Goto Range(rngLocation)
                ActiveSheet.Paste
            Next rngCell
Next myrCell
End Sub

2 caveats:

1. After the paste occurs I lose the named range of the destination cell.
2. I would prefer to have A1:A4 be text as opposed to numbers, but I could not get

If Application.WorksheetFunction.IsText(Range(myrCell)) Then myrCell.cut

*From Andy Pope contribution found here: http://www.ozgrid.com/forum/showthre...682#post143682

To work in place of the

If myrCell > 0 Then myrCell.Cut

Used herein.

Thanks!

**davegugg** · 08-17-2011, 11:01 AM

This should fix all your issues:

Sub ForumExp()

    Dim myrCell As Range
    Dim rngCell As Range
    Dim rngLocation As Range
    Dim arrRng(4) As Range
    Dim i As Integer
    
    Set arrRng(0) = ActiveSheet.Range("aPaste")
    Set arrRng(1) = ActiveSheet.Range("bPaste")
    Set arrRng(2) = ActiveSheet.Range("cPaste")
    Set arrRng(3) = ActiveSheet.Range("dPaste")
    i = 0
    '=============================================================
    For Each myrCell In Range("MyRange")
        If myrCell <> "" Then
            arrRng(i).Value = myrCell.Value
            i = i + 1
        End If
    Next myrCell

End Sub