Code is returning to vbnullstring

sarvadeshavyapta

New Member
Joined
Jul 31, 2015
Messages
30
Code:
Sub Newreport()
    Dim ws  As Worksheet    ' Worksheet object for file to save
    Dim wb  As Workbook     ' Workbook object for file to save
    
    'Disable Ctrl+S
    With Application
    .OnKey "^s", ""
    End With
    
    'Set ws in which GRIN data gets loaded
    On Error Resume Next
    Set ws = Sheets("Sheet1")
    
    'If 1st step is missed
    If ws Is Nothing Then
     UserForm2.Show
     
     End If
    If ws.AutoFilterMode = True Then ws.AutoFilterMode = False 'turns off filters if any
     'Copying GRIN datas from sheet 1 to report.Although Select is unrecommneded in VBA, using it as the same is required
    'Copy Item code
    Static curr As Range
    Static curr1 As Range
    
    If curr Is Nothing Then Set curr = Worksheets("Sheet1").Range("B2")
    If curr1 Is Nothing Then Set curr1 = Worksheets("Sheet1").Range("C2")
    
    If curr = vbNullString Then
    MsgBox "No Item code found"
    Exit Sub
    End If
    If curr1.Value = vbNullString Then
    MsgBox "No GRIN found"
    Exit Sub
    End If
    Worksheets("Sheet1").Activate
curr.Select
curr1.Select
Worksheets("Fresh").Range("M6").Value = curr.Value
    Worksheets("Fresh").Range("D8").Value = curr1.Value
Set curr = curr.Offset(1, 0)
    Set curr1 = curr1.Offset(1, 0)
Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("Fresh")
Range("X6") = Chr(Month(Now) + 64)
    
   On Error Resume Next
   Set C = Range("Y6")
    
   For Each cc In C
   cc.Value = cc.Value + 1
   Next
    ' Fill creation date
  Range("AA6") = Now
    ' Release objects
  Set ws = Nothing
  Set wb = Nothing
  End Sub



I'm having some issues."Curr" actually copies contents from other sheet(sheet1).After executing this macro if I delete sheet1 and import a new sheet1 to current workbook,the next execution of macro is returning to vbnullstring.I've to press the reset button in vba to get it sorted everytime.What could be causing this error?Sorry for my bad english.
 
Last edited by a moderator:

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top