Need help with ForEach-If-ElseIf situation

[wannabburly]

Trying to write code that checks Worksheet names against a data range that I update daily.

Problem is I can't get it to consistently work. I'm having trouble with either creating the sheets or updating them.


For Each oCell In rngCreateSheets
If oCell.Value = wsNames Then
'Run Update Code
ElseIf oCell.Value <> wsNames Then
'Run Create Code
End If
Next oCell

That code works fine if there are no sheets existing, but when I rerun the code to update the sheets, it tries to create and name a sheet it has already created and I get an error. If I switch the equals and not equals once the sheets are created, it updates appropriately.

[SamT]

Option Explicit


Function SheetExists(SheetName As String) As Boolean
Dim Sht As Worksheet
For Each Sht In Worksheets
If Sht.Name = SheetName Then
SheetExists = True
Exit For
Next Sht
End Function


Sub DoSomething()
For Each oCell In rngCreateSheets
If Not SheetExists(oCell.Value) Then
'Run Create Code
End If

'Run Update Code

Next oCell

End Sub

[wannabburly]

Thanks. That helps a lot.

Now, part of the Create code is to create the sheet, set it as ActiveSheet and go on about copy & pasting values from the Input sheet. For my Update code, how can I get the program to Activate the sheet with the name that matches the oCell.Value?

[wannabburly]

Or I guess it doesn't necessarily have to Activate the sheet. I just need the sheet that matches the cell value to be the destination.

[SamT]

With Sheets(oCell.Value)
'Do stuff to sheet
End With

[wannabburly]

How do I set that Sheet as my destination for Copy & Paste?

Forgive me, this is the first time I've tried to use VBA.

[SamT]

Best is to assign the Name to the Sheet when it is created

Dim sht as Worksheet
Dim ShtName As String

Worksheets.Add
Active sheet name = oCell.Value
Set Sht = ActiveSheet 'Hereafter, don't use "ActiveSheet"
ShtName = oCell.Value
Sheets("Sheet1").Range("A1").Copy Sht.Range("B2")
Sheets("Sheet1").Range("A1").Copy Sheets(ShtName).Range("B2")

[wannabburly]

Here is the beginning of my Create Code...


For Each oCell In rngCreateSheets
If Not SheetExists(oCell.Value) Then

oTemplate.Copy After:=Worksheets(Sheets.Count)
Set oDest = ActiveSheet
oDest.Name = oCell.Value
oDest.Range("B5").Value = oCell.Value
'Repetitive Copy & Paste...


Here is the Update Code, including the End If from above...

End If

With Sheets(oCell.Value)

oSource.Cells(1, 2).Copy
oDest2.Range("I2").PasteSpecial Paste:=xlPasteValues 'As an example

'Update Code, which is just more Copy & Paste with some appending going on also
End With
Next oCell


The first 5 sheets in my workbook are "Working", "Hidden Data", "Read Me", "Input", "Template" with the created sheets going successively after "Template".

The Code creates sheets fine, but will revert back to the "Input" sheet (where I have a form control button to run the Macro) to start doing the Updating as opposed to going to the sheet whose name matches the current oCell.Value to start the Updating.

From what I can tell, when creating the sheet, it correctly goes down my range of numbers used to dictate what sheets are created, but when the sheet is already created it is not correctly stepping back through that range.

[SamT]

oDest2 ?

Here is Example Code, including the End If from above...


End If

With oDest 'A sheet OBJECT

oSource.Cells(1, 2).Copy
'Dot Range (.Range) refers to the "With" OBJECT
.Range("I2").PasteSpecial Paste:=xlPasteValues 'As an example

'Update Code, which is just more Copy & Paste with some appending going on also
End With

Next oCell

[wannabburly]

Thanks, I'll give that a try and report back.

oDest is declared prior to For Each loop As Worksheet, as is oDest2.

Previously, I had the Create and Update codes together within the For Each loop, so I thought I needed some way to differentiate the destination for Create copying & pasting, and Update copying & pasting. They are now separated by the If Then loop.

Again, making mistakes and learning as I go...

Can I remove the oDest prior to the .Range across the code since the oDest is declared right after the sheet is created? Or do I have to keep it in my Create code since I'm only using "With" in my Update code?

[wannabburly]

Ok, I made the Update portion of the code start out as you provided above.

I now get "Run-time error '91': Object Variable or With block variable not set.

When I select Debug, this line is highlighted.

.Range("I2").PasteSpecial Paste:=xlPasteValues

[SamT]

Instead of using oDest, why not use DestSht, SrcSht, & DestRng. IMO, they are eminently more readable and most VBA code is too short to really need Apps Hungarian notation. If you don't know that Sht, WkSh, WkSht, & WSht stand for Worksheet and that a Worksheet is an object, you haven't even started to learn VBA For Excel. The Same for Range and Cell: Rng & Cel. See my Profile for threads I have posted in for more examples of my style.

Having said all that, the best style for you is the one that you develop yourself and use daily. See snb' Profile for examples of his style.

[wannabburly]

At this point, I'm not concerned about which notation to use I have a project that I'm trying to complete using Macros and I'm going about it the only way I know how. I'm not a coder or programmer, nor do I claim to be, so unfortunately right now its a lot of trial and error. For the most part, I've found code from others and adapted it to my situation, adding and deleting things as necessary.

I appreciate your help, which is what I came here for. I'll see what I can take away from the threads on your profile.