1. ## Possible Loop? (2002)

I've finally broken down and need expert assistance. I have a column for each day of the week, and need to extract only the following ranges of each column: cells 5 and 6, then cells 9 and 10, then cells 13 and 14, etc. In other words, give me the data from two specific cells, skip the next 2 cells, then give me the data from the next 2 cells, etc. On top of that, if there is any data at all in the second of these cells, I need to place it in a specific location (cell on another worksheet) based not only on the data in the first cell, but also based on the column it

2. ## Re: Possible Loop? (2002)

You wrote "need to extract only the following ranges ..." Can you explain where you want to extract to? And how to determine what to do with the second of these cells?

3. ## Re: Possible Loop? (2002)

Thanks, Hans.
Your response has made me consider my own question more thoroughly. I only need to extract both values to another worksheet in the same workbook. The first (top) cell contains the ideal expectation, and the cell below it contains the actual results of that expectation, for each day of the week. In between each of these two-cell ranges is garbage I don't want, so I want to skip over those and get the next two-cell range containing the expected and actual. When I am able to put it on the other worksheet, I can perform other analyses on it.

Thanks,
Jamesian

4. ## Re: Possible Loop? (2002)

Thanks. More questions: you wrote that you have four worksheets with seven columns each.
- Are these seven columns contiguous, for example columns B through H, or not?
- Do you want to extract the data to one worksheet, with 28 columns filled? And if so, is there a specific order?

5. ## Re: Possible Loop? (2002)

Yes, for the first question. They are contiguous.

For the second, if I could get all Mondays in the first 4 columns, all Tuesdays in the 2nd 4, etc.

Thanks,
Jamesian

6. ## Re: Possible Loop? (2002)

Here is some code, to go into a general module. You will have to modify the values of the constants to suit your situation, as well as the names of the worksheets in the ExtractCells procedure. To execute, click somewhere in ExtractCells and press F5. Make a backup copy before you try this.

' First row to be extracted
Const StartRow = 5

' Number of rows in source
Const NumRows = 63

' First column to be extracted (1 = A, 2 = B, etc.)
Const StartCol = 2

' Number of columns to be extracted
Const NumCols = 7

' Name of destination worksheet
Const strTarget = "Sheet5"

Sub ExtractCells()
ExtractSheet "Sheet1", 1
ExtractSheet "Sheet2", 2
ExtractSheet "Sheet3", 3
ExtractSheet "Sheet4", 4
End Sub

Sub ExtractSheet(strSource As String, lngOffset As Long)
Dim wshSource As Worksheet
Dim wshTarget As Worksheet
Dim lngRow As Long
Dim lngCol As Long

Set wshSource = Worksheets(strSource)
Set wshTarget = Worksheets(strTarget)

For lngCol = 0 To NumCols - 1
For lngRow = 0 To NumRows 2 Step 2
wshTarget.Cells(lngRow + 1, 4 * lngCol + lngOffset) = _
wshSource.Cells(lngRow * 2 + StartRow, lngCol + StartCol)
wshTarget.Cells(lngRow + 2, 4 * lngCol + lngOffset) = _
wshSource.Cells(lngRow * 2 + StartRow + 1, lngCol + StartCol)
Next lngRow
Next lngCol

Set wshTarget = Nothing
Set wshSource = Nothing
End Sub

7. ## Re: Possible Loop? (2002)

Hans, it's beautiful and works perfectly. Thank you for your invaluable time.

Thanks,
Jamesian

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•