Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Always creates the new range which is not needed in the macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Always creates the new range which is not needed in the macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Always creates the new range which is not needed in the macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't highlight a single cell. Creates a range when I move curso | Excel Worksheet Functions | |||
Macro that creates a directory but... | Excel Programming | |||
Macro in VBA: Setting a variable print range HELP NEEDED!! | Excel Programming | |||
Excel query creates named range | Excel Programming | |||
Excel query creates named range | Excel Programming |