Macro help

[marissalynch]

Hi there - I am trying to write a macro for the following criteria.

The name of the worksheet will always be different. The number of columns will always be the same, however, the number of rows will vary.
Column AV is sorted Z-A so the word Main is first, followed by the word Comparison. I want to cut and paste columns AE through AU into columns N through AD, but only up until Main.

Thank you in advance!

 

[Bosquedeguate]

Please provide a copy of a sample Excel sheet that this macro will operate on.

 

[marissalynch]

 

[Bosquedeguate]

Try this Sub (Macro) to Copy AE - AU (when AV = "Main") to N - AD

Option Explicit
Public Sub Copy_AEtoAU_To_NtoAD()
  Dim lr As Long
  Dim r As Long
  lr = Range("AV" & Rows.Count).End(xlUp).Row
  
  For r = 2 To lr
    If UCase(Range("AV" & r)) = "MAIN" Then
      Range("AE" & r & ":AU" & r).Copy Destination:=ActiveSheet.Range("N" & r & ":AD" & r)
    Else
      Exit For
    End If
  Next r
End Sub

 

[Peter_SSs]

Assuming at least one "Main" row, what about doing them all at once?

Sub Move_Main()
  Range("AE2", Columns("AV").Find(What:="Main", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious).Offset(, -1)).Cut Destination:=Range("N2")
End Sub

 

[marissalynch]

I'm adding it onto the already existing macro below and not sure where to add it within.

Sub walmartbeacon_cleanup()
'
' walmartbeacon_cleanup Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'
Cells.Replace What:="Walmart Grocery", Replacement:="Pickup", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="walmart.com", Replacement:="Delivery", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Columns("A:AE").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
"AE2:AE755"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A1:AE755")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("N:AD").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("T11").Select

End Sub

 

[Bosquedeguate]

Try replacing the last 4 lines of your VBA code with the one-line solution

 

[marissalynch]

I got a Compile error: Expected End Sub
Sub walmartbeacon_cleanup()
'
' walmartbeacon_cleanup Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'
Cells.Replace What:="Walmart Grocery", Replacement:="Pickup", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="walmart.com", Replacement:="Delivery", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Columns("A:AE").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
"AE2:AE755"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A1:AE755")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
Sub Move_Main()
Range("AE2", Columns("AV").Find(What:="Main", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious).Offset(, -1)).Cut Destination:=Range("N2")
End Sub

 

[Bosquedeguate]

I got a Compile error: Expected End Sub
Sub walmartbeacon_cleanup()
'
' walmartbeacon_cleanup Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'
Cells.Replace What:="Walmart Grocery", Replacement:="Pickup", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="walmart.com", Replacement:="Delivery", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Columns("A:AE").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
"AE2:AE755"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A1:AE755")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
Sub Move_Main()
Range("AE2", Columns("AV").Find(What:="Main", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious).Offset(, -1)).Cut Destination:=Range("N2")
End Sub

Remove the Sub Move_Main line

 

[marissalynch]

Remove the Sub Move_Main line

I removed it and now the error is Expected End With