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: