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.

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"divad" <(E-Mail Removed)> wrote in message
news:1E3649C7-1FC5-481D-8EA2-(E-Mail Removed)...
>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,

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:

> 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
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "divad" <(E-Mail Removed)> wrote in message
> news:1E3649C7-1FC5-481D-8EA2-(E-Mail Removed)...
> >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.
> >
> >
>
>

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
(E-Mail Removed)
"divad" <(E-Mail Removed)> wrote in message
news:4129FD6D-2F27-4BDA-A1E0-(E-Mail Removed)...
> 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:
>
>> 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
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "divad" <(E-Mail Removed)> wrote in message
>> news:1E3649C7-1FC5-481D-8EA2-(E-Mail Removed)...
>> >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.
>> >
>> >
>>
>>