Cant Select temp Sheet

I have a routine where I create two temp sheets and
ATTEMPT to paste the contents of sheet 1 into both
sheets2 and sheets 3. The data is not showing up in the
temp sheets. In fact, even if I try to "sh2.Select" it
doesn't do anything. Does this have something to do with
the fact that I have used the Set command for both sh2
and sh3?


Dim TempSh2PasteCell As Range
Dim TempSh3PasteCell As Range
Dim CopyStartRow As Integer
Dim CopyStartCol As Integer
Dim TempSh2Cell As Range
Dim TempSh3Cell As Range

TempSh2Name = "AuditUFTempH"
TempSh3Name = "AuditUFTempV"

On Error Resume Next
Set sh2 = ActiveWorkbook.Sheets(TempSh2Name)
Set sh3 = ActiveWorkbook.Sheets(TempSh3Name)

If Not sh2 Is Nothing Then
Application.DisplayAlerts = False
sh2.Delete
Application.DisplayAlerts = True
End If

If Not sh3 Is Nothing Then
Application.DisplayAlerts = False
sh3.Delete
Application.DisplayAlerts = True
End If

With ActiveWorkbook
..Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name _
= TempSh2Name

..Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name _
= TempSh3Name
End With

For Each sh In ActiveWorkbook.Worksheets
'Set up past ranges in temp sheets
CopyStartRow = sh.UsedRange.Cells(1, 1).Row
CopyStartCol = sh.UsedRange.Cells(1, 1).Column
'Set Paste cell off 1 column to the right
Set TempSh2PasteCell = Worksheets
(TempSh2Name).Cells(CopyStartRow, CopyStartCol + 1)
'Set Paste cell off 1 row below
Set TempSh3PasteCell = Worksheets
(TempSh3Name).Cells(CopyStartRow + 1, CopyStartCol)

'Copy original range to new range off by 1 column
sh.UsedRange.Copy Destination:=Worksheets(sh2).Range
(TempSh2PasteCell.Address)

Copy original range to new range off by 1 row
sh.UsedRange.Copy Destination:=Worksheets(sh3).Range
(TempSh3PasteCell.Address)

Next

See my reply to previous question.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> I have a routine where I create two temp sheets and
> ATTEMPT to paste the contents of sheet 1 into both
> sheets2 and sheets 3. The data is not showing up in the
> temp sheets. In fact, even if I try to "sh2.Select" it
> doesn't do anything. Does this have something to do with
> the fact that I have used the Set command for both sh2
> and sh3?
>
>
> Dim TempSh2PasteCell As Range
> Dim TempSh3PasteCell As Range
> Dim CopyStartRow As Integer
> Dim CopyStartCol As Integer
> Dim TempSh2Cell As Range
> Dim TempSh3Cell As Range
>
> TempSh2Name = "AuditUFTempH"
> TempSh3Name = "AuditUFTempV"
>
> On Error Resume Next
> Set sh2 = ActiveWorkbook.Sheets(TempSh2Name)
> Set sh3 = ActiveWorkbook.Sheets(TempSh3Name)
>
> If Not sh2 Is Nothing Then
> Application.DisplayAlerts = False
> sh2.Delete
> Application.DisplayAlerts = True
> End If
>
> If Not sh3 Is Nothing Then
> Application.DisplayAlerts = False
> sh3.Delete
> Application.DisplayAlerts = True
> End If
>
> With ActiveWorkbook
> .Worksheets.Add(After:=.Worksheets
> (.Worksheets.Count)).Name _
> = TempSh2Name
>
> .Worksheets.Add(After:=.Worksheets
> (.Worksheets.Count)).Name _
> = TempSh3Name
> End With
>
> For Each sh In ActiveWorkbook.Worksheets
> 'Set up past ranges in temp sheets
> CopyStartRow = sh.UsedRange.Cells(1, 1).Row
> CopyStartCol = sh.UsedRange.Cells(1, 1).Column
> 'Set Paste cell off 1 column to the right
> Set TempSh2PasteCell = Worksheets
> (TempSh2Name).Cells(CopyStartRow, CopyStartCol + 1)
> 'Set Paste cell off 1 row below
> Set TempSh3PasteCell = Worksheets
> (TempSh3Name).Cells(CopyStartRow + 1, CopyStartCol)
>
> 'Copy original range to new range off by 1 column
> sh.UsedRange.Copy Destination:=Worksheets(sh2).Range
> (TempSh2PasteCell.Address)
>
> Copy original range to new range off by 1 row
> sh.UsedRange.Copy Destination:=Worksheets(sh3).Range
> (TempSh3PasteCell.Address)
>
> Next