Excel Sheet loses focus.

[PWD]

Good afternoon. I know we don't like "Select" but I need to find some way to regain the focus of my starting sheet when executing the following code:-

x = 0
    With ActiveSheet
    
        StartSheet = .Name
        MyUsedRows = .UsedRange.Rows.Count
        MyUsedColumns = .UsedRange.Columns.Count
    
    End With

Title = Range("K3")

For a = 3 To MyUsedRows

If Range("K" & a) = Title Then
x = x + 1
Else: 'End of similar items so create a new sheet

With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Title
End With

Sheets(StartSheet).Range("a" & a - x).Resize(x, MyUsedColumns).Copy

Sheets(Title).Range("A2").PasteSpecial Paste:=xlPasteAll

Sheets(StartSheet).Select

If I don't "Select" the StartSheet then

x = 0
Title = Range("K" & a)
a = a - 1 'Need to go back 1 row
End If


Next a

Is incorrect as the focus is still on the sheet where the paste took place.

I rather optimistically tried:-

Sheets(StartSheet).Range("a" & a - x).Resize(x, MyUsedColumns).Copy Destination: Sheets(Title).Range ("A2")

But I get

Error 1004
The Clipboard cannot be emptied. Another program may be using the clipboard.

Any thoughts?

Many thanks,
D€$

 

[SkipVought]

You do With ActiveSheet...End With

Why not the same for your sheet in question?

I personally avoid Activesheet and rather use a specific or derived sheet reference.

The other thing that I do in nearly all my projects, is use the following naming comvention in my code...
[tt]
Sheet Name Sheet Code Name
Summary wsSummary
RQ wsRQ
[/tt]
then in my code I reference the sheet CodeName, so that if the user changes the Sheet Name, my code does not burp.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Thanks Skip, I'll have a look at that. Nearly quittin' time here!

Many thanks,
D€$

 

[SkipVought]

The other thing to keep in mind is that EVERY object, like your ranges in

x = 0
    With ActiveSheet
    
        StartSheet = .Name
        MyUsedRows = .UsedRange.Rows.Count
        MyUsedColumns = .UsedRange.Columns.Count
    
    End With[b][highlight]
'ref here for the sheet!!!
'      |                  
'      v                  [/highlight][/b]
Title = Range("K3")

For a = 3 To MyUsedRows
'.....

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

It's probably me just being a bit thick here but this

With Sheets(Title)
.Range("A2").PasteSpecial Paste:=xlPasteAll
End With

still keeps the focus on the new sheet. Ah, time to rewind; of course it does, because I've added it & renamed it:-

With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
[i] [b] ActiveSheet.Name = Title [/b] [/i]
End With

Sheets(StartSheet).Range("a" & a - x).Resize(x, MyUsedColumns).Copy

Sheets(Title).Range("A2").PasteSpecial Paste:=xlPasteAll

It IS the active sheet. I can't see any other way of getting back to the start sheet apart from:-

Sheets(StartSheet).Select

Many thanks,
D€$

 

[SkipVought]

Is there a reason to SELECT that sheet? I can only think of a very few instances where SELECT is necessary:
[tt]
1. It is the end of your procedure and you want the display to be on a particular workbook/sheet/cell for the user.

2. You need to set a Conditional Format and it seems that the only way to code that is the SELECT the CF range.

3. Hmmmmm? I'm at a loss to coe up with any other.
[/tt]
So what's the problem with using an explicit reference to a sheet?

Something I might do is IF the start sheet may not ALWAYS be the same sheet and I want to return to that sheet at the end...

  dim wsStrt as worksheet

  set wsStrt = Activesheet

'....... do all the stuff

  wsStrt.select

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip. OK, I'm probably coming at my task in the wrong way. Basically I need to examine Column "K" in the only WorkSheet (so far) in this WorkBook. This column is sorted low to high alphabetically. I need to create new WorkSheets based on rows of data with the same values & copy that data into it. Then I need to get back to the original sheet and do this until the end of the data.

Once I've inserted a new sheet, how do I get back to the original sheet to continue?

This is the code

    With ActiveSheet
    
        StartSheet = .Name
        MyUsedRows = .UsedRange.Rows.Count
        MyUsedColumns = .UsedRange.Columns.Count
    
    End With

Title = Range("K3")

For a = 3 To MyUsedRows 'Data starts on Row 3

If Range("K" & a) = Title Then
x = x + 1
Else: 'End of similar items so create a new sheet

With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Title
End With

Sheets(StartSheet).Range("a" & a - x).Resize(x, MyUsedColumns).Copy

'This is already the Active Sheet
Sheets(Title).Range("A3").PasteSpecial Paste:=xlPasteAll
'Need to have 2 x header rows

Many thanks,
D€$

 

[SkipVought]

    With ActiveSheet
    
        StartSheet = .Name
        MyUsedRows = .UsedRange.Rows.Count
        MyUsedColumns = .UsedRange.Columns.Count
    '[b][highlight]this makes no sense as this title, for multiple sheets, NEVER CHANGES!!![/highlight][/b]
        Title = .Range("K3")
    
        For a = 3 To MyUsedRows 'Data starts on Row 3
        
            If Range("K" & a) = Title Then
                x = x + 1
            Else: 'End of similar items so create a new sheet
            
                With ActiveWorkbook.Sheets.Add(after:=Worksheets(Worksheets.Count))
                '[b][highlight]this makes no sense as each time you add a sheet it gets the SAME TITLE VALUE!!![/highlight][/b]
                    .Name = Title

                    Sheets(StartSheet).Range("a" & a - x).Resize(x, MyUsedColumns).Copy
                    
                    .Range("A3").PasteSpecial Paste:=xlPasteAll
                End With
                
            End If
        Next
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip. Title does actually change due to the incrementation of "a".

Sheets(StartSheet).Select '******************

x = 0
Title = Range("K" & a)
a = a - 1 'Need to go back 1 row
End If


Next a

What I'm trying to figure out is how to get back to my "StartSheet" so I can continue with my loop:

For a = 3 To MyUsedRows 'Data starts on Row 3

If Range("K" & a) = Title Then
x = x + 1
Else: 'End of similar items so create a new sheet

With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Title
End With

Many thanks,
D€$

 

[SkipVought]

Try this simple test on a workbook with multiple sheets. Although each sheet is activated, the reference ALWAYS refers to the original ActiveSheet...

Sub test()
    Dim ws As Worksheet
    Sheets(1).Activate
    With ActiveSheet[b][highlight]
        MsgBox .Name[/highlight][/b]
        For Each ws In Worksheets
            ws.Activate[b][highlight]
            MsgBox .Name[/highlight][/b]
        Next
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip, it sure does! However the following code appears to select the new sheet:

With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Title
End With

Can this be avoided?

Many thanks,
D€$

 

[SkipVought]

It cnnot be avoided. It happens!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Sigh! Been off work for a few days, thanks anyway - at least I know

Many thanks,
D€$