Shifting Columns

[Andresleo47]

Hi Dear Community,

I'm trying to find the best way to Shift (Cut - Paste) all the information below and to the right of the "Worker" cell in VBA. Basically, I would like all the information below and to the right of cell E2 to be cut and pasted in Cell D2. It will delete all the information from cell D2 below, and that's fine!

However I would not like to use specific cells (Because it may vary with time). In this case it's cell E2 below and to the right, but in the future this might change. So instead of using the cell E2 as a parameter, I would like to use the word "Worker" in row 2 as a Parameter.

Am I making sense? Kindly advise, If it's not clear, I would be happy to rephrase it.

Thanks,

Andres

 

[Andresleo47]

Great, it worked. Thanks so much. In the same direction, same file...If you look at the previous attachments, there's a column (empty) called "Legal Entity". That column is showing in column U in the attachment. Is there a way I can cut-paste this column on Column E (From E2 onwards), without using the cells as parameters, but just using the Name "Legal Entity" matching the column "Legal Entity" in row 1?

Thanks, I'm glad the code is working thanks to you!!

 

[Fluff]

Try

Sub Andresleo()
   Dim Fnd As Range
   Set Fnd = Range("2:2").Find("Legal Entity", , , xlWhole, , xlNext, False, , False)
   If Not Fnd Is Nothing Then
      Range(Fnd, Cells(Rows.Count, Fnd.Column)).Cut Range("E2")
   End If
End Sub

 

[Andresleo47]

Hi, thanks. Looks really interesting. I have a question. If "Legal Entity" from row 1 goes to another column (Let's say, column A), would this code still work? Is there any way the following line:

Range(Fnd, Cells(Rows.Count, Fnd.Column)).Cut Range("E2")

Doesn't look after Cell E2, but after the column that actually has the "Legal Entity" title on row 1.

Is that possible?

Thanks!

 

[Fluff]

Try

Sub Andresleo()
   Dim Fnd As Range, Fnd2 As Range
   Set Fnd = Range("2:2").Find("Legal Entity", , , xlWhole, , xlNext, False, , False)
   Set Fnd2 = Range("1:1").Find("Legal Entity", , , xlWhole, , xlNext, False, , False)
   If Not Fnd Is Nothing And Not Fnd2 Is Nothing Then
      Range(Fnd, Cells(Rows.Count, Fnd.Column)).Cut Fnd2.Offset(1)
   End If
End Sub

 

[Andresleo47]

Wow, great...It's working...Now, one last thing... How can I populate this cell with "Hiring Manager", but without using this line:

Cells(2, 21).Value = "Hiring Manager"

I mean, if cell U1 changes it's position, this line wouldn't work, right?

Any thoughts?

 

[Fluff]

Us find a shown previously & then offset 1 row

 

[Andresleo47]

Like this?

Sub Andresleo()
Dim Fnd As Range, Fnd2 As Range
Set Fnd = Range("2:2").Find("Hiring Manager", , , xlWhole, , xlNext, False, , False)
Set Fnd2 = Range("1:1").Find("Hiring Manager", , , xlWhole, , xlNext, False, , False)
If Not Fnd Is Nothing And Not Fnd2 Is Nothing Then
Range(Fnd, Cells(Rows.Count, Fnd.Column)).Cut Fnd2.Offset(1)
End If
End Sub

 

[Fluff]

No, like this

Sub Andresleo()
   Dim Fnd As Range
   Set Fnd = Range("1:1").Find("Hiring Manager", , , xlWhole, , xlNext, False, , False)
   If Not Fnd Is Nothing Then
      Fnd.Offset(1) = "Hiring Manager"
   End If
End Sub

 

[Andresleo47]

Works great! Thanks for everything!

Take care

 

[Fluff]

You're welcome & thanks for the feedback.