How can I loop this

S

Steve B

I am trying to copy paste this range. Is there a way to loop this if
I want to change the variable "X" to 1 to 100?


Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\portfolio\Example X.xls")
Wb2.Sheets("Cashflow1").Range("L23:U47").Copy _
Wb1.Sheets("output").Range("c4")

Wb2.Close False
Application.ScreenUpdating = True

Thanks in advance
 
T

Tim Williams

dim x as integer

for x=1 to 100
.....
Set Wb2 = Workbooks.Open("C:\portfolio\Example " & x & ".xls")
.....
next x
 
D

David Adamson

SteveB try somthing like this

Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim FileN As String
Dim i As Integer


Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
For i = 1 To 100
FileN = "C:\portfolio\Example" & i & ".xls"

Set Wb2 = Workbooks.Open(FileN)
Wb2.Sheets("Cashflow1").Range("L23:U47").Copy _
Wb1.Sheets("output").Range("c4")

Wb2.Close False
Next i

Application.ScreenUpdating = True
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top