Create Dynamic Range Copying with Excel VBA

VBA Code: Create Dynamic Range Copying

Sub CopyDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim rng As Range
    Dim destination As Range   
    ' Set the worksheet (modify as needed)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Find the last used row in column A (assuming column A always has data)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Find the last used column in row 1 (assuming row 1 always has headers)
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Define the dynamic range based on last row and last column
    Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    ' Set the destination range (e.g., copy to "Sheet2", starting at A1)
    Set destination = ThisWorkbook.Sheets("Sheet2").Cells(1, 1)
    ' Copy the range and paste values and formats
    rng.Copy
    destination.PasteSpecial Paste:=xlPasteValues
    destination.PasteSpecial Paste:=xlPasteFormats
    ' Clear the clipboard
    Application.CutCopyMode = False
    ' Notify the user
    MsgBox "Dynamic range copied successfully!", vbInformation, "Copy Complete"
End Sub

Detailed Explanation

1. Declare Variables

Dim ws As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim rng As Range
Dim destination As Range

• ws: Represents the worksheet where the data is located.
• lastRow: Stores the last used row in column A.
• lastCol: Stores the last used column in row 1.
• rng: Defines the dynamic range to be copied.
• destination: Specifies where the copied data will be pasted.

2. Set the Worksheet

Set ws = ThisWorkbook.Sheets(« Sheet1 »)

• The ws variable is set to reference « Sheet1 ». Modify this as needed.

3. Find the Last Used Row

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

• ws.Rows.Count returns the total number of rows in Excel (1,048,576 in modern versions).
• .End(xlUp) moves up from the last row to find the last occupied cell in column A.
• The .Row property extracts the row number.

4. Find the Last Used Column

lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

• ws.Columns.Count gives the total number of columns (16,384 in modern Excel).
• .End(xlToLeft) moves left from the last column in row 1 to find the last occupied column.
• The .Column property extracts the column number.

5. Define the Dynamic Range

Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

• ws.Cells(1,1): Starting point (A1).
• ws.Cells(lastRow, lastCol): Ending point based on last detected row and column.
• ws.Range(…, …) forms the dynamic range.

6. Set the Destination

Set destination = ThisWorkbook.Sheets(« Sheet2 »).Cells(1, 1)

• Defines the top-left cell where data will be pasted in « Sheet2 ».

7. Copy and Paste the Data

rng.Copy

destination.PasteSpecial Paste:=xlPasteValues

destination.PasteSpecial Paste:=xlPasteFormats

• .Copy copies the dynamic range.
• .PasteSpecial Paste:=xlPasteValues pastes only values (removing formulas).
• .PasteSpecial Paste:=xlPasteFormats retains formatting.

8. Clear Clipboard and Notify User

Application.CutCopyMode = False

MsgBox « Dynamic range copied successfully! », vbInformation, « Copy Complete »

• Application.CutCopyMode = False removes the copy selection.
• MsgBox informs the user that the operation is complete.

How to Use This Code

1. Open Excel and press ALT + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Copy and paste the above code into the module.
4. Modify « Sheet1 » and « Sheet2 » as needed.
5. Run CopyDynamicRange() to copy data dynamically.