Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range Issue
Hello,
Would like to copy all the Ranged Names starting with "VBA" to a new workbook. 1. Syntax jams on : Range(nme.Name).Copy '''''GRRRRRRR''''''' 2. I do not create a new workbook for every name I am exporting do I? Sige Sub sige() Dim ThisBook As Workbook Dim ExpBook As Workbook Dim nme As Name Set ThisBook = ActiveWorkbook Set ExpBook = Workbooks.Add(xlWorksheet) For Each nme In ThisBook.Names If Left(nme.Name, 3) = "VBA" Then MsgBox nme.Name Range(nme.Name).Copy '''''GRRRRRRR''''''' With ExpBook .Worksheets(1).Range(Range(nme.Name).Address).Past e .SaveAs FileName:=ThisWorkbook.Path & "\temp.xls", _ FileFormat:=xlWorkbook .Close SaveChanges:=False If Err < 0 Then MsgBox "Cannot export" & _ ThisWorkbook.Path & "\temp.xls" End With Else MsgBox "No names to export" Exit Sub End If Next nme End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range Issue
Hi Don,
I created those names through VBA, thats why I give them a name beginning with "VBA"... and they refer to sortalike ranges: =Sheet1!$B$4:$B$27 ... Sige "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range Issue
Sige,
For Each nme In ThisBook.Names If Left(nme.Name, 3) = "VBA" Then MsgBox nme.Name With ExpBook Range(nme).Copy .Worksheets(1).Range(Range(nme).Address) ''''' No mor GRRRRRRR''''''' HTH, Bernie MS Excel MVP "SIGE" wrote in message ... Hi Don, I created those names through VBA, thats why I give them a name beginning with "VBA"... and they refer to sortalike ranges: =Sheet1!$B$4:$B$27 .. Sige "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range Issue
Hi Bernie, Thanks .... but "Still Grrr" ;o) On : Range(nme).Copy .Worksheets(1).Range(Range(nme).Address) '''''No mor GRRRRRRR''''''' I get an Run-time error- '2147221080(800401a8)':Automation error. Sige Grrr :o))) "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range Issue
Sige,
You have all sorts of other problems with the code, due to bad logic. You copy for one name, then save and close the workbook. Your flow might be this, if you want to export wach named range to its own workbook Dim counter As Integer counter = 0 For Each nme In ThisBook.Names If Left(nme.Name, 3) = "VBA" Then Set ExpBook = Workbooks.Add(xlWorksheet) counter = counter +1 MsgBox nme.Name With ExpBook Range(nme).Copy .Worksheets(1).Range(Range(nme).Address) .SaveAs Filename:=ThisWorkbook.Path & "\temp" & counter" & ".xls", _ FileFormat:=xlWorkbook .Close SaveChanges:=False If Err < 0 Then MsgBox "Cannot export" & _ ThisWorkbook.Path & "\temp.xls" End With Else MsgBox "No names to export" Exit Sub End If Next nme If you want to export all the named ranges to a single workbook, then it would be Set ExpBook = Workbooks.Add(xlWorksheet) With ExpBook For Each nme In ThisBook.Names If Left(nme.Name, 3) = "VBA" Then MsgBox nme.Name Range(nme).Copy ..Worksheets(1).Range(Range(nme).Address) If Err < 0 Then MsgBox "Cannot export" & _ ThisWorkbook.Path & "\temp.xls" End With End If Next nme .SaveAs Filename:=ThisWorkbook.Path & "\temp.xls", _ FileFormat:=xlWorkbook .Close SaveChanges:=False End With HTH, Bernie MS Excel MVP "SIGE" wrote in message ... Hi Bernie, Thanks .... but "Still Grrr" ;o) On : Range(nme).Copy .Worksheets(1).Range(Range(nme).Address) '''''No mor GRRRRRRR''''''' I get an Run-time error- '2147221080(800401a8)':Automation error. Sige Grrr :o))) "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range Issue
Sub sige()
Dim ThisBook As Workbook Dim ExpBook As Workbook Dim nme As Name Dim rng as Range Set ThisBook = ActiveWorkbook Set ExpBook = Workbooks.Add(xlWorksheet) For Each nme In ThisBook.Names If Left(Ucase(nme.Name, 3)) = "VBA" Then MsgBox nme.Name set rng = nme.ReferstoRange rng.copy With ExpBook .Worksheets(1).Range(rng.Address).Paste If Err < 0 Then MsgBox "Cannot export" & _ ThisWorkbook.Path & "\temp.xls" End With Else MsgBox "No names to export" Exit Sub End If Next nme With ExpBook .SaveAs FileName:=ThisWorkbook.Path & "\temp.xls", _ FileFormat:=xlWorkbook .Close SaveChanges:=False End With End Sub -- Regards, Tom Ogilvy "SIGE" wrote in message ... Hi Bernie, Thanks .... but "Still Grrr" ;o) On : Range(nme).Copy .Worksheets(1).Range(Range(nme).Address) '''''No mor GRRRRRRR''''''' I get an Run-time error- '2147221080(800401a8)':Automation error. Sige Grrr :o))) "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range Issue
If the name of the name is
vba5 vba6 etc then us Sub eachname() For Each n In Names If UCase(Left(n.Name, 3)) = "VBA" Then MsgBox n.Name Next End Sub -- Don Guillett SalesAid Software "SIGE" wrote in message ... Hi Don, I created those names through VBA, thats why I give them a name beginning with "VBA"... and they refer to sortalike ranges: =Sheet1!$B$4:$B$27 .. Sige "NOSPAM" to be removed for direct mailing... *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Copy Paste Issue | Excel Discussion (Misc queries) | |||
Copy & Paste Issue | Excel Discussion (Misc queries) | |||
Copy Paste issue | Excel Worksheet Functions | |||
Copy Chart issue | Charts and Charting in Excel | |||
Issue with copy & paste? | Excel Discussion (Misc queries) |