Hello this code works fine except the text I want to add to column T, adds it all the way down the entire columns 100,000+ rows. I only want the text to be added in rows with data in column S. I have tried to fix it and I am sure it's an easy fix, but I have been unable to pin point the correction. Name code is last line at bottom.
Sub NeedHelp()
' MoveColumns Macro
' Description: Rearrange columns in Excel based on column header
Dim iRow As Long
Dim iCol As Long
'Constant values
data_sheet1 = "Original_" 'Create Input Box to ask the user which sheet needs to be reorganised
target_sheet = "Load" 'Specify the sheet to store the results
iRow = Sheets(data_sheet1).UsedRange.Rows.Count 'Determine how many rows are in use
'Create a new sheet to store the results
Worksheets.Add.Name = "Load" 'New Worksheet to load to EC
'Start organizing columns
For iCol = 1 To Sheets(data_sheet1).UsedRange.Columns.Count
'Sets the TargetCol to zero in order to prevent overwriting existing targetcolumns
TargetCol = 0
Range("B1").Value = result
'Read the header of the original sheet to determine the column order
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item1" Then TargetCol = 1
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item2" Then TargetCol = 2
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item3" Then TargetCol = 3
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item4" Then TargetCol = 4
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item5" Then TargetCol = 5
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item6" Then TargetCol = 6
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item7" Then TargetCol = 7
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item8" Then TargetCol = 8
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item9" Then TargetCol = 9
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item10" Then TargetCol = 10
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item11" Then TargetCol = 11
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item12" Then TargetCol = 12
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item13" Then TargetCol = 13
'If a TargetColumn was determined (based upon the header information) then copy the column to the right spot
If TargetCol <> 0 Then
'Select the column and copy it
Sheets(data_sheet1).Range(Sheets(data_sheet1).Cells(1, iCol), Sheets(data_sheet1).Cells(iRow, iCol)).Copy Destination:=Sheets(target_sheet).Cells(1, TargetCol)
End If
Next iCol 'Move to the next column until all columns are read
Worksheets("Header").Range("1:1").Copy Worksheets("Load").Range("1:1") 'Copy + Paste Header
Columns("A:Z").EntireColumn.AutoFit 'Column Width Fix
Worksheets("Load").Range("T:T").Value = "Customer Name" 'Add Client Name
End Sub
Sub NeedHelp()
' MoveColumns Macro
' Description: Rearrange columns in Excel based on column header
Dim iRow As Long
Dim iCol As Long
'Constant values
data_sheet1 = "Original_" 'Create Input Box to ask the user which sheet needs to be reorganised
target_sheet = "Load" 'Specify the sheet to store the results
iRow = Sheets(data_sheet1).UsedRange.Rows.Count 'Determine how many rows are in use
'Create a new sheet to store the results
Worksheets.Add.Name = "Load" 'New Worksheet to load to EC
'Start organizing columns
For iCol = 1 To Sheets(data_sheet1).UsedRange.Columns.Count
'Sets the TargetCol to zero in order to prevent overwriting existing targetcolumns
TargetCol = 0
Range("B1").Value = result
'Read the header of the original sheet to determine the column order
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item1" Then TargetCol = 1
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item2" Then TargetCol = 2
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item3" Then TargetCol = 3
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item4" Then TargetCol = 4
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item5" Then TargetCol = 5
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item6" Then TargetCol = 6
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item7" Then TargetCol = 7
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item8" Then TargetCol = 8
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item9" Then TargetCol = 9
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item10" Then TargetCol = 10
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item11" Then TargetCol = 11
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item12" Then TargetCol = 12
If Sheets(data_sheet1).Cells(1, iCol).Value = "Item13" Then TargetCol = 13
'If a TargetColumn was determined (based upon the header information) then copy the column to the right spot
If TargetCol <> 0 Then
'Select the column and copy it
Sheets(data_sheet1).Range(Sheets(data_sheet1).Cells(1, iCol), Sheets(data_sheet1).Cells(iRow, iCol)).Copy Destination:=Sheets(target_sheet).Cells(1, TargetCol)
End If
Next iCol 'Move to the next column until all columns are read
Worksheets("Header").Range("1:1").Copy Worksheets("Load").Range("1:1") 'Copy + Paste Header
Columns("A:Z").EntireColumn.AutoFit 'Column Width Fix
Worksheets("Load").Range("T:T").Value = "Customer Name" 'Add Client Name
End Sub