I know this should be simple, but I'm pretty new to VBA and can't seem to get
it to work. My code:

Dim myWbk as Workbook
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

It never sets the value of the variable, so when I try:

Set ws1 = myWbk.Worksheets("Individuals")

I get "Run Time Error 91 - Object variable or With block variable not set"

What in the world am I doing wrong?

Thanks,

Cheryl

Set ws1 = Worksheets("Individuals")
--
Gary''s Student - gsnu200768


"Cheryl" wrote:

I know this should be simple, but I'm pretty new to VBA and can't seem to get
it to work. My code:

Dim myWbk as Workbook
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

It never sets the value of the variable, so when I try:

Set ws1 = myWbk.Worksheets("Individuals")

I get "Run Time Error 91 - Object variable or With block variable not set"

What in the world am I doing wrong?

Thanks,

Cheryl

That statement is only valid if the activeworkbook has not changed. The
posted code
Set ws1 = Worksheets("Individuals")
is the same as
Set ws1 = Activeworkbook.Worksheets("Individuals")

Since the user is undoubtedly working with 2 workbooks I see this as
inherantly dangerous as which workbook is active at a given moment can
change. By setting a workbook object to the active workbook the OP can always
access that book by referencing myWkb. I would recommend staying with their
existing code.

Just my 2 cents...

--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

Set ws1 = Worksheets("Individuals")
--
Gary''s Student - gsnu200768


"Cheryl" wrote:

I know this should be simple, but I'm pretty new to VBA and can't seem to get
it to work. My code:

Dim myWbk as Workbook
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

It never sets the value of the variable, so when I try:

Set ws1 = myWbk.Worksheets("Individuals")

I get "Run Time Error 91 - Object variable or With block variable not set"

What in the world am I doing wrong?

Thanks,

Cheryl

Just to clarify, no this is not necessarily simple. The fact that you are new
to VBA and you are setting workbook objects is impressive in and of itself...
On to your question. Your code looks good by me. Are you sure that you have a
sheet called
"Individuals". Check for blank spaces in the actual worksheet name since
"Individuals " is different from
"Individuals"

Beyond that try
Dim myWbk as Workbook
dim wks as worksheet
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

for each wks in myWkb.worksheets
msgbox wks.name
next wks
--
HTH...

Jim Thomlinson


"Cheryl" wrote:

I know this should be simple, but I'm pretty new to VBA and can't seem to get
it to work. My code:

Dim myWbk as Workbook
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

It never sets the value of the variable, so when I try:

Set ws1 = myWbk.Worksheets("Individuals")

I get "Run Time Error 91 - Object variable or With block variable not set"

What in the world am I doing wrong?

Thanks,

Cheryl

Thanks Jim. When I run your code, I do get message boxes with all the sheet
names in the active workbook. However, when I try to set ws1 and ws2, I still
get the same error. I've checked the sheet names, and they're spelled
correctly with no spaces. Any other thoughts?

"Jim Thomlinson" wrote:

Just to clarify, no this is not necessarily simple. The fact that you are new
to VBA and you are setting workbook objects is impressive in and of itself...
On to your question. Your code looks good by me. Are you sure that you have a
sheet called
"Individuals". Check for blank spaces in the actual worksheet name since
"Individuals " is different from
"Individuals"

Beyond that try
Dim myWbk as Workbook
dim wks as worksheet
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

for each wks in myWkb.worksheets
msgbox wks.name
next wks
--
HTH...

Jim Thomlinson


"Cheryl" wrote:

I know this should be simple, but I'm pretty new to VBA and can't seem to get
it to work. My code:

Dim myWbk as Workbook
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

It never sets the value of the variable, so when I try:

Set ws1 = myWbk.Worksheets("Individuals")

I get "Run Time Error 91 - Object variable or With block variable not set"

What in the world am I doing wrong?

Thanks,

Cheryl

simple type. You use:

myWbk and myWkb

Use only one of these
--
Gary''s Student - gsnu200768


"Cheryl" wrote:

Thanks Jim. When I run your code, I do get message boxes with all the sheet
names in the active workbook. However, when I try to set ws1 and ws2, I still
get the same error. I've checked the sheet names, and they're spelled
correctly with no spaces. Any other thoughts?

"Jim Thomlinson" wrote:

Just to clarify, no this is not necessarily simple. The fact that you are new
to VBA and you are setting workbook objects is impressive in and of itself...
On to your question. Your code looks good by me. Are you sure that you have a
sheet called
"Individuals". Check for blank spaces in the actual worksheet name since
"Individuals " is different from
"Individuals"

Beyond that try
Dim myWbk as Workbook
dim wks as worksheet
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

for each wks in myWkb.worksheets
msgbox wks.name
next wks
--
HTH...

Jim Thomlinson


"Cheryl" wrote:

I know this should be simple, but I'm pretty new to VBA and can't seem to get
it to work. My code:

Dim myWbk as Workbook
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

It never sets the value of the variable, so when I try:

Set ws1 = myWbk.Worksheets("Individuals")

I get "Run Time Error 91 - Object variable or With block variable not set"

What in the world am I doing wrong?

Thanks,

Cheryl

Still not working. Here's what I'm trying to do. I have a workbook that
someone else created with staffing data. In one of their worksheets, they've
used VLOOKUPS to make a data range dynamic based on the value of cell B2.
That's in the sheet named "Individuals".

I've created another sheet named "Indiv Stmts", where I've used VLOOKUPS as
well to pull the data from the appropriate cells in Individuals, and added a
chart at the bottom to graphically display the data. I want to loop through a
list of names in cells A4:A28 (or exiting the loop when the value of the next
cell is blank) in the sheet "Individuals" and set the value of cell B2 to the
name, then recalculate, thereby refreshing the data in my Indiv Stmts sheet.
Then I want to copy just that sheet to a new workbook, naming both the sheet
and the workbook with that person's name, thereby creating individual
workbooks for each person. Here is the whole module:

Sub IndivWkbk()
'Creates individual associate worksheets with estimated client hours for
each of their clients

Dim myWkk As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim WSNew As Worksheet
Dim cell As Range
Dim FolderName As String
Dim MyPath As String
Dim ansprint As String, msg As String, title As String, style As String
Dim strWorkBookName As String '
Dim r As Integer
Dim c As Integer
Dim rw As Integer

Set myWkb = ActiveWorkbook

With myWkb
Set ws1 = .Worksheets("Individuals")
Set ws2 = .Worksheets("Indiv Stmt")
End With

Set cell = ws1.Range("B2")
MyPath = ActiveWorkbook.Path
r = 4
c = 1

'Add a slash at the end of the path
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'Create folder for the new files
FolderName = MyPath & "Indiv Stmts " & Format(Now, "yyyy-mm-dd") & "\"
MkDir FolderName

'Message Box asks user if they want to print statements
msg = "New workbooks will be created for each associate. Do you want to
print individual statements?"
style = vbYesNo
title = "Print Statements?"
ansprint = MsgBox(msg, style, title)

With ws1
Do While r 0

cell.Value = Cells(r, 1)
Application.Calculate

'Add new workbook with one sheet
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
WSNew.Name = cell.Value
'Copy the individual statement to new workbook
With ws2 'Indiv Stmts
.Range("$A$1:$P$42").Copy
End With

With WSNew.Range("A1")
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

For rw = 11 To 18
With WSNew.Rows(rw)
If Cells(rw, 1) = "" Then
.RowHeight = 0
End If
End With
Next

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs FolderName & cell.Value, ws1.Parent.FileFormat

WSNew.Parent.Close False

If ansprint = "Yes" Then

strWorkBookName = FolderName & cell.Value & ".xls"
Workbooks.Open (strWorkBookName)
With Worksheets(cell.Value)
.PageSetup.PrintArea = "$A$1:$P$42"
.PageSetup.Orientation = xlLandscape
.PrintOut
End With
ActiveWorkbook.Close True

End If

r = r + 1

If Cells(r, 1) = "" Then
r = 0
End If

Loop

End With


MsgBox "Files have been created in " & FolderName

End Sub

********************
So far the code never sets the myWkb variable, so of course it's not setting
the worksheet variables either.


"Gary''s Student" wrote:

simple type. You use:

myWbk and myWkb

Use only one of these
--
Gary''s Student - gsnu200768


"Cheryl" wrote:

Thanks Jim. When I run your code, I do get message boxes with all the sheet
names in the active workbook. However, when I try to set ws1 and ws2, I still
get the same error. I've checked the sheet names, and they're spelled
correctly with no spaces. Any other thoughts?

"Jim Thomlinson" wrote:

Just to clarify, no this is not necessarily simple. The fact that you are new
to VBA and you are setting workbook objects is impressive in and of itself...
On to your question. Your code looks good by me. Are you sure that you have a
sheet called
"Individuals". Check for blank spaces in the actual worksheet name since
"Individuals " is different from
"Individuals"

Beyond that try
Dim myWbk as Workbook
dim wks as worksheet
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

for each wks in myWkb.worksheets
msgbox wks.name
next wks
--
HTH...

Jim Thomlinson


"Cheryl" wrote:

I know this should be simple, but I'm pretty new to VBA and can't seem to get
it to work. My code:

Dim myWbk as Workbook
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

It never sets the value of the variable, so when I try:

Set ws1 = myWbk.Worksheets("Individuals")

I get "Run Time Error 91 - Object variable or With block variable not set"

What in the world am I doing wrong?

Thanks,

Cheryl

Oh poop. Never mind. I figured it out. I left out a quotation mark. Sheesh!

One more question though. I have the new folder being created with the
current date for the name. If more than one iteration gets run on the same
day, I'd like it to delete the folder and its contents if it already exists.
Any simple code for accomplishing that?

"Cheryl" wrote:

Still not working. Here's what I'm trying to do. I have a workbook that
someone else created with staffing data. In one of their worksheets, they've
used VLOOKUPS to make a data range dynamic based on the value of cell B2.
That's in the sheet named "Individuals".

I've created another sheet named "Indiv Stmts", where I've used VLOOKUPS as
well to pull the data from the appropriate cells in Individuals, and added a
chart at the bottom to graphically display the data. I want to loop through a
list of names in cells A4:A28 (or exiting the loop when the value of the next
cell is blank) in the sheet "Individuals" and set the value of cell B2 to the
name, then recalculate, thereby refreshing the data in my Indiv Stmts sheet.
Then I want to copy just that sheet to a new workbook, naming both the sheet
and the workbook with that person's name, thereby creating individual
workbooks for each person. Here is the whole module:

Sub IndivWkbk()
'Creates individual associate worksheets with estimated client hours for
each of their clients

Dim myWkk As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim WSNew As Worksheet
Dim cell As Range
Dim FolderName As String
Dim MyPath As String
Dim ansprint As String, msg As String, title As String, style As String
Dim strWorkBookName As String '
Dim r As Integer
Dim c As Integer
Dim rw As Integer

Set myWkb = ActiveWorkbook

With myWkb
Set ws1 = .Worksheets("Individuals")
Set ws2 = .Worksheets("Indiv Stmt")
End With

Set cell = ws1.Range("B2")
MyPath = ActiveWorkbook.Path
r = 4
c = 1

'Add a slash at the end of the path
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'Create folder for the new files
FolderName = MyPath & "Indiv Stmts " & Format(Now, "yyyy-mm-dd") & "\"
MkDir FolderName

'Message Box asks user if they want to print statements
msg = "New workbooks will be created for each associate. Do you want to
print individual statements?"
style = vbYesNo
title = "Print Statements?"
ansprint = MsgBox(msg, style, title)

With ws1
Do While r 0

cell.Value = Cells(r, 1)
Application.Calculate

'Add new workbook with one sheet
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
WSNew.Name = cell.Value
'Copy the individual statement to new workbook
With ws2 'Indiv Stmts
.Range("$A$1:$P$42").Copy
End With

With WSNew.Range("A1")
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

For rw = 11 To 18
With WSNew.Rows(rw)
If Cells(rw, 1) = "" Then
.RowHeight = 0
End If
End With
Next

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs FolderName & cell.Value, ws1.Parent.FileFormat

WSNew.Parent.Close False

If ansprint = "Yes" Then

strWorkBookName = FolderName & cell.Value & ".xls"
Workbooks.Open (strWorkBookName)
With Worksheets(cell.Value)
.PageSetup.PrintArea = "$A$1:$P$42"
.PageSetup.Orientation = xlLandscape
.PrintOut
End With
ActiveWorkbook.Close True

End If

r = r + 1

If Cells(r, 1) = "" Then
r = 0
End If

Loop

End With


MsgBox "Files have been created in " & FolderName

End Sub

********************
So far the code never sets the myWkb variable, so of course it's not setting
the worksheet variables either.


"Gary''s Student" wrote:

simple type. You use:

myWbk and myWkb

Use only one of these
--
Gary''s Student - gsnu200768


"Cheryl" wrote:

Thanks Jim. When I run your code, I do get message boxes with all the sheet
names in the active workbook. However, when I try to set ws1 and ws2, I still
get the same error. I've checked the sheet names, and they're spelled
correctly with no spaces. Any other thoughts?

"Jim Thomlinson" wrote:

Just to clarify, no this is not necessarily simple. The fact that you are new
to VBA and you are setting workbook objects is impressive in and of itself...
On to your question. Your code looks good by me. Are you sure that you have a
sheet called
"Individuals". Check for blank spaces in the actual worksheet name since
"Individuals " is different from
"Individuals"

Beyond that try
Dim myWbk as Workbook
dim wks as worksheet
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

for each wks in myWkb.worksheets
msgbox wks.name
next wks
--
HTH...

Jim Thomlinson


"Cheryl" wrote:

I know this should be simple, but I'm pretty new to VBA and can't seem to get
it to work. My code:

Dim myWbk as Workbook
Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set myWkb = ActiveWorkbook

It never sets the value of the variable, so when I try:

Set ws1 = myWbk.Worksheets("Individuals")

I get "Run Time Error 91 - Object variable or With block variable not set"

What in the world am I doing wrong?

Thanks,

Cheryl