Creating new tab and adjusting in new tab

[RandyLWalkerJr]

Hello,

The macro I am trying to create will essentially do the following (in this order):

1. Copy the active page into a new tab and make the new tab active
2. Move the values from column m (rows 71-129) to column j (rows 71-129)
3. Delete column k (rows 71-129).4

Here is the kicker and the part that is really throwing me off. I need the vba to be able to account for varying ranges, so that in one instance it is from rows 71-129, and another time from 79-300. Is this possible?

I'm very new to VBAs and all help would be greatly appreciated. Thanks so much in advance

 

[Redwolfx]

This is rather simplistic, but I think it does what you are asking.

Sub MoveAndOrganize()

Dim Rng As Range
Dim B As String
Dim E As String
Dim WS As Worksheet

ActiveSheet.Copy After:=Sheets(Sheets.Count)

Set WS = ActiveSheet

B = Columns("M:M").Find("Total completed and stored to date (D+E+F)", Range("M1"), xlValues).Address
E = Columns("M:M").Find("total range M", Range("M1"), xlValues).Address
    
Set Rng = Range(B & ":" & E)

WS.Activate
Rng.Copy
WS.Range("J" & Rng.Row).PasteSpecial
Rng.ClearContents
Rng.Offset(0, -2).ClearContents

End Sub

 

[RandyLWalkerJr]

Looking great. I'm sorry to do this but I have a couple of quick issues!!


1.The macro is also copying over the identifiers instaed of just the range in between. Is there a way to remove that issue?

2. Column m is being cut and pasted rather than copied. I just want to copy over the VALUES, but leave the existing formulas in column M.

 

[Redwolfx]

Sorry you said Move Column M to J.

Both issues you brought up should be taken care of with this code. Though it is untested.

Sub MoveAndOrganize()

Dim Rng As Range
Dim B As String
Dim E As String
Dim WS As Worksheet

ActiveSheet.Copy After:=Sheets(Sheets.Count)

Set WS = ActiveSheet

B = Columns("M:M").Find("Total completed and stored to date (D+E+F)", Range("M1"), xlValues).Offset(1, 0).Address
E = Columns("M:M").Find("total range M", Range("M1"), xlValues).Offset(-1, 0).Address
    
Set Rng = Range(B & ":" & E)

WS.Activate
Rng.Copy
WS.Range("J" & Rng.Row).PasteSpecial
Rng.Offset(0, -2).ClearContents

End Sub

 

[RandyLWalkerJr]

Ok. Hopefully last thing. How can I change the paste line so that it only pastes values rather than formulas? (from column m to j).

We are getting really close. Thanks.

 

[Redwolfx]

Ok. Hopefully last thing. How can I change the paste line so that it only pastes values rather than formulas? (from column m to j).

We are getting really close. Thanks.

After paste special add xlpastevalues

 

[RandyLWalkerJr]

Up and running. Thanks very much for the help.

 

[Redwolfx]

Glad to help. Thanks for the feedback/