Hello,

I need your help, I am not too good in IT, all I can say that I can make
macro just by the help of this newsgroup.

I do not know what is wrong with this macro. When I print a range, it
becomes a new range included in this macro (x).

Now I have the range which I do not know whre it comes from which added to
my macro. Anyone can help me to find where it comes from?

This is my macro, if somebody can help me to only do my following named
rangesL
dbasseA002
dbasseA003
dbasseA004

How can I put it to this macro:

Thanks very much

Frank

Sub AutoShape7_Click()
' Macro recorded 5/10/2007 by Frank
' This is to copy all the data from interface worksheets to this summaryWBLA
' Button no.4
ActiveWindow.SmallScroll ToRight:=-5
Range("A9:Y1714").Select
Selection.ClearContents
Dim x As Object
For Each x In ActiveWorkbook.Names
Range(x).Copy
Sheets("SUMMARYWBLA").Select
Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
Next x
Range("I2").Select
End Sub

It looks like you have a query or pivot table that added names into you
workbook which is normal. It think you need to skip over these unwanted
named ranges.

Sub AutoShape7_Click()
' Macro recorded 5/10/2007 by Frank
' This is to copy all the data from interface worksheets to this summaryWBLA
' Button no.4

'clear all data after row 9 in the worksheet
'Range("A9:Y1714").Select
'Selection.ClearContents
Rows("9:" & Rows.count).ClearContents
Dim x As Object
For Each x In ActiveWorkbook.Names
'skip named ranges starting with dbasse
if left(x.name,6) <> "dbasse" then
'Range(x).Copy
With Sheets("SUMMARYWBLA")
LastRow = .Range("A" & Rows.Count).End(xlup).row
NewRow = LastRow + 1
Range(x.name).Copy _
Destination:=.Range("A" & NewRow)
End with
End if
Next x
End Sub

"Frank Situmorang" wrote:

> Hello,
>
> I need your help, I am not too good in IT, all I can say that I can make
> macro just by the help of this newsgroup.
>
> I do not know what is wrong with this macro. When I print a range, it
> becomes a new range included in this macro (x).
>
> Now I have the range which I do not know whre it comes from which added to
> my macro. Anyone can help me to find where it comes from?
>
> This is my macro, if somebody can help me to only do my following named
> rangesL
> dbasseA002
> dbasseA003
> dbasseA004
>
> How can I put it to this macro:
>
> Thanks very much
>
> Frank
>
> Sub AutoShape7_Click()
> ' Macro recorded 5/10/2007 by Frank
> ' This is to copy all the data from interface worksheets to this summaryWBLA
> ' Button no.4
> ActiveWindow.SmallScroll ToRight:=-5
> Range("A9:Y1714").Select
> Selection.ClearContents
> Dim x As Object
> For Each x In ActiveWorkbook.Names
> Range(x).Copy
> Sheets("SUMMARYWBLA").Select
> Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
> Next x
> Range("I2").Select
> End Sub

Thanks very much Joel, your are awesome. It works perfectly after a bit
modifying your suggestion to be : if left(x.name,6) = "dbasse", which I think
you know that.

Again, thank you very very much.

Frank

"Joel" wrote:

> It looks like you have a query or pivot table that added names into you
> workbook which is normal. It think you need to skip over these unwanted
> named ranges.
>
> Sub AutoShape7_Click()
> ' Macro recorded 5/10/2007 by Frank
> ' This is to copy all the data from interface worksheets to this summaryWBLA
> ' Button no.4
>
> 'clear all data after row 9 in the worksheet
> 'Range("A9:Y1714").Select
> 'Selection.ClearContents
> Rows("9:" & Rows.count).ClearContents
> Dim x As Object
> For Each x In ActiveWorkbook.Names
> 'skip named ranges starting with dbasse
> if left(x.name,6) <> "dbasse" then
> 'Range(x).Copy
> With Sheets("SUMMARYWBLA")
> LastRow = .Range("A" & Rows.Count).End(xlup).row
> NewRow = LastRow + 1
> Range(x.name).Copy _
> Destination:=.Range("A" & NewRow)
> End with
> End if
> Next x
> End Sub
>
> "Frank Situmorang" wrote:
>
> > Hello,
> >
> > I need your help, I am not too good in IT, all I can say that I can make
> > macro just by the help of this newsgroup.
> >
> > I do not know what is wrong with this macro. When I print a range, it
> > becomes a new range included in this macro (x).
> >
> > Now I have the range which I do not know whre it comes from which added to
> > my macro. Anyone can help me to find where it comes from?
> >
> > This is my macro, if somebody can help me to only do my following named
> > rangesL
> > dbasseA002
> > dbasseA003
> > dbasseA004
> >
> > How can I put it to this macro:
> >
> > Thanks very much
> >
> > Frank
> >
> > Sub AutoShape7_Click()
> > ' Macro recorded 5/10/2007 by Frank
> > ' This is to copy all the data from interface worksheets to this summaryWBLA
> > ' Button no.4
> > ActiveWindow.SmallScroll ToRight:=-5
> > Range("A9:Y1714").Select
> > Selection.ClearContents
> > Dim x As Object
> > For Each x In ActiveWorkbook.Names
> > Range(x).Copy
> > Sheets("SUMMARYWBLA").Select
> > Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
> > Next x
> > Range("I2").Select
> > End Sub