VBA copy selection range in loop

Question

I want to ask about loop in VBA. I have this VBA code:

Sub OpenCopyPaste()

' open the source workbook and select the source sheet
Workbooks.Open Filename:="C:\Users\Adryan Permana\Downloads\Test\part8.xlsx"
Sheets("Sheet1").Select
' copy the source range
Sheets("Sheet1").Range("C2:E2,C3:E3,C4:E4,C5:E5,C6:E6,C7:E7,C8:E8,C9:E9").Select
Selection.Copy

' select current workbook and paste the values starting at A1
Windows("report.xlsx").Activate
Sheets("Sheet1").Select
Sheets("Sheet1").Range("C3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save

End Sub

I want to select all data in range "C2:E2" to "C9:E9" and paste it to other workbook.

In my current code, I need to type one by one range from C2 - C9. I want to select data from C2-C9 in loop.

Is there any way to do it in loop?

Not sure I undestand, you want to copy "C2:E2", than "C3:E3" one by one all the way to "C9:E9" ? why not copy "C2:E9" with one shot ? — Shai Rado
– Shai Rado, Commented Apr 19, 2017 at 7:10
Check out similar question: stackoverflow.com/questions/10782189/… — krib
– krib, Commented Apr 19, 2017 at 7:50

Shai Rado · Accepted Answer · 2017-04-19 07:17:44Z

1

You can copy the entire range with Range("C2:E9").Copy.

Also, there is no need to use Select, Activate and Selection, it slows down the code run-time, just use fully qulifed Wroksheets and Range instead.

Code

Option Explicit

Sub OpenCopyPaste()

Dim wb As Workbook
Dim Reportwb As Workbook

' set report workbook to workbook object (works only is the file is already open)
Set Reportwb = Workbooks("report.xlsx")

' open the source workbook and select the source sheet
Set wb = Workbooks.Open(Filename:="C:\Users\Adryan Permana\Downloads\Test\part8.xlsx")

' copy the source range and paste in 1 line , paste at "C3"
wb.Sheets("Sheet1").Range("C2:E9").Copy Destination:=Reportwb.Sheets("Sheet1").Range("C3")

Application.CutCopyMode = False
Reportwb.Save

End Sub

answered Apr 19, 2017 at 7:17

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Adryan Permana Over a year ago

Thanks. It work. How about if I want to select 2 certain range copy it and paste to other workbook in different columns. For Example; C2:E9 (copy) = C3 (Paste) B12:F15(copy) =D10 (Paste) B16:F17(copy)= D12(Paste)

Adryan Permana Over a year ago

Great, It works. Thank you. I am still new with VBA. How about if I want copy data from other workbook without open that workbook. I need to copy data from 20 workbooks. If I use this code, it will open 20 workbooks and it consume a lot of time or maybe error in the application. Is there any way to copy data without open the workbooks?

Adryan Permana Over a year ago

I tried to add PasteSpecial in your code but it doesn't work. How about if I want to use pastespecial?

Shai Rado Over a year ago

@AdryanPermana first I think I've fully answered the question you raised in your post so you can mark this as "ANSWER" , cy clicking on the gray check-mark next to my answer (it will turn green), read here meta.stackexchange.com/questions/5234/… . After, add a new post, with your modified code and describe what you are trying to achieve, it will allow all users here to assist you.

Adryan Permana Over a year ago

Ok, sorry my mistake. I already mark it. Thank your for your help. I really appreciate it :)

|

sda11 · Accepted Answer · 2017-04-19 08:52:11Z

0

Instead use C2:E9

Sub OpenCopyPaste()
' open the source workbook and select the source sheet
Workbooks.Open Filename:="C:\Users\Adryan Permana\Downloads\Test\part8.xlsx"
Sheets("Sheet1").Select
' copy the source range


Sheets("Sheet1").Range("C2:E9").Select
Selection.Copy

' select current workbook and paste the values starting at A1
Windows("report.xlsx").Activate
Sheets("Sheet1").Select
Sheets("Sheet1").Range("C3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

edited Apr 19, 2017 at 8:52

answered Apr 19, 2017 at 7:20

sda11

331 silver badge6 bronze badges

2 Comments

Adryan Permana Over a year ago

Thanks. It worked prefectly. I am still new with VBA. I just realise we can do like that. How about if I want to select 2 certain range copy it and paste to other workbook in different columns. For Example; C2:E9 (copy) = C3 (Paste) B12:F15(copy) =D10 (Paste)

sda11 Over a year ago

if you think this is an answer mark this as answer. Kindly ask this question not in comment section. I will be glad to answer that. Thank you ;)

Collectives™ on Stack Overflow

VBA copy selection range in loop

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related