Loop through a range of cells and skip blank cells.

T

testrad

New Member
Joined
May 20, 2015
Messages
6
I have a spreadsheet that survey's equipment condition for equipment condition for multiple places, which is designated by ID (see Data Input example). For each row I need the equipment under the columns header "E1" and "E1C" copy/pasted into another worksheet ("Data Output") with the ID number associtated with that row and then "E2" & "E2C" copy pasted into next blank row with the same ID. Keep looping through the columns and skip the nonblank cells in each row. After going through all the columns in a row it should go to the next row until there is a row with no data (blank row).
Data Input example (smaller example of what the table looks like)

[TABLE="width: 791"]
<colgroup><col><col><col span="10"></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]E1[/TD]
[TD]E1C[/TD]
[TD]E2[/TD]
[TD]E2C[/TD]
[TD]E3[/TD]
[TD]E3C[/TD]
[TD]E4[/TD]
[TD]E4C[/TD]
[TD]E5[/TD]
[TD]E5C[/TD]
[TD]ET5C[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]N600D[/TD]
[TD]9[/TD]
[TD]N600D[/TD]
[TD]9[/TD]
[TD]N600D[/TD]
[TD]9[/TD]
[TD]N2000D+[/TD]
[TD]9[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]N2000D+[/TD]
[TD]2[/TD]
[TD]N2000D [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]N2000D+[/TD]
[TD]9[/TD]
[TD]N2000D+[/TD]
[TD]0[/TD]
[TD]N2000D+[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[/TR]
[TR]
[TD]195[/TD]
[TD]N600D[/TD]
[TD]9[/TD]
[TD]N2000D+[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The "Data Output Example" image can help explain what I mean.

[TABLE="width: 211"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Model[/TD]
[TD]Condition[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]N600D[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]N600D[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]N600D[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]N600D[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]N2000D+[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]N2000D[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]N2000D+[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]N2000D+[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]N2000D+[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD]…[/TD]
[TD]…[/TD]
[/TR]
[TR]
[TD]195[/TD]
[TD]N600D[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]195[/TD]
[TD]N2000D+[/TD]
[TD]8

[/TD]
[/TR]
</tbody>[/TABLE]

The code i have so far does what the intended output should do, but it is hard coded instead of looping through the rows/columns and automatically skipping blank cells.

Option Explicit
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False


Dim source As Worksheet, target As Worksheet
Dim r As Range, rB As Range

'range is B:L. B8:L8 empty so skipped
'next is B9:L9. skip J9:L9 becuase empty
Sheets("Source").Range("B9:C9,A9").Copy
Sheets("ET target").Range("A2").PasteSpecial xlValues
Sheets("Source").Range("D9:E9,A9").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("D9:E9,A9").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("F9:G9,A9").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("H9:I9,A9").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
'Skip B10:L10 empty. Next is B11:L11. Skip F11:L11 becuase empty
Sheets("Source").Range("B11:C11,A11").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("D11:E11,A11").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
'Skip B12:L14 becuase empty. Next is B15:L15. skip H15:L15 becuase empty
Sheets("Source").Range("B15:C15,A15").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("D15:E15,A15").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
Sheets("Source").Range("F15:G15,A15").Copy
Sheets("ET target").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
'Repeat for upto 200 rows.



Application.ScreenUpdating = True


End Sub

Sorry if there is duplicate threads somewhere. I have looked through quite a few posts and did not find anything that i could tailor to fit. Thanks for your help!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Try this

Code: 
Sub Loop_range()
  Dim sh1 As Worksheet, sh2 As Worksheet, ar As Range, j As Long
  Set sh1 = Sheets("Source")
  Set sh2 = Sheets("ET target")
  For Each ar In sh1.Range("B1", sh1.Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
    For j = 2 To sh1.Cells(ar.Row, Columns.Count).End(xlToLeft).Column Step 2
      sh2.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, 3).Value = Array(ar.Offset(, -1), sh1.Cells(ar.Row, j), sh1.Cells(ar.Row, j + 1))
    Next
  Next
End Sub
 
Upvote 0
Here is another approach. In particular, this would be much faster if the data is large.
I have assumed that data starts in cell A1 of "Source" and that "ET Target" is empty when the code is run. If either assumption is incorrect, more details please.
Test in a copy of your workbook.

Code: 
Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long, maxnum As Long
  
  With Sheets("Source")
    a = .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Resize(, .Cells(1, .Columns.Count).End(xlToLeft).Column).Value
    uba2 = UBound(a, 2)
    maxnum = .Range("B2").Resize(UBound(a) - 1, uba2 - 1).SpecialCells(xlConstants).Count / 2
  End With
  ReDim b(1 To maxnum, 1 To 3)
  For i = 2 To UBound(a)
    For j = 2 To uba2 Step 2
      If Len(a(i, j)) > 0 Then
        k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = a(i, j): b(k, 3) = a(i, j + 1)
      End If
    Next j
  Next i
  With Sheets("ET Target")
    .Range("A2").Resize(k, 3).Value = b
    .Range("A1:C1").Value = Array("ID", "Model", "Condition")
    .UsedRange.Columns.AutoFit
  End With
End Sub
 
Upvote 0
Sorry for the slow reply i wasn't able to download the copy and screen shot files to my work computer, so i had to get the files transferred to a personal computer. (thanks btw for the URLs)

I realize that I over-simplified my "Data Input Example", now that i see how your codes work. My example Input/Output showed what should happen for one region of the spreadsheet; i.e. 1 type of equipment on the survey. There is actually multiple regions that need to get pasted into separate sheets. Additionally there is columns with user info that should not get copied into the other sheets. My initial thought was that if i could get the code to work for 1 region i could duplicate to work for the other regions.

the table below is is has all of the columns in the spreadsheet but not all the rows. additionally the user info is hidden (columns B:AK)
Revised data input example:

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
AQ​
[/th][th]
AR​
[/th][th]
AS​
[/th][th]
AT​
[/th][th]
AU​
[/th][th]
AV​
[/th][th]
AW​
[/th][th]
AX​
[/th][th]
AY​
[/th][th]
AZ​
[/th][th]
BA​
[/th][th]
BB​
[/th][th]
BC​
[/th][th]
BD​
[/th][th]
BE​
[/th][th]
BF​
[/th][th]
BG​
[/th][th]
BH​
[/th][th]
BI​
[/th][th]
BJ​
[/th][th]
BK​
[/th][th]
BL​
[/th][th]
BM​
[/th][th]
BN​
[/th][th]
BO​
[/th][th]
BP​
[/th][th]
BQ​
[/th][th]
BR​
[/th][th]
BS​
[/th][th]
BT​
[/th][th]
BU​
[/th][th]
BV​
[/th][th]
BW​
[/th][th]
BX​
[/th][th]
BY​
[/th][th]
BZ​
[/th][th]
CA​
[/th][th]
CB​
[/th][th]
CC​
[/th][th]
CD​
[/th][th]
CE​
[/th][th]
CF​
[/th][th]
CG​
[/th][th]
CH​
[/th][th]
CI​
[/th][th]
CJ​
[/th][th]
CK​
[/th][th]
CL​
[/th][th]
CM​
[/th][th]
CN​
[/th][th]
CO​
[/th][th]
CP​
[/th][th]
CQ​
[/th][th]
CR​
[/th][th]
CS​
[/th][th]
CT​
[/th][th]
CU​
[/th][th]
CV​
[/th][th]
CW​
[/th][th]
CX​
[/th][th]
CY​
[/th][th]
CZ​
[/th][th]
DA​
[/th][th]
DB​
[/th][th]
DC​
[/th][th]
DD​
[/th][th]
DE​
[/th][th]
DF​
[/th][th]
DG​
[/th][th]
DH​
[/th][th]
DI​
[/th][th]
DJ​
[/th][th]
DK​
[/th][th]
DL​
[/th][th]
DM​
[/th][th]
DN​
[/th][th]
DO​
[/th][th]
DP​
[/th][th]
DQ​
[/th][th]
DR​
[/th][th]
DS​
[/th][th]
DT​
[/th][th]
DU​
[/th][th]
DV​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#C0C0C0]
ID​
[/td][td=bgcolor:#C0C0C0]
ET1​
[/td][td=bgcolor:#C0C0C0]
ET1C​
[/td][td=bgcolor:#C0C0C0]
ET2​
[/td][td=bgcolor:#C0C0C0]
ET2C​
[/td][td=bgcolor:#C0C0C0]
ET3​
[/td][td=bgcolor:#C0C0C0]
ET3C​
[/td][td=bgcolor:#C0C0C0]
ET4​
[/td][td=bgcolor:#C0C0C0]
ET4C​
[/td][td=bgcolor:#C0C0C0]
ET5​
[/td][td=bgcolor:#C0C0C0]
ET5C​
[/td][td=bgcolor:#C0C0C0]
ET6​
[/td][td=bgcolor:#C0C0C0]
ET6C​
[/td][td=bgcolor:#C0C0C0]
ET7​
[/td][td=bgcolor:#C0C0C0]
ET7C​
[/td][td=bgcolor:#C0C0C0]
ET8​
[/td][td=bgcolor:#C0C0C0]
ET8C​
[/td][td=bgcolor:#C0C0C0]
ET1-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
ET1C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
ET2-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
ET2C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
UT1​
[/td][td=bgcolor:#C0C0C0]
UT1C​
[/td][td=bgcolor:#C0C0C0]
UT2​
[/td][td=bgcolor:#C0C0C0]
UT2C​
[/td][td=bgcolor:#C0C0C0]
UT3​
[/td][td=bgcolor:#C0C0C0]
UT3C​
[/td][td=bgcolor:#C0C0C0]
UT4​
[/td][td=bgcolor:#C0C0C0]
UT4C​
[/td][td=bgcolor:#C0C0C0]
UT5​
[/td][td=bgcolor:#C0C0C0]
UT5C​
[/td][td=bgcolor:#C0C0C0]
UT6​
[/td][td=bgcolor:#C0C0C0]
UT6C​
[/td][td=bgcolor:#C0C0C0]
UT1-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
UT1C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
UT2-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
UT2C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
BT1​
[/td][td=bgcolor:#C0C0C0]
BT1C​
[/td][td=bgcolor:#C0C0C0]
BT2​
[/td][td=bgcolor:#C0C0C0]
BT2C​
[/td][td=bgcolor:#C0C0C0]
BT3​
[/td][td=bgcolor:#C0C0C0]
BT3C​
[/td][td=bgcolor:#C0C0C0]
BT4​
[/td][td=bgcolor:#C0C0C0]
BT4C​
[/td][td=bgcolor:#C0C0C0]
BT1-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
BT1C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
BT2-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
BT2C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
RT1​
[/td][td=bgcolor:#C0C0C0]
RT1C​
[/td][td=bgcolor:#C0C0C0]
RT2​
[/td][td=bgcolor:#C0C0C0]
RT2C​
[/td][td=bgcolor:#C0C0C0]
RT3​
[/td][td=bgcolor:#C0C0C0]
RT3C​
[/td][td=bgcolor:#C0C0C0]
RT1-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
RT1C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
RT2-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
RT2C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
INT1​
[/td][td=bgcolor:#C0C0C0]
INT1C​
[/td][td=bgcolor:#C0C0C0]
INT2​
[/td][td=bgcolor:#C0C0C0]
INT2C​
[/td][td=bgcolor:#C0C0C0]
INT3​
[/td][td=bgcolor:#C0C0C0]
INT3C​
[/td][td=bgcolor:#C0C0C0]
INT1-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
INT1C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
INT2-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
INT2C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
CR1​
[/td][td=bgcolor:#C0C0C0]
CR1C​
[/td][td=bgcolor:#C0C0C0]
CR2​
[/td][td=bgcolor:#C0C0C0]
CR2C​
[/td][td=bgcolor:#C0C0C0]
CR3​
[/td][td=bgcolor:#C0C0C0]
CR3C​
[/td][td=bgcolor:#C0C0C0]
CR1-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
CR1C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
CR2-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
CR2C-MOBILITY​
[/td][td=bgcolor:#C0C0C0]
Survey Meter​
[/td][td=bgcolor:#C0C0C0]
Survey MeterC​
[/td][td=bgcolor:#C0C0C0]
MPI1​
[/td][td=bgcolor:#C0C0C0]
MPI1C​
[/td][td=bgcolor:#C0C0C0]
FPI1​
[/td][td=bgcolor:#C0C0C0]
FPI1C​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
11​
[/td][td]N600D[/td][td]
9​
[/td][td]N600D[/td][td]
9​
[/td][td]N600D[/td][td]
9​
[/td][td]N2000D+[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Sonic 1200M[/td][td]
9​
[/td][td]GE USN-52[/td][td]
8​
[/td][td]GE USN-52[/td][td]
8​
[/td][td]GE USN-52[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]BM1000[/td][td]
9​
[/td][td]BM1000[/td][td]
9​
[/td][td]BM600[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]LPX-160[/td][td]
9​
[/td][td]LPX-160[/td][td]
8​
[/td][td]LPX-160[/td][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td]TSG 501015 (newer)[/td][td]
6​
[/td][td]TSG 501013 (older)[/td][td]
0​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]QTY 2+ 451P, QTY 2+ SM-400[/td][td]
8​
[/td][td]GB3509A-01[/td][td]
9​
[/td][td]GB MA-2[/td][td]
9​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
12​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
13​
[/td][td]N2000D+[/td][td]
9​
[/td][td]N2000D [/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]GE USN-60[/td][td]
9​
[/td][td]GE USN-60[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]GB3509A-01[/td][td]
9​
[/td][td]Other[/td][td]
9​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
14​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
15​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
16​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
17​
[/td][td]N2000D+[/td][td]
9​
[/td][td]N2000D+[/td][td]
9​
[/td][td]N2000D+[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]N2000D+[/td][td]
9​
[/td][td][/td][td][/td][td]GE USN-60[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]GE USN-60[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]LPX-160[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]TSG 501013 (older)[/td][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]QTY 2+ 451P, QTY 4+ SM-400[/td][td]
7​
[/td][td]GB3509A-01[/td][td]
1​
[/td][td]GB PT-48[/td][td]
9​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
18​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
19​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
20​
[/td][td]N600D[/td][td]
9​
[/td][td]N600D[/td][td]
9​
[/td][td]N600D[/td][td]
9​
[/td][td]N600D[/td][td]
9​
[/td][td]N600D[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]N600D[/td][td]
9​
[/td][td]N600D[/td][td]
9​
[/td][td]Sonic 1200M[/td][td]
9​
[/td][td]GE USN-60[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]GE USN-60[/td][td]
0​
[/td][td][/td][td][/td][td]Bondmaster[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Bondmaster[/td][td]
9​
[/td][td][/td][td][/td][td]LPX-160[/td][td]
9​
[/td][td]LPX-160[/td][td]
9​
[/td][td][/td][td][/td][td]LPX-160[/td][td]
0​
[/td][td][/td][td][/td][td]TSG 501013 (older)[/td][td]
9​
[/td][td]TSG 501013 (older)[/td][td]
9​
[/td][td][/td][td][/td][td]TSG 501013 (older)[/td][td]
9​
[/td][td][/td][td][/td][td]GE CRx Flex II[/td][td]
6​
[/td][td]GE CRx Flex II[/td][td]
6​
[/td][td][/td][td][/td][td]GE CR-50P[/td][td]
7​
[/td][td][/td][td][/td][td]QTY 6+ 451P, no SM-400[/td][td]
8​
[/td][td]Other[/td][td]
9​
[/td][td]GB PT-36[/td][td]
9​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Source[/td][/tr][/table]
 
Upvote 0
testrad said:
Sorry for the slow reply i wasn't able to download the copy and screen shot files to my work computer, so i had to get the files transferred to a personal computer. (thanks btw for the URLs)

I realize that I over-simplified my "Data Input Example", now that i see how your codes work. My example Input/Output showed what should happen for one region of the spreadsheet; i.e. 1 type of equipment on the survey. There is actually multiple regions that need to get pasted into separate sheets. Additionally there is columns with user info that should not get copied into the other sheets. My initial thought was that if i could get the code to work for 1 region i could duplicate to work for the other regions.

the table below is is has all of the columns in the spreadsheet but not all the rows. additionally the user info is hidden (columns B:AK)
Click to expand...

A couple of questions:
- Each region on a new sheet?
- Which columns should be copied?
 
Upvote 0
Yes, so there is 9 different types of equipment as you scroll through the columns(ET, UT, BT, RT, INT, CR, SM, MPI, & FPI). each one gets copied into a separate sheet.

The table below shows the different equipment types and the ranges of the columns. For example, ET equipment is range AQ2:BJ (upto XX number of rows). The ET data in each row would get copied into a sheet called "ET target". The next is UT equipment, whose range is BK2:BZ and would get copied into a sheet called "UT target". The output for all equipment is the same, just in different sheets (I.E. the output should be ID, model, condition).

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
U​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td]
55​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Surveys Returned[/td][/tr][/table]
 
Upvote 0
sorry about that. still figuring out the coping table tools :).

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]
ET Equipment​
[/td][td]
UT Equipment​
[/td][td]
BT Equipment​
[/td][td]
RT Equipment​
[/td][td]
INT Equipment​
[/td][td]
CR Equipment​
[/td][td]
SM Equipment​
[/td][td]
MPI Equipment​
[/td][td]
FPI Equipment​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
region 1​
[/td][td]
region 2​
[/td][td]
region 3​
[/td][td]
region 4​
[/td][td]
region 5​
[/td][td]
region 6​
[/td][td]
region 7​
[/td][td]
region 8​
[/td][td]
region 9​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
AQ2:BJ200​
[/td][td]
BK2:BZ200​
[/td][td]
CA2:CL200​
[/td][td]
CM2:CV200​
[/td][td]
CW2:DF200​
[/td][td]
DG2:DP200​
[/td][td]
DQ2:D200​
[/td][td]
DS2:DT200​
[/td][td]
DU2:DV200​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet8[/td][/tr][/table]
 
Upvote 0
I'm confused.
What is the real name of the regions. That is, in the headers from AQ1 to DV1.
Do you have:
ET1-MOBILITY
or
ET Equipment
or
Region 1


It is possible that you can put a list of names and the region on a sheet, something like this, each region in column B means a new sheet.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:136.87px;" /><col style="width:98.85px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >NOMBRE</td><td >REGION</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >ET1</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >ET1C</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >ET2</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >ET2C</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >ET3</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >ET3C</td><td >REGION 1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >…</td><td >…</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >ET1-MOBILITY</td><td >REGION 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >ET1C-MOBILITY</td><td >REGION 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >ET2-MOBILITY</td><td >REGION 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >ET2C-MOBILITY</td><td >REGION 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >…</td><td >…</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >UT1</td><td >REGION 3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >UT1C</td><td >REGION 3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >UT2</td><td >REGION 3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >UT2C</td><td >REGION 3</td></tr></table>
 
Last edited:
Upvote 0
I guess I don't see what the importance of the headers is. The specific ranges of the data is described in my last table and the actual headers were in the "Revised Data Input example". I put "region" in the table because that is best how to describe how theres different sections. additionally i added "ET equipment" to give an idea of what equipment is in those regions. Ive added a table as requested. I hope that clears it up, If not i can try again. thanks for your help!

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
headers​
[/td][td]
regions​
[/td][td]
Actual Range​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]ET1[/td][td]
Region 1​
[/td][td]
AQ2:BJ200 (or last row)​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]ET1C[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]ET2[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]ET2C[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]…[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]ET8[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]ET8C[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td]ET1-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td]ET1C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td]ET1-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td]ET1C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td]UT1[/td][td]
Region 2​
[/td][td]
BK2:BZ200​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]UT1C[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]UT2[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td]UT2C[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]…[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td]UT6[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td]UT6C[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
26​
[/td][td]UT1-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
27​
[/td][td]UT1C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
28​
[/td][td]UT2-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
29​
[/td][td]UT2C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
30​
[/td][td]BT1[/td][td]
Region 3​
[/td][td]
CA2:CL200​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
31​
[/td][td]BT1C[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
32​
[/td][td]…[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
33​
[/td][td]BT1-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
34​
[/td][td]BT1C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
35​
[/td][td]BT2-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
36​
[/td][td]BT2C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
37​
[/td][td]RT1[/td][td]
Region 4​
[/td][td]
CM2:CV200​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
38​
[/td][td]RT1C[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
39​
[/td][td]…[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
40​
[/td][td]RT1-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
41​
[/td][td]RT1C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
42​
[/td][td]RT2-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
43​
[/td][td]RT2C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
44​
[/td][td]INT1[/td][td]
Region 5​
[/td][td]
CW2:DF200​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
45​
[/td][td]INTC[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
46​
[/td][td]…[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
47​
[/td][td]INT1-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
48​
[/td][td]INT1C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
49​
[/td][td]INT2-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
50​
[/td][td]INT2C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
51​
[/td][td]CR1[/td][td]
Region 6​
[/td][td]
DG2:DP200​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
52​
[/td][td]CR1C[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
53​
[/td][td]…[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
54​
[/td][td]CR1-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
55​
[/td][td]CR1C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
56​
[/td][td]CR2-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
57​
[/td][td]CR2C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
58​
[/td][td]SM1[/td][td]
Region 7​
[/td][td]
DQ2:DR200​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
59​
[/td][td]SM1C[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
60​
[/td][td]…[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
61​
[/td][td]SM1-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
62​
[/td][td]SM1C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
63​
[/td][td]SM2-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
64​
[/td][td]SM2C-Mobility[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
65​
[/td][td]MPI1C[/td][td]
Region 8​
[/td][td]
DS2:DT200​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
66​
[/td][td]MPI1C[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
67​
[/td][td]FPI1[/td][td]
Region 9​
[/td][td]
DU2:DV200​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
68​
[/td][td]FPI1C[/td][td]
​
[/td][td]
​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet8[/td][/tr][/table]
 
Upvote 0
The idea is to find a pattern in the header to determine when to create each sheet. You didn't explain the range of columns, maybe it's clear to you, but I don't know your information. Can the ranges be on the sheet8 or maybe the first 2 letters of each region?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Copy/Paste Adjacent Cell
Replies
6
Views
384
mumps
mumps
B
  • Question Question
VBA Code to Send Mail to skip blank cells
Replies
1
Views
331
Micron
M
H
  • Solved
Macro to speedy up process when copying Data
Replies
3
Views
110
Logit
Logit
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
372
NdNoviceHlp
NdNoviceHlp
N
  • Solved
VBA - Increment range and loop until blank Cell
Replies
3
Views
330
dreid1011
dreid1011

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top