Copy/paste range variable between workbooks
 

I cant get the paste to work below. I managed to get it work when only using
a normal range (b4), but when I try to use a variables as ranges it does not
work any more. Does anyone have a clue?


Sub Upload()

Dim fs, f, fc, f1, f2
Dim rng, rng1 As Range
Dim XLApp As Excel.Application, XLBook As Excel.Workbook
Dim i, j, k, l, rowcount As Integer

i = 3
j = 2
k = 4
l = 2
f2 = ThisWorkbook.Path

Set XLApp = New Excel.Application
Set rng = Worksheets("sheet1").Cells(i, j)

Set fs = CreateObject("scripting.filesystemobject")
Set f = fs.getfolder(f2 + "\files\")
Set fc = f.Files

For Each f1 In fc
Set XLBook = XLApp.Workbooks.Open(f1)
Set rng1 = XLBook.Sheets("business overview").Cells(k, l)

XLBook.Sheets("Business Overview").Cells(k, l).Copy
ThisWorkbook.Activate
Sheets("sheet1").Range(rng).Paste

XLBook.Close False

Next
End Sub

Copy/paste range variable between workbooks
 

Sheets("sheet1").Range(rng).Paste

will cause an error. Range does not accept a single range object as an
argument.
Why are you creating a new excel application just to open workbooks and copy
data? It certainly isn't necessary.

--
Regards,
Tom Ogilvy


"Jim73" wrote in message
...
I cant get the paste to work below. I managed to get it work when only
using
a normal range (b4), but when I try to use a variables as ranges it does
not
work any more. Does anyone have a clue?


Sub Upload()

Dim fs, f, fc, f1, f2
Dim rng, rng1 As Range
Dim XLApp As Excel.Application, XLBook As Excel.Workbook
Dim i, j, k, l, rowcount As Integer

i = 3
j = 2
k = 4
l = 2
f2 = ThisWorkbook.Path

Set XLApp = New Excel.Application
Set rng = Worksheets("sheet1").Cells(i, j)

Set fs = CreateObject("scripting.filesystemobject")
Set f = fs.getfolder(f2 + "\files\")
Set fc = f.Files

For Each f1 In fc
Set XLBook = XLApp.Workbooks.Open(f1)
Set rng1 = XLBook.Sheets("business overview").Cells(k, l)

XLBook.Sheets("Business Overview").Cells(k, l).Copy
ThisWorkbook.Activate
Sheets("sheet1").Range(rng).Paste

XLBook.Close False

Next
End Sub

Copy/paste range variable between workbooks
 

I need to open 120 workbooks (one at a time) and then make a copy over to one
single workbook and create a "small" database. To not have 1000 pages long
code i need to have a variable range in the workbooks. If there is another
way to open-copy-paste-close i would be very pleased. I tried "Open As #",
but did not get it to work.

Thanks in advance
/Jim

"Tom Ogilvy" wrote:

Sheets("sheet1").Range(rng).Paste

will cause an error. Range does not accept a single range object as an
argument.
Why are you creating a new excel application just to open workbooks and copy
data? It certainly isn't necessary.

--
Regards,
Tom Ogilvy


"Jim73" wrote in message
...
I cant get the paste to work below. I managed to get it work when only
using
a normal range (b4), but when I try to use a variables as ranges it does
not
work any more. Does anyone have a clue?


Sub Upload()

Dim fs, f, fc, f1, f2
Dim rng, rng1 As Range
Dim XLApp As Excel.Application, XLBook As Excel.Workbook
Dim i, j, k, l, rowcount As Integer

i = 3
j = 2
k = 4
l = 2
f2 = ThisWorkbook.Path

Set XLApp = New Excel.Application
Set rng = Worksheets("sheet1").Cells(i, j)

Set fs = CreateObject("scripting.filesystemobject")
Set f = fs.getfolder(f2 + "\files\")
Set fc = f.Files

For Each f1 In fc
Set XLBook = XLApp.Workbooks.Open(f1)
Set rng1 = XLBook.Sheets("business overview").Cells(k, l)

XLBook.Sheets("Business Overview").Cells(k, l).Copy
ThisWorkbook.Activate
Sheets("sheet1").Range(rng).Paste

XLBook.Close False

Next
End Sub