CODEWITHSUNDEEP
excelvba
iQuikDraw
iQuikDraw

Reputation: 1

How to select a specific cell on a worksheet?

I am importing a report, posting it to a worksheet, creating a new tab (Worksheet - Named by "W(Date)" and creating a MS Query table from the original to make a filtered table on the new tab.

When I name the new sheet, I have a summary data sheet where I want to input the new sheet name to the first open cell in column F. I can then make a dynamic list from the sheet names to pull data by sheet to the charts page at the front of the workbook.

The section below, when I get to the line that states wsDest1.Range("F1" & RowCount).Select the cursor jumps down to line 110 in the column, instead of row 11.

Using the immediate window with Print wsDest1.Cells(wsDest1.Rows.Count, "F").End(xlUp).Row returns "10". Using Print activesheet.usedrange.address returns $A$1:$I$10 so for column "F" it should still be 11 to place the cursor.

Most variables are declared at the top of the script, or are immediate declarations before the action. the script runs without errors except for the cursor placement at the end of the script.

Where is the extra 100 lines coming from?

    Sheets.Add After:=Sheets(Worksheets.Count)
    'Sheets("Sheet1").Select
        
    Set wks = ActiveSheet
    Do While sName <> wks.Name
        sName = Application.InputBox _
          (Prompt:="Enter new worksheet name")
        On Error Resume Next
        wks.Name = sName
        On Error GoTo 0
    Loop
        
    Set wks = Nothing
         
    'Define value "n" = sName
    n = sName
            
    Set wsDest1 = Workbooks("TG Shipped Lines_Late Shipment Tracking_WorkInProgress.xlsm").Worksheets("SUMMARY DATA")
        
    wsDest1.Activate
        
    RowCount = wsDest1.Cells(wsDest1.Rows.Count, "F").End(xlUp).Row
        
    wsDest1.Range("F1" & RowCount).Select
        
    Selection.Value = n
        
    Set wsDest1 = Nothing
        
End Sub

Upvotes: 0

Views: 525

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

wsDest1.Range("F1" & RowCount).Select

should be

wsDest1.Range("F" & RowCount).Select

EDIT: as other have pointed out there's no need for any select/activate:

    Dim wks As Worksheet, wb As Workbook, sName
    
    Set wb = ActiveWorkbook
    
    Set wks = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    Do While sName <> wks.Name
        sName = Application.InputBox(Prompt:="Enter new worksheet name")
        On Error Resume Next
        wks.Name = sName
        On Error GoTo 0
    Loop
    
    'If "TG Shipped Lines_Late Shipment Tracking_WorkInProgress.xlsm"
    '   is where this code is running, then this line will do the rest
    ThisWorkbook.Worksheets("SUMMARY DATA").Cells(Rows.Count, "F") _
            .End(xlUp).Offset(1, 0).Value = wks.Name

Upvotes: 3

Related Questions