VBA ActiveCell property with Copy-Paste

Question

VBA ActiveCell property with Copy-Paste

CamaDragon 0

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!

0 comments

3 answers

Your answer

Answer 1

Copy the source range directly to ActiveCell by passing it as the Destination; if the user selects B200, B7:B39 will be copied into B200:B232.

Sub CopyRowText()
    Dim ws As Worksheet
    Dim target As Range

    Set ws = ActiveSheet
    Set target = ActiveCell

    ws.Range("B7:B39").Copy Destination:=target
End Sub

The Destination only needs to identify the upper-left destination cell; Excel sizes the pasted area to match the source range. This copies values, formulas, and formatting without a separate Paste operation.^1^

Check the row count

B7:B39 contains 33 rows, not 32:

39 - 7 + 1 = 33

Therefore:

To copy 33 rows, use B7:B39.
To copy 32 rows, use B7:B38.

If the macro inserts rows before copying, save the user's selected cell first so later operations do not change which cell is active:

Sub InsertAndCopy()
    Dim ws As Worksheet
    Dim target As Range

    Set ws = ActiveSheet
    Set target = ActiveCell

    'Your row-insertion code goes here.

    ws.Range("B7:B38").Copy Destination:=target
    Application.CutCopyMode = False
End Sub

Copy text/values only

If “row text” means only the displayed underlying values—not formulas or formatting—avoid the Clipboard and resize the destination to the same dimensions as the source:

Sub CopyValuesOnly()
    Dim source As Range
    Dim target As Range

    Set source = ActiveSheet.Range("B7:B38")
    Set target = ActiveCell

    target.Resize(source.Rows.Count, source.Columns.Count).Value = source.Value
End Sub

Resize returns a range with the requested number of rows and columns.^2^

If the source must always come from a specific sheet, replace ActiveSheet with an explicit worksheet reference:

Set ws = ThisWorkbook.Worksheets("Sheet1")

This avoids copying from the wrong sheet if another worksheet becomes active.

References

Answer 2

You should be able to use the Copy method with a destination range. Since the user has selected cell B200, you can use ActiveCell as the destination.

Range("B7:B39").Copy Destination:=ActiveCell

This copies the contents and formatting of cells B7:B39 into B200:B232, assuming B200 is the active cell.

If you want to copy only the text/values without the original formatting, use:

ActiveCell.Resize(33, 1).Value = Range("B7:B39").Value

Note that B7:B39 contains 33 rows, not 32. If you intend to copy exactly 32 rows, use B7:B38 instead.

For your macro, the first method is probably simplest if you want to retain the original cell formatting.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 3

Hi @CamaDragon ,

Thanks, Marcin, for the solution above. Just adding a bit on top of it: if you insert the 32 rows at the active cell first, that cell gets pushed down, so the paste lands below the new rows. Save the row number before inserting:

Sub InsertBlockWithHeaders()
    Dim ws As Worksheet
    Dim src As Range
    Dim startRow As Long
 
    Set ws = ActiveSheet
    Set src = ws.Range("B7:B38")   '32 rows
    startRow = ActiveCell.Row
 
    ws.Rows(startRow).Resize(32).Insert
    src.Copy Destination:=ws.Cells(startRow, "B")
    Application.CutCopyMode = False
End Sub

Just make sure the selected row is below row 38. If you want the labels across a row instead, use PasteSpecial Transpose:=True.

If you found my response helpful or informative, I would greatly appreciate it if you could follow this guide for your confirmation.

Thank you.