Code moves worksheets columns to the left thus not showing column A

[ipbr21054]

Hi,
I use the code below but i see that column A is off the page.
My code searches for a value in column B
It does that fine but how do i stop column A beeing out of sight ?

Thanks

Private Sub PurchasedKey_Click()
  Dim sPath As String
  Dim strFileName As String
  Dim sh As Worksheet
  Dim wb As Workbook
 
  With ActiveSheet
    If .Range("Q1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
    If .Range("N1") = "M" Then
    
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " (SLS).pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    Else

    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    End If
    
    With ActiveSheet
    'ActiveWindow.SelectedSheets.PrintOut copies:=1
    Unload PrinterForm
    
    .Range("B3").Select
    Application.ScreenUpdating = False
    Dim C As Range
    Dim ans As String
    Dim Lastrow As Long
        ans = ActiveCell.Value
        Set wb = Application.Workbooks.Open("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR.xlsm")
        Lastrow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
    For Each C In Sheets("POSTAGE").Range("B1:B" & Lastrow)
        If C.Value = ans Then Application.Goto Reference:=wb.Sheets("POSTAGE").Range(C.Address): Exit For
    Next

 End With
 End With
          
          Application.Run ("'" & wb.Name & "'!openForm")
          Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Cannot test due to the lack of relevant files, but try changing the For ... Next loop near the bottom of the code to this

For Each C In Sheets("POSTAGE").Range("B1:B" & Lastrow)
  If C.Value = ans Then
    Application.Goto Reference:=wb.Sheets("POSTAGE").Range(C.Address)
    ActiveWindow.ScrollColumn = 1
    Exit For
Next

 

[ipbr21054]

I get Next withour For ?

Just a thought.
How about when this workbook is open have the code select column A then offset it by 1 so the column B is selected,obvioulsy not sure what to do there.

 

[Peter_SSs]

I get Next withour For ?

My mistake (as I couldn't actually test)

For Each C In Sheets("POSTAGE").Range("B1:B" & Lastrow)
  If C.Value = ans Then
    Application.Goto Reference:=wb.Sheets("POSTAGE").Range(C.Address)
    ActiveWindow.ScrollColumn = 1
    Exit For
  End If
Next

 

[ipbr21054]

Many thanks now wroking

 

[Peter_SSs]

You're welcome.