Please Help!! Macros

  • Thread starter Thread starter Neil Holden
  • Start date Start date
N

Neil Holden

Hello excel Guru's. I am trying to do something that is way to technical for
me!!

I have 12 excel sheets all in different locations, i need to pull the data
from all of them and updae my central excel sheet once a week, the reason i'm
doing this is becuase I am producing a crystal report and i need all the data
in a central location.

I need each excel sheet to start pulling the data through from certain rows
for example I need Excel 1 to start at row 4 - 100, excel 2 to start at row
105 - 205 and so on.

If you don't understand what I mean please say.

Thanks very much.

Neil.
 
so create a table with three columns
column A
contains the full names and paths of each workbook
"H:\location\pais\Dupon.xls"
Column B is the start row for the data
Column C is t he last row
name range the table, say "sourcefiles"

so now just read the table ....
I'm using the active workbook "sheet2" as my target

dim wb as workbook
dim ws as worksheet
dim cell as range

for each cell in acitivesheet.range("sourcefiles").Columns(1).cells
set wb = workbooks.open(cell.value)
with wb.activesheet
.Range(cell.offset(,1) & ":" & cell.offset(,2) ).Copy
worksheets("sheet2").Range("A65000").End(xlup).Offset(1).Pastespecial
xlPasteAll

end with
wb.close false
next
 
there's my inevitable typo in this line

For Each cell In ActiveSheet.Range("sourcefiles").Columns(1).Cells

and add a ThisWorkbook. to the paste line i
ThisWorkbook.Worksheets("sheet2").Range("A65000").End(xlUp).Offset(1).PasteSpecial xlPasteAll
 
Hi Patrick thanks for your help with this.

Column A i understand no problem
Column B, I need the entire row in this column?
Columns C, same apply to this one?

I have tried you code but unfortunately not had any luck.

I have placed the code is this worksheet:

Dim wb As Workbook
Dim ws As Worksheet
Dim cell As Range

For Each cell In ActiveSheet.Range("sourcefiles").Columns(1).Cells
Set wb = Workbooks.Open(cell.Value)
With wb.ActiveSheet
.Range(cell.Offset(, 1) & ":" & cell.Offset(, 2)).Copy

ThisWorkbook.Worksheets("sheet2").Range("A65000").End(xlUp).Offset(1).PasteSpecial xlPasteAll
xlPasteAll

End With
wb.Close False
Next

But not having much luck, i have named the table sourcefiles.

Thanks.
 
the code should be in a standard module, so in the development environment,
INSERT / Module

make sure that you have
OPTION EXPLICIT
at the top of the module - it forces you to properly DIM your variables and
is great for collecting typos :)

in B and C you put just a number representing the row, like 5 and 25
 
Back
Top