I need some help fixing some code. I'm still learning about VB so
hopefully, this will be easy for someone else.

I'm trying to create a pivot table from a list of cells with a variable
length. I'm trying to use the LASTROW function from Ron DeBruin and I can't
seem to get it to work. I want the LASTROW function to look at data in
worksheet "WorksheetNames"

Set SourceSh = ThisWorkbook.WorksheetNames
SourceSh.Name = "WorksheetNames"
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)

Debug.Print Last
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"WorksheetNames!$A$1:$C$" & Last).CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


Thanks in advance,

Barb Reinhardt

What does LastRow do? Does it expect a worksheet or a range?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Barb Reinhardt" wrote in message
...
I need some help fixing some code. I'm still learning about VB so
hopefully, this will be easy for someone else.

I'm trying to create a pivot table from a list of cells with a variable
length. I'm trying to use the LASTROW function from Ron DeBruin and I
can't
seem to get it to work. I want the LASTROW function to look at data in
worksheet "WorksheetNames"

Set SourceSh = ThisWorkbook.WorksheetNames
SourceSh.Name = "WorksheetNames"
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)

Debug.Print Last
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"WorksheetNames!$A$1:$C$" & Last).CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


Thanks in advance,

Barb Reinhardt

Also, is there a worksheet with a code name of Worksheetnames, judging by
the code it seems unlikely. You cannot set a worksheet variable to a
non-existent sheet.

And does the code error, or just not work? If the former, at which line?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Barb Reinhardt" wrote in message
...
I need some help fixing some code. I'm still learning about VB so
hopefully, this will be easy for someone else.

I'm trying to create a pivot table from a list of cells with a variable
length. I'm trying to use the LASTROW function from Ron DeBruin and I
can't
seem to get it to work. I want the LASTROW function to look at data in
worksheet "WorksheetNames"

Set SourceSh = ThisWorkbook.WorksheetNames
SourceSh.Name = "WorksheetNames"
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)

Debug.Print Last
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"WorksheetNames!$A$1:$C$" & Last).CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


Thanks in advance,

Barb Reinhardt

Here is the DeBraun function LASTROW

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function



"Bob Phillips" wrote:

Also, is there a worksheet with a code name of Worksheetnames, judging by
the code it seems unlikely. You cannot set a worksheet variable to a
non-existent sheet.

And does the code error, or just not work? If the former, at which line?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Barb Reinhardt" wrote in message
...
I need some help fixing some code. I'm still learning about VB so
hopefully, this will be easy for someone else.

I'm trying to create a pivot table from a list of cells with a variable
length. I'm trying to use the LASTROW function from Ron DeBruin and I
can't
seem to get it to work. I want the LASTROW function to look at data in
worksheet "WorksheetNames"

Set SourceSh = ThisWorkbook.WorksheetNames
SourceSh.Name = "WorksheetNames"
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)

Debug.Print Last
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"WorksheetNames!$A$1:$C$" & Last).CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


Thanks in advance,

Barb Reinhardt

Your first couple of lines are confusing. If you already have a sheet with
a tab name of WorksheetNames then


Dim SourceSh as Worksheet
Set SourceSh = ThisWorkbook.Worksheet("WorksheetNames")
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)
Debug.Print Last

If not and you want to rename it to WorksheetNames, then how are you
determining which sheet to work with?

--
Regards,
Tom Ogilvy

"Barb Reinhardt" wrote in message
...
I need some help fixing some code. I'm still learning about VB so
hopefully, this will be easy for someone else.

I'm trying to create a pivot table from a list of cells with a variable
length. I'm trying to use the LASTROW function from Ron DeBruin and I
can't
seem to get it to work. I want the LASTROW function to look at data in
worksheet "WorksheetNames"

Set SourceSh = ThisWorkbook.WorksheetNames
SourceSh.Name = "WorksheetNames"
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)

Debug.Print Last
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"WorksheetNames!$A$1:$C$" & Last).CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


Thanks in advance,

Barb Reinhardt

I get error

Method or data member not found

on this line

Set SourceSh = ThisWorkbook.Worksheet("WorksheetNames")

The source code is in personal.xls and I'm using another workbook.

"Tom Ogilvy" wrote:

Your first couple of lines are confusing. If you already have a sheet with
a tab name of WorksheetNames then


Dim SourceSh as Worksheet
Set SourceSh = ThisWorkbook.Worksheet("WorksheetNames")
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)
Debug.Print Last

If not and you want to rename it to WorksheetNames, then how are you
determining which sheet to work with?

--
Regards,
Tom Ogilvy

"Barb Reinhardt" wrote in message
...
I need some help fixing some code. I'm still learning about VB so
hopefully, this will be easy for someone else.

I'm trying to create a pivot table from a list of cells with a variable
length. I'm trying to use the LASTROW function from Ron DeBruin and I
can't
seem to get it to work. I want the LASTROW function to look at data in
worksheet "WorksheetNames"

Set SourceSh = ThisWorkbook.WorksheetNames
SourceSh.Name = "WorksheetNames"
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)

Debug.Print Last
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"WorksheetNames!$A$1:$C$" & Last).CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


Thanks in advance,

Barb Reinhardt

either

Set SourceSh = ActiveWorkbook.Worksheet("WorksheetNames")

or reference a specific open workbook

Set SourceSh = Workbooks("Myworkbook.xls").Worksheets("WorksheetN ames")

However, since you want to select the sheet, I would assume you are working
with the activeworkbook. If not, then instead of

Sheets("WorksheetNames").Select

( or even SourceSh.Select )

use
Application.Goto Reference:=SourceSh



--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote in message
...
I get error

Method or data member not found

on this line

Set SourceSh = ThisWorkbook.Worksheet("WorksheetNames")

The source code is in personal.xls and I'm using another workbook.

"Tom Ogilvy" wrote:

Your first couple of lines are confusing. If you already have a sheet
with
a tab name of WorksheetNames then


Dim SourceSh as Worksheet
Set SourceSh = ThisWorkbook.Worksheet("WorksheetNames")
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)
Debug.Print Last

If not and you want to rename it to WorksheetNames, then how are you
determining which sheet to work with?

--
Regards,
Tom Ogilvy

"Barb Reinhardt" wrote in
message
...
I need some help fixing some code. I'm still learning about VB so
hopefully, this will be easy for someone else.

I'm trying to create a pivot table from a list of cells with a
variable
length. I'm trying to use the LASTROW function from Ron DeBruin and
I
can't
seem to get it to work. I want the LASTROW function to look at data
in
worksheet "WorksheetNames"

Set SourceSh = ThisWorkbook.WorksheetNames
SourceSh.Name = "WorksheetNames"
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)

Debug.Print Last
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"WorksheetNames!$A$1:$C$" & Last).CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


Thanks in advance,

Barb Reinhardt

It worked with this

Set SourceSh = ActiveWorkbook.WorksheetS("WorksheetNames")


"Tom Ogilvy" wrote:

either

Set SourceSh = ActiveWorkbook.Worksheet("WorksheetNames")

or reference a specific open workbook

Set SourceSh = Workbooks("Myworkbook.xls").Worksheets("WorksheetN ames")

However, since you want to select the sheet, I would assume you are working
with the activeworkbook. If not, then instead of

Sheets("WorksheetNames").Select

( or even SourceSh.Select )

use
Application.Goto Reference:=SourceSh



--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote in message
...
I get error

Method or data member not found

on this line

Set SourceSh = ThisWorkbook.Worksheet("WorksheetNames")

The source code is in personal.xls and I'm using another workbook.

"Tom Ogilvy" wrote:

Your first couple of lines are confusing. If you already have a sheet
with
a tab name of WorksheetNames then


Dim SourceSh as Worksheet
Set SourceSh = ThisWorkbook.Worksheet("WorksheetNames")
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)
Debug.Print Last

If not and you want to rename it to WorksheetNames, then how are you
determining which sheet to work with?

--
Regards,
Tom Ogilvy

"Barb Reinhardt" wrote in
message
...
I need some help fixing some code. I'm still learning about VB so
hopefully, this will be easy for someone else.

I'm trying to create a pivot table from a list of cells with a
variable
length. I'm trying to use the LASTROW function from Ron DeBruin and
I
can't
seem to get it to work. I want the LASTROW function to look at data
in
worksheet "WorksheetNames"

Set SourceSh = ThisWorkbook.WorksheetNames
SourceSh.Name = "WorksheetNames"
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)

Debug.Print Last
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"WorksheetNames!$A$1:$C$" & Last).CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


Thanks in advance,

Barb Reinhardt

Yep, sometimes I make typos. Glad you figured it out.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote in message
...
It worked with this

Set SourceSh = ActiveWorkbook.WorksheetS("WorksheetNames")


"Tom Ogilvy" wrote:

either

Set SourceSh = ActiveWorkbook.Worksheet("WorksheetNames")

or reference a specific open workbook

Set SourceSh = Workbooks("Myworkbook.xls").Worksheets("WorksheetN ames")

However, since you want to select the sheet, I would assume you are
working
with the activeworkbook. If not, then instead of

Sheets("WorksheetNames").Select

( or even SourceSh.Select )

use
Application.Goto Reference:=SourceSh



--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote in
message
...
I get error

Method or data member not found

on this line

Set SourceSh = ThisWorkbook.Worksheet("WorksheetNames")

The source code is in personal.xls and I'm using another workbook.

"Tom Ogilvy" wrote:

Your first couple of lines are confusing. If you already have a
sheet
with
a tab name of WorksheetNames then


Dim SourceSh as Worksheet
Set SourceSh = ThisWorkbook.Worksheet("WorksheetNames")
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)
Debug.Print Last

If not and you want to rename it to WorksheetNames, then how are you
determining which sheet to work with?

--
Regards,
Tom Ogilvy

"Barb Reinhardt" wrote in
message
...
I need some help fixing some code. I'm still learning about VB
so
hopefully, this will be easy for someone else.

I'm trying to create a pivot table from a list of cells with a
variable
length. I'm trying to use the LASTROW function from Ron DeBruin
and
I
can't
seem to get it to work. I want the LASTROW function to look at
data
in
worksheet "WorksheetNames"

Set SourceSh = ThisWorkbook.WorksheetNames
SourceSh.Name = "WorksheetNames"
Sheets("WorksheetNames").Select
Debug.Print Last
Last = LastRow(SourceSh)

Debug.Print Last
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"WorksheetNames!$A$1:$C$" & Last).CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


Thanks in advance,

Barb Reinhardt