copying using a macro

[divad]

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 don’t 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 Guillett]

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).row
wb.range(cells(2,"a"),cells(sourcelastrow,"a")).copy _
.cells(destinationlastrow,"a")
end with
next wb

 

[divad]

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("D1517").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("D1517").Selectâ€
“Range("R15:â€until an empty line").Selectâ€, instead of:
“Range("R15:R17").Selectâ€

Thanks

Pedro

"Don Guillett" escreveu:

 

[Don Guillett]

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.