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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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 Question
VBA to copy range if interior.color is grey till next grey row
Replies
2
Views
565
Akbarov
A
M
  • Question 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
995
HaHoBe
HaHoBe
D
  • Question Question
Copy/Pasting rows of a table to specific cells of a separate worksheet template and exporting as a new file.
Replies
0
Views
214
DjentChicken
D
Y
  • Solved
Copy/paste dynamic, filtered data + put a column into next row (through macro)
Replies
2
Views
1K
YourBroLucas
Y
H
  • Question Question
Dynamic calendar and sum conditionally formatted cells
2 3
Replies
25
Views
1K
KRice
K

Forum statistics

Threads
1,224,222
Messages
6,177,221
Members
452,765
Latest member
Erka Gizli

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