Define a range name using VB

  • Thread starter Thread starter Skeddy
  • Start date Start date
S

Skeddy

I have a macro setup that imports and copies data from two source files into
an excel spreadsheet.

What I want to do after I have imported all of the information is to select
the data, and then define it as a name for use in a pivot table.

The code I'm using at the moment to slect the range (no matter the lenght of
data) is:

********************
Sub RangeSelect()
' This will select all the current cells no matter the size
' of the data that has been imported
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select
End Sub
********************

The number of fields is never going to change, but the number of items
always will. The code above takes this into account.

What I now want to do is run the above to select the range of data and then
call it "data1" so I can then update a pivot table in the same work book.
Any ideas ?

For those that are interested, the main "body" of the macro starts off as
below:

********************
Sub OpenData()
' This is the really cool bit of the Macro
' This will get the current user name logged into the Windows Box
Dim X
Set X = CreateObject("WSCRIPT.Network")
Dim U
U = X.UserName
' Application.ScreenUpdating = False
' Ok, so now we are going to open up the two pivot table reports
' using the UserName from above to locate and open the file
Workbooks.OpenText Filename:="C:\WINNT\Profiles\" & X.UserName &
"\Desktop\ES_UKARDUE" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=
_
False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array _
(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1),
Array(11, 1), Array(12, 1), _
Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17,
1), Array(18, 1), Array( _
19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1),
Array(24, 1), Array(25, 1), _
Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30,
1), Array(31, 1), Array( _
32, 1))
' Now we are going to copy the data into the workbook
Windows("Pivot Make Up.xls").Activate
Sheets("Debtors").Select
Cells.Select
Selection.ClearContents
Windows("ES_UKARDUE").Activate
Cells.Select
Selection.Copy
Windows("Pivot Make Up.xls").Activate
ActiveSheet.Paste
' Again, we are now going to re-run the steps above, but only for the cash
report
Workbooks.OpenText Filename:="C:\WINNT\Profiles\" & X.UserName &
"\Desktop\ESUKSLTRAN" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=
_
False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array _
(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1),
Array(11, 1), Array(12, 1), _
Array(13, 1), Array(14, 1), Array(15, 1))
Windows("Pivot Make Up.xls").Activate
Sheets("Cash").Select
Cells.Select
Selection.ClearContents
Windows("ESUKSLTRAN").Activate
Cells.Select
Selection.Copy
Windows("Pivot Make Up.xls").Activate
ActiveSheet.Paste
' Now that the data has been copied, we need to close the files
' but we need to empty the clipboard first, other wise Excel has a fit.
Application.CutCopyMode = False
' So we've now emptied the clipboard and can safely exit the two open files.
Windows("ES_UKARDUE").Activate
ActiveWorkbook.Close SaveChanges:=False
Windows("ESUKSLTRAN").Activate
ActiveWorkbook.Close SaveChanges:=False
' Files are closed, user gets a prompt.
' Application.ScreenUpdating = True
MsgBox "All done......."
End Sub
********************
 
You could modify your code:

Sub RangeSelect()

dim myRng as range
set myRng = Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight))
myRng.name = "data1"
End Sub

But you don't actually need to name that range (or select it).

In your pivottable code, you have a sourcerange parm:

....sourcerange=myrng.address(external:=true), ...

If you delete/add rows to the data and want your pivottable to expand/contract,
you can use a dynamic range. Debra Dalgleish has some nice instructions at:

http://www.contextures.com/xlNames01.html#Dynamic
 
Have you thought recording a macro while selecting a range and then giving
it a name in the name box just to the left of the formula box.
 
Back
Top