Selecting A Range of Columns and then Copying It To Next Unused Row (A question on efficiency)

L

limeister

New Member
Joined
Feb 2, 2010
Messages
19
Just wondering,
Do you guys every get tired of helping out people here? I surely hope not. Because I have another question for you. Thanks again!

Right now I have found all my answers through search and / or asking directly in this forum. This is so much better than the office help file, or other forums.

I have more of an efficiency question. If you read the code below, I have a range of columns AH to BV. The row changes according to which row is the last row with data.

Is there a better way than just typing in lots of code?
The code below words perfectly with my userform but I thought there might be a better way to do it.

Thanks again.

Code: 
Private Sub cmdAdd_Click()
    CurrentRow = ActiveSheet.UsedRange.Rows.Count + 1
    ' Clear the form for user to add new name:
    LoadRow
    ' Copy formulas in columns V, W and AH to BV to next available row
    ActiveSheet.Range("V" & CurrentRow - 1).Copy
    ActiveSheet.Range("V" & CurrentRow).Select
    ActiveSheet.Range("V" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("W" & CurrentRow - 1).Copy
    ActiveSheet.Range("W" & CurrentRow).Select
    ActiveSheet.Range("W" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AH" & CurrentRow - 1).Copy
    ActiveSheet.Range("AH" & CurrentRow).Select
    ActiveSheet.Range("AH" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AI" & CurrentRow - 1).Copy
    ActiveSheet.Range("AI" & CurrentRow).Select
    ActiveSheet.Range("AI" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AJ" & CurrentRow - 1).Copy
    ActiveSheet.Range("AJ" & CurrentRow).Select
    ActiveSheet.Range("AJ" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AK" & CurrentRow - 1).Copy
    ActiveSheet.Range("AK" & CurrentRow).Select
    ActiveSheet.Range("AK" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AL" & CurrentRow - 1).Copy
    ActiveSheet.Range("AL" & CurrentRow).Select
    ActiveSheet.Range("AL" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AM" & CurrentRow - 1).Copy
    ActiveSheet.Range("AM" & CurrentRow).Select
    ActiveSheet.Range("AM" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AN" & CurrentRow - 1).Copy
    ActiveSheet.Range("AN" & CurrentRow).Select
    ActiveSheet.Range("AN" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AO" & CurrentRow - 1).Copy
    ActiveSheet.Range("AO" & CurrentRow).Select
    ActiveSheet.Range("AO" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AP" & CurrentRow - 1).Copy
    ActiveSheet.Range("AP" & CurrentRow).Select
    ActiveSheet.Range("AP" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AQ" & CurrentRow - 1).Copy
    ActiveSheet.Range("AQ" & CurrentRow).Select
    ActiveSheet.Range("AQ" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AR" & CurrentRow - 1).Copy
    ActiveSheet.Range("AR" & CurrentRow).Select
    ActiveSheet.Range("AR" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AS" & CurrentRow - 1).Copy
    ActiveSheet.Range("AS" & CurrentRow).Select
    ActiveSheet.Range("AS" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AT" & CurrentRow - 1).Copy
    ActiveSheet.Range("AT" & CurrentRow).Select
    ActiveSheet.Range("AT" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AU" & CurrentRow - 1).Copy
    ActiveSheet.Range("AU" & CurrentRow).Select
    ActiveSheet.Range("AU" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AV" & CurrentRow - 1).Copy
    ActiveSheet.Range("AV" & CurrentRow).Select
    ActiveSheet.Range("AV" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AW" & CurrentRow - 1).Copy
    ActiveSheet.Range("AW" & CurrentRow).Select
    ActiveSheet.Range("AW" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AX" & CurrentRow - 1).Copy
    ActiveSheet.Range("AX" & CurrentRow).Select
    ActiveSheet.Range("AX" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AY" & CurrentRow - 1).Copy
    ActiveSheet.Range("AY" & CurrentRow).Select
    ActiveSheet.Range("AY" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("AZ" & CurrentRow - 1).Copy
    ActiveSheet.Range("AZ" & CurrentRow).Select
    ActiveSheet.Range("AZ" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BA" & CurrentRow - 1).Copy
    ActiveSheet.Range("BA" & CurrentRow).Select
    ActiveSheet.Range("BA" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BB" & CurrentRow - 1).Copy
    ActiveSheet.Range("BB" & CurrentRow).Select
    ActiveSheet.Range("BB" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BC" & CurrentRow - 1).Copy
    ActiveSheet.Range("BC" & CurrentRow).Select
    ActiveSheet.Range("BC" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BD" & CurrentRow - 1).Copy
    ActiveSheet.Range("BD" & CurrentRow).Select
    ActiveSheet.Range("BD" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BE" & CurrentRow - 1).Copy
    ActiveSheet.Range("BE" & CurrentRow).Select
    ActiveSheet.Range("BE" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BF" & CurrentRow - 1).Copy
    ActiveSheet.Range("BF" & CurrentRow).Select
    ActiveSheet.Range("BF" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BG" & CurrentRow - 1).Copy
    ActiveSheet.Range("BG" & CurrentRow).Select
    ActiveSheet.Range("BG" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BH" & CurrentRow - 1).Copy
    ActiveSheet.Range("BH" & CurrentRow).Select
    ActiveSheet.Range("BH" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BI" & CurrentRow - 1).Copy
    ActiveSheet.Range("BI" & CurrentRow).Select
    ActiveSheet.Range("BI" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BJ" & CurrentRow - 1).Copy
    ActiveSheet.Range("BJ" & CurrentRow).Select
    ActiveSheet.Range("BJ" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BK" & CurrentRow - 1).Copy
    ActiveSheet.Range("BK" & CurrentRow).Select
    ActiveSheet.Range("BK" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BL" & CurrentRow - 1).Copy
    ActiveSheet.Range("BL" & CurrentRow).Select
    ActiveSheet.Range("BL" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BM" & CurrentRow - 1).Copy
    ActiveSheet.Range("BM" & CurrentRow).Select
    ActiveSheet.Range("BM" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BN" & CurrentRow - 1).Copy
    ActiveSheet.Range("BN" & CurrentRow).Select
    ActiveSheet.Range("BN" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BO" & CurrentRow - 1).Copy
    ActiveSheet.Range("BO" & CurrentRow).Select
    ActiveSheet.Range("BO" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BP" & CurrentRow - 1).Copy
    ActiveSheet.Range("BP" & CurrentRow).Select
    ActiveSheet.Range("BP" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BQ" & CurrentRow - 1).Copy
    ActiveSheet.Range("BQ" & CurrentRow).Select
    ActiveSheet.Range("BQ" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BR" & CurrentRow - 1).Copy
    ActiveSheet.Range("BR" & CurrentRow).Select
    ActiveSheet.Range("BR" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BS" & CurrentRow - 1).Copy
    ActiveSheet.Range("BS" & CurrentRow).Select
    ActiveSheet.Range("BS" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BT" & CurrentRow - 1).Copy
    ActiveSheet.Range("BT" & CurrentRow).Select
    ActiveSheet.Range("BT" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BU" & CurrentRow - 1).Copy
    ActiveSheet.Range("BU" & CurrentRow).Select
    ActiveSheet.Range("BU" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("BV" & CurrentRow - 1).Copy
    ActiveSheet.Range("BV" & CurrentRow).Select
    ActiveSheet.Range("BV" & CurrentRow).PasteSpecial Paste:=xlAll
    ActiveSheet.Range("A" & CurrentRow).Select
    Application.CutCopyMode = False
End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
To copy V:W:

Code: 
    ActiveSheet.Range("V" & CurrentRow - 1 & ":W" & CurrentRow - 1).Copy ActiveSheet.Range("V" & CurrentRow)

You can apply the same to AH:BV.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA to copy range if interior.color is grey till next grey row
Replies
2
Views
457
Akbarov
A
M
  • Question
VBA Code to paste Data in specific Row in another Workbook, depending on the Number in Column 43 of the copied Row
Replies
7
Views
756
HaHoBe
HaHoBe
Y
  • Solved
Copy/paste dynamic, filtered data + put a column into next row (through macro)
Replies
2
Views
863
YourBroLucas
Y
H
  • Question
Dynamic calendar and sum conditionally formatted cells
2 3
Replies
25
Views
731
KRice
K
M
  • Question
Load Array extract data based on two filters and copy to new sheet
Replies
3
Views
652
moorecurls
M

Forum statistics

Threads
1,214,830
Messages
6,121,835
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top