I have a big problem, and I need some help;
I have for about 2000 excel files in my computer, that contain each one
about 10 sheets inside. In one of the sheets I have a map, and I need to copy
some ranges (columns) to another workbook, using a macro.
The problem is that in each excel file (2000 different one), the range that
I want to copy has different sizes. I dont know how to make a formula that
say that the range is to be for example between A1 and an empty line.

workbook 1 workbook 2 new workbook
A A A
1 100‚¬ 50‚¬
2 200‚¬ 30‚¬
3 300‚¬
4

First time I need to copy the cells from A1 to A3 to the new workbook.
In a second time, after deleted the values in the new workbook, and using
the macro, it must copy de cells of the workbook from A1 to A2 again to the
new workbook.
So how can I say that in the first case the range stops at A3 and in the
second case it stops at A2?


Thanks for the help.

this is the idea
for each wb
with workbook("dest.xls").sheets("sheet1")
destinationlastrow=.cells(rows.count,"a").end(xlup ).row +1
sourcelastrow=wb.cells(rows.count,"a").end(xlup).r ow
wb.range(cells(2,"a"),cells(sourcelastrow,"a")).co py _
.cells(destinationlastrow,"a")
end with
next wb
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"divad" wrote in message
...
I have a big problem, and I need some help;
I have for about 2000 excel files in my computer, that contain each one
about 10 sheets inside. In one of the sheets I have a map, and I need to
copy
some ranges (columns) to another workbook, using a macro.
The problem is that in each excel file (2000 different one), the range
that
I want to copy has different sizes. I dont know how to make a formula
that
say that the range is to be for example between A1 and an empty line.

workbook 1 workbook 2 new workbook
A A A
1 100‚¬ 50‚¬
2 200‚¬ 30‚¬
3 300‚¬
4

First time I need to copy the cells from A1 to A3 to the new workbook.
In a second time, after deleted the values in the new workbook, and using
the macro, it must copy de cells of the workbook from A1 to A2 again to
the
new workbook.
So how can I say that in the first case the range stops at A3 and in the
second case it stops at A2?


Thanks for the help.

Don,

Thanks for the help... but I still need your support.

Here is my macro:

€œSub Macro3()
'
' Macro3 Macro
' Macro recorded 27-06-2008 by PEDRO DAVID MARTINS PEDROSA COSTA
'

'
Workbooks.Open Filename:="G:\BII\DCC\0_MaPAExpurgo.xls"
Range("A2:AA859").Select
Application.CutCopyMode = False
Selection.ClearContents
Windows("exp upi.xls").Activate
Range("H15:H17").Select
Selection.Copy
Windows("0_MaPAExpurgo.xls").Activate
Range("P2").Select
ActiveSheet.Paste
Windows("exp upi.xls").Activate
Range("D15:D17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0_MaPAExpurgo.xls").Activate
Range("H2").Select
ActiveSheet.Paste
Range("D6:W6").Select
Windows("exp upi.xls").Activate
Range("R15:R17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0_MaPAExpurgo.xls").Activate
Range("R2").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub€�

What I really need is to create €œsomething€� in the macro that says:
€œRange("H15:€�until an empty line").Select€�, instead of:
€œRange("H15:H17").Select€�
€œRange("D15:€�until an empty line").Select€�, instead of:
€œRange("D15:D17").Select€�
€œRange("R15:€�until an empty line").Select€�, instead of:
€œRange("R15:R17").Select€�

Thanks

Pedro

"Don Guillett" escreveu:

this is the idea
for each wb
with workbook("dest.xls").sheets("sheet1")
destinationlastrow=.cells(rows.count,"a").end(xlup ).row +1
sourcelastrow=wb.cells(rows.count,"a").end(xlup).r ow
wb.range(cells(2,"a"),cells(sourcelastrow,"a")).co py _
.cells(destinationlastrow,"a")
end with
next wb
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"divad" wrote in message
...
I have a big problem, and I need some help;
I have for about 2000 excel files in my computer, that contain each one
about 10 sheets inside. In one of the sheets I have a map, and I need to
copy
some ranges (columns) to another workbook, using a macro.
The problem is that in each excel file (2000 different one), the range
that
I want to copy has different sizes. I dont know how to make a formula
that
say that the range is to be for example between A1 and an empty line.

workbook 1 workbook 2 new workbook
A A A
1 100‚¬ 50‚¬
2 200‚¬ 30‚¬
3 300‚¬
4

First time I need to copy the cells from A1 to A3 to the new workbook.
In a second time, after deleted the values in the new workbook, and using
the macro, it must copy de cells of the workbook from A1 to A2 again to
the
new workbook.
So how can I say that in the first case the range stops at A3 and in the
second case it stops at A2?


Thanks for the help.

In which workbook do you want the macro to reside, source(exp upi.xls)or
destination.(0_MaPAExpurgo.xls)
Apparently you did not bother to try to implement my provided macro which
does NOT select. You really should try offered free solutions or contact me
privately for custom work.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"divad" wrote in message
...
Don,

Thanks for the help... but I still need your support.

Here is my macro:

€œSub Macro3()
'
' Macro3 Macro
' Macro recorded 27-06-2008 by PEDRO DAVID MARTINS PEDROSA COSTA
'

'
Workbooks.Open Filename:="G:\BII\DCC\0_MaPAExpurgo.xls"
Range("A2:AA859").Select
Application.CutCopyMode = False
Selection.ClearContents
Windows("exp upi.xls").Activate
Range("H15:H17").Select
Selection.Copy
Windows("0_MaPAExpurgo.xls").Activate
Range("P2").Select
ActiveSheet.Paste
Windows("exp upi.xls").Activate
Range("D15:D17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0_MaPAExpurgo.xls").Activate
Range("H2").Select
ActiveSheet.Paste
Range("D6:W6").Select
Windows("exp upi.xls").Activate
Range("R15:R17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("0_MaPAExpurgo.xls").Activate
Range("R2").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub€�

What I really need is to create €œsomething€� in the macro that says:
€œRange("H15:€�until an empty line").Select€�, instead of:
€œRange("H15:H17").Select€�
€œRange("D15:€�until an empty line").Select€�, instead of:
€œRange("D15:D17").Select€�
€œRange("R15:€�until an empty line").Select€�, instead of:
€œRange("R15:R17").Select€�

Thanks

Pedro

"Don Guillett" escreveu:

this is the idea
for each wb
with workbook("dest.xls").sheets("sheet1")
destinationlastrow=.cells(rows.count,"a").end(xlup ).row +1
sourcelastrow=wb.cells(rows.count,"a").end(xlup).r ow
wb.range(cells(2,"a"),cells(sourcelastrow,"a")).co py _
.cells(destinationlastrow,"a")
end with
next wb
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"divad" wrote in message
...
I have a big problem, and I need some help;
I have for about 2000 excel files in my computer, that contain each one
about 10 sheets inside. In one of the sheets I have a map, and I need
to
copy
some ranges (columns) to another workbook, using a macro.
The problem is that in each excel file (2000 different one), the range
that
I want to copy has different sizes. I dont know how to make a formula
that
say that the range is to be for example between A1 and an empty line.

workbook 1 workbook 2 new workbook
A A A
1 100‚¬ 50‚¬
2 200‚¬ 30‚¬
3 300‚¬
4

First time I need to copy the cells from A1 to A3 to the new workbook.
In a second time, after deleted the values in the new workbook, and
using
the macro, it must copy de cells of the workbook from A1 to A2 again to
the
new workbook.
So how can I say that in the first case the range stops at A3 and in
the
second case it stops at A2?


Thanks for the help.