0

I am trying to copy a set of cells based on the active cell when the user hits the macro shortcut key. For example, they have AI10 (R10C35) selected, I want it to copy the range R10C36:R22C79 Then paste values to R11C36:R23C79. The end of the range to copy will always be R22C79, and the end of the paste will always be R23C79. The start of the range is the only thing that varies based on the active cell.

If I can get the help to select the range and get it to copy, I can figure out the PasteRange and HolidayRange from there.

I'm sure my If statements could be simplified too, and open to constructive criticism on those as well, but the string to range is my main objective because the rest works as is.

As I currently have this, I get:

Runtime Error 1004
Method 'Range' of object '_Global' failed

Thanks!

Dim CurrentColumn As Integer
Dim CopyRange As String
Dim PasteRange As String
Dim HolidayRange As String

CurrentRow = ActiveCell.Row
CurrentColumn = ActiveCell.Column

If CurrentColumn <> "35" Then MsgBox ("You must select a date in column AI")
If CurrentRow < 9 Then MsgBox ("You must select a date in column AI")
If CurrentRow > 22 Then MsgBox ("You must select a date in column AI")
If CurrentColumn <> "35" Then Exit Sub
If CurrentRow < 9 Then Exit Sub
If CurrentRow > 22 Then Exit Sub

CopyRange = "R" & CurrentRow & "C" & CurrentColumn + "1" & ":R22C79"
PasteRange = "R" & CurrentRow + "1" & "C79" & ":R23C79"


Range(CopyRange).Select
Selection.Copy

Range(PasteRange).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

HolidayRange = "R" & CurrentRow & "C36:R" & CurrentRow & "C79"
Range(HolidayRange).ClearContents
Mikku
  • 6,538
  • 3
  • 15
  • 38
Laura
  • 3
  • 1
  • 1
    As you are using RC notation, you are better off using `Cells`. I don't think it works with `Range` as you seem to have found out. – SJR Aug 13 '19 at 15:41
  • If I change range to cells, it gives me run-time error 5; Invalid call or argument. Maybe if I break out the R & C?? – Laura Aug 13 '19 at 15:50
  • Well Cells has different syntax so you need to check that first. – SJR Aug 13 '19 at 15:53
  • Could you give me an example? I am no where near as fluent as the rest of you...sorry. :( – Laura Aug 13 '19 at 15:54
  • Type the word Cells in the VBE, highlight it and press F1 for help. cells(row,column), e.g. cells(1,1) = range("A1"). – SJR Aug 13 '19 at 15:57

1 Answers1

1

I may not have got this quite right, but should give you an idea of how to approach it.

Also worth reading how to avoid select.

Sub x()

Dim CurrentColumn As Long

currentrow = ActiveCell.Row
CurrentColumn = ActiveCell.Column

If CurrentColumn <> 35 Then MsgBox ("You must select a date in column AI"):exit sub
If currentrow < 9 Then MsgBox ("You must select a date in column AI"):exit sub
If currentrow > 22 Then MsgBox ("You must select a date in column AI"):exit sub

Range(Cells(currentrow + 1, CurrentColumn + 1), Cells(23, 79)).Value = Range(Cells(currentrow, CurrentColumn + 1), Cells(22, 79)).Value
Range(Cells(currentrow, 36), Cells(currentrow, 79)).ClearContents

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26