Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cant Select temp Sheet
See my reply to previous question.
-- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem using obj.Namespace(sourceFileName) Namespace(DestinationFilename).Itemsand then opening the xls file which creates a Temp Folder inside Temp | Excel Discussion (Misc queries) | |||
How do I select price from sheet.b where sheet.a part no = sheet.b | Excel Worksheet Functions | |||
Use Sheet CodeNames to Select Sheet in Different Workbook | Excel Discussion (Misc queries) | |||
Macro, select Sheet "Number", NOT Sheet Name | Excel Worksheet Functions | |||
Select Sheet then Select Range | Excel Programming |