1

I know this question has been asked in several different fashions but I'm unable to get the code to run. I'm very new to coding macros (2 whopping days of it now). Here is the part I'm looking for help with. 

If Box = "8" Then Workbooks("Narc Log.xlsm").Sheets("Fentanyl").Range("B5:F5").Copy _
    Destination:=Workbooks("Box 8.xlsm").Sheets("Fentanyl").Range("B4")

This copies a specific range of cells. I want to change it to copy the 6 cells to the left of the active cell and paste to the next available line in the other workbook "Box8"

braX
  • 11,506
  • 5
  • 20
  • 33
Jeff
  • 11
  • 3

1 Answers1

0

first you should find the position of your active cell

  Dim c, r As Integer
    r = ActiveCell.Row
    c = ActiveCell.Column
Set sht = ActiveSheet

then you can use range function to get them

with sht
sht.range(.cells(c-7,r) , .cells(c-1,r)).copy
end with

and rest of code below.

P.S. i do not like to use .copy as it always run into error. I would prefer something like

Workbooks("Box 8.xlsm").Sheets("Fentanyl").Range("B4:B10").value = sht.range(.cells(c-7,r) , .cells(c-1,r)).value
Osman Wong
  • 170
  • 9
  • 2
    Note that if you `Dim c, r As Integer` only `r` is of type `Integer` but `c` will be `Variant`. You must declare a type for **every** variable in VBA! Also you must use `Long` because Excel has more rows than `Integer` can handle: `Dim c As Long, r As Long`. I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. – Pᴇʜ Nov 28 '18 at 07:42