Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Copy and paste multiple range to a Summary

0

Hi All,

i am really new to this VBA and i seek for your help on the following:

Requirement: 

1.Range O3:O1995 , S3:S1995 , and W3:W1995 copy and paste as value to C3:C1995

2.Range P3:P1995 , T3:T1995 , and X3:X1995 copy and paste as value to F3:F1995

3.Unable to skip the blank  as All these range are with formula. i only need to copy those with data only.  (tried to use lens but it didn't work)

4.when copy and paste these range , i dont want all these to override the data. it will paste as the next blank rows at range C3 and F3

Appreciate your help on the following.

Thank you

Answer
Discuss

Discussion

When you say this: "Range O3:O1995 , S3:S1995 , and W3:W1995 copy and paste as value to C3:C1995" do you mean that the data from cell O3 should be combined with data from cell S3 and W3 and all put into cell C3? Or do you mean that the ranges to copy will not be completely full of data so simply copy the cells from column O to column C and then, under that, copy the cells from column S to the next empty rows in column C, etc?
don (rep: 1989) Jul 26, '17 at 7:42 am
Hi Don, thanks for your reply.it is Just simply copy the cells from o to Column c and then S to the next empty rows in column c etc
Edward_13 (rep: 2) Jul 26, '17 at 10:57 am
Add to Discussion

Answers

0
Selected Answer

Try this:

Sub copy_data()

For Each cell In Range("O3:O1995")

    If cell.Value <> "" Then

        Range("C" & Rows.Count).End(xlUp).Offset(1).Value = cell.Value

    End If

Next cell

End Sub

Copy and paste the code for as many ranges as needed.

Change:

O3:O1995

to the range to copy.

Change:

C

to the column to where you need to copy the data.

Discuss

Discussion

Thanks bro. It works. 
really appreciate it!!!
Edward_13 (rep: 2) Jul 27, '17 at 9:27 pm
Add to Discussion
0

Try this macro

Option Explicit

Sub copy_range()
Dim arr, arr1, i%, t%, my_rg As Range
arr = Array("O3:O1995", "S3:S1995", "W3:W1995 ")
arr1 = Array("P3:P1995", "T3:T1995 ", "X3:X1995 ")
t = 3
For i = 0 To 2
Set my_rg = Range(arr(i)).SpecialCells(2)
my_rg.Copy Range("c" & t)
t = t + my_rg.Cells.Count
 Next
 Set my_rg = Nothing
 t = 3
 For i = 0 To 2
Set my_rg = Range(arr1(i)).SpecialCells(2)
my_rg.Copy Range("f" & t)
t = t + my_rg.Cells.Count
 Next
End Sub
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login