Marco used in 15 worksheet

JimHorton

New Member
Joined
Apr 30, 2014
Messages
11
I want to use the code below in 15 worksheets. The issue I have is if a user inserts a column after "I" which than that mess's up the formula (Range("m7:m166").Select) in the macro. If I used named ranges than I would need 15 names and 15 macros. Must be an easier way??? Less lengthy way???



Code:
Sub Ref1Machine()'
' Ref1Machine Macro
'
'
    Application.ScreenUpdating = False
    Range("RefMaster") = Range("e3")
' REFRESH_DATA Macro
    ActiveWorkbook.RefreshAll
' Gets the machine in the cell above actual hours/$ and pastes them below
'
    Range("D6:D85").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-60
    Range("e6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("m7:m166").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-144
    Range("n7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E3").Select
    Application.CutCopyMode = False
        Application.ScreenUpdating = True
End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
If column M has a specific value or header in a specific row, I'd use .FIND in VBA to find this value and then copy the values to the first column on the right. This would negate any issues with users inserting columns before column M.
In this code, you need to replace the part in red with the specific value I'm suggesting. If you're looking for a string value, make sure you include speachmarks around the value:
Rich (BB code):
Sub Ref1Machine() '
' Ref1Machine Macro

Dim rng As Range

    Application.ScreenUpdating = False
    
    Range("RefMaster").Value = Range("E3").Value
    ' REFRESH_DATA Macro
    
    ActiveWorkbook.RefreshAll
    ' Gets the machine in the cell above actual hours/$ and pastes them below
    
    Range("E6:E85").Value = Range("D6:D85").Value
    
    On Error Resume Next
    set rng = Cells.Find(What:=your value, lookin:=xlvalues)
    On Error GoTo 0
    
    If Not rng Is Nothing Then
        Cells(7, rng.Column + 1).Resize(Range("M7:M166").Cells.Count).Value = Cells(7, rng.Column).Resize(Range("M7:M166").Cells.Count).Value
    Else
        MsgBox "Value not found"
    End If
        
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
As you could probably tell Jack, I am Record Macro and then edit kind of user.

I could not get the code to work. The one column to the right is the issue because the data in the column is changed by other criteria (blank to $10K). I do have a header that will not change 3 rows above (M3) the data I want to copy. I will have to research how to use cells.find and relative reference my move from there.

Thanks for giving me some direction.

For the time being I have Protected the sheets so a user can not Insert columns.
 
Upvote 0

Forum statistics

Threads
1,223,495
Messages
6,172,624
Members
452,466
Latest member
Lynlindsay

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