Basic Q here, but having trouble finding best answer. This is simple macro I have on a sheet titled "NXT":
CB_wipe_area and CB_paste_area are dynamic ranges that are defined in named cells on that sheet ("nxt"), and are currently:
CB_wipe_area: V4:AC999
CB_paste_area: V4:AC534
The code is fine when run from the NXT sheet but I want to run this code from a different sheet (named "main"). I have a macro I run from the "main" sheet that includes this:
The problem is that when I run it (from the MAIN sheet), it clears those ranges (V4:AC999 and V4:AC534) on the MAIN sheet, not on the NXT sheet. What's the correct way to tell Excel "I want you to run this Macro on that other sheet but understand that the ranges apply to THAT sheet, not this one?"
Code:
Sub CPHC_nxt_CB()
Range(Range("CB_wipe_area").value).ClearContents
Range("CB_formgrab").Copy
Range(Range("CB_paste_area").value).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range(Range("CB_paste_area").value).Copy
Range(Range("CB_paste_area").value).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
CB_wipe_area and CB_paste_area are dynamic ranges that are defined in named cells on that sheet ("nxt"), and are currently:
CB_wipe_area: V4:AC999
CB_paste_area: V4:AC534
The code is fine when run from the NXT sheet but I want to run this code from a different sheet (named "main"). I have a macro I run from the "main" sheet that includes this:
Code:
My_Macro()
Application.Calculation = xlAutomatic
Application.ScreenUpdating = False
[B] With Sheets("nxt")[/B]
[B] Call CPHC_nxt_CB[/B]
[B] End With[/B]
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
The problem is that when I run it (from the MAIN sheet), it clears those ranges (V4:AC999 and V4:AC534) on the MAIN sheet, not on the NXT sheet. What's the correct way to tell Excel "I want you to run this Macro on that other sheet but understand that the ranges apply to THAT sheet, not this one?"
Last edited: