Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Can NOT copy / paste across workbooks | Excel Discussion (Misc queries) | |||
Can't Copy and Paste between Workbooks | Excel Discussion (Misc queries) | |||
How to copy&paste a variable range rows and colums | Excel Discussion (Misc queries) | |||
Paste a Range from a variable | Excel Programming |