ActiveCell and copy

JLK

New member
I’m trying to figure out what seems to be a simple VBA operation. I’m creating a macro that will automate inserting 32 rows and then formatting those rows with row and column headers. I have the portion for inserting rows. I want to copy some row text from one area (same sheet) to the ActiveCell that the user has selected. I’m just not getting the syntax right. For example copy from B7:B39 starting with cell B200 (which would be the active cell)

Thanks in advance for any help!

Jill
 
Last edited:
Hello Jill,

You can use ActiveCell as the destination when copying a range with VBA. Since you want to copy data from B7 to the cell selected by the user, try this:

Option 1: Copy data with formatting

Code:
Range("B7:B39").Copy Destination:=ActiveCell
For example, if the user selects B200 before running the macro, Excel will copy B7 into B200, including the formatting.

Option 2: Copy only the values (without formatting)

If you only want to copy the text or values, use:

Code:
ActiveCell.Resize(33, 1).Value = Range("B7:B39").Value
This copies the values without changing the destination formatting.

One thing to check: You mentioned inserting 32 rows, but B7 contains 33 rows. If you need exactly 32 rows, change the source range to B7:B38 and use Resize(32, 1) in the second method.
Also, make sure the user selects the destination cell before running the macro.

Let us know if you need help combining this with your row insertion and formatting code.
 

Online statistics

Members online
0
Guests online
219
Total visitors
219

Forum statistics

Threads
468
Messages
2,092
Members
6,827
Latest member
vetrosaunarp
Back
Top