Hi All,

I have recorded the following macro.

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 8/22/2008 by gaurav.sharma
'

'
ActiveSheet.Unprotect Password:="justme"
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ScrollRow = 9963
ActiveWindow.ScrollRow = 9687
ActiveWindow.ScrollRow = 9225
ActiveWindow.ScrollRow = 8395
ActiveWindow.ScrollRow = 7288
ActiveWindow.ScrollRow = 6181
ActiveWindow.ScrollRow = 5074
ActiveWindow.ScrollRow = 3967
ActiveWindow.ScrollRow = 3045
ActiveWindow.ScrollRow = 2215
ActiveWindow.ScrollRow = 1477
ActiveWindow.ScrollRow = 739
ActiveWindow.ScrollRow = 1
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B2").Select
ActiveSheet.Protect Password:="justme"
ActiveWorkbook.Save
End Sub


I want to add to it that if cell B2 is blank...it gives an error saying
"Invalid Data"

Help is appreciated.
Thanks

Sub copyh2nextrow()
Range("j2").Value = Range("h2").End(xlDown)
If Range("b2") = "" Then 'what do you want to do
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gaurav" wrote in message
...
Hi All,

I have recorded the following macro.

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 8/22/2008 by gaurav.sharma
'

'
ActiveSheet.Unprotect Password:="justme"
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ScrollRow = 9963
ActiveWindow.ScrollRow = 9687
ActiveWindow.ScrollRow = 9225
ActiveWindow.ScrollRow = 8395
ActiveWindow.ScrollRow = 7288
ActiveWindow.ScrollRow = 6181
ActiveWindow.ScrollRow = 5074
ActiveWindow.ScrollRow = 3967
ActiveWindow.ScrollRow = 3045
ActiveWindow.ScrollRow = 2215
ActiveWindow.ScrollRow = 1477
ActiveWindow.ScrollRow = 739
ActiveWindow.ScrollRow = 1
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B2").Select
ActiveSheet.Protect Password:="justme"
ActiveWorkbook.Save
End Sub


I want to add to it that if cell B2 is blank...it gives an error saying
"Invalid Data"

Help is appreciated.
Thanks

Hi

The macro recorder includes a lot of extra stuff that is not required.
I have trimmed this out and included your If test at the end.

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 8/22/2008 by gaurav.sharma

ActiveSheet.Unprotect Password:="justme"
Range("H2").Activate
Range("H2", Selection.End(xlDown)).Copy
Range("E2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("B2").Activate
If Range("B2") = "" Then
MsgBox "Invalid Data"
End If
ActiveSheet.Protect Password:="justme"
ActiveWorkbook.Save
End Sub



--
Regards
Roger Govier

"Gaurav" wrote in message
...
Hi All,

I have recorded the following macro.

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 8/22/2008 by gaurav.sharma
'

'
ActiveSheet.Unprotect Password:="justme"
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ScrollRow = 9963
ActiveWindow.ScrollRow = 9687
ActiveWindow.ScrollRow = 9225
ActiveWindow.ScrollRow = 8395
ActiveWindow.ScrollRow = 7288
ActiveWindow.ScrollRow = 6181
ActiveWindow.ScrollRow = 5074
ActiveWindow.ScrollRow = 3967
ActiveWindow.ScrollRow = 3045
ActiveWindow.ScrollRow = 2215
ActiveWindow.ScrollRow = 1477
ActiveWindow.ScrollRow = 739
ActiveWindow.ScrollRow = 1
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B2").Select
ActiveSheet.Protect Password:="justme"
ActiveWorkbook.Save
End Sub


I want to add to it that if cell B2 is blank...it gives an error saying
"Invalid Data"

Help is appreciated.
Thanks

Thanks Roger. That worked.


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

The macro recorder includes a lot of extra stuff that is not required.
I have trimmed this out and included your If test at the end.

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 8/22/2008 by gaurav.sharma

ActiveSheet.Unprotect Password:="justme"
Range("H2").Activate
Range("H2", Selection.End(xlDown)).Copy
Range("E2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("B2").Activate
If Range("B2") = "" Then
MsgBox "Invalid Data"
End If
ActiveSheet.Protect Password:="justme"
ActiveWorkbook.Save
End Sub



--
Regards
Roger Govier

"Gaurav" wrote in message
...
Hi All,

I have recorded the following macro.

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 8/22/2008 by gaurav.sharma
'

'
ActiveSheet.Unprotect Password:="justme"
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ScrollRow = 9963
ActiveWindow.ScrollRow = 9687
ActiveWindow.ScrollRow = 9225
ActiveWindow.ScrollRow = 8395
ActiveWindow.ScrollRow = 7288
ActiveWindow.ScrollRow = 6181
ActiveWindow.ScrollRow = 5074
ActiveWindow.ScrollRow = 3967
ActiveWindow.ScrollRow = 3045
ActiveWindow.ScrollRow = 2215
ActiveWindow.ScrollRow = 1477
ActiveWindow.ScrollRow = 739
ActiveWindow.ScrollRow = 1
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B2").Select
ActiveSheet.Protect Password:="justme"
ActiveWorkbook.Save
End Sub


I want to add to it that if cell B2 is blank...it gives an error saying
"Invalid Data"

Help is appreciated.
Thanks

Thanks Don.


"Don Guillett" wrote in message
...
Sub copyh2nextrow()
Range("j2").Value = Range("h2").End(xlDown)
If Range("b2") = "" Then 'what do you want to do
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gaurav" wrote in message
...
Hi All,

I have recorded the following macro.

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 8/22/2008 by gaurav.sharma
'

'
ActiveSheet.Unprotect Password:="justme"
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ScrollRow = 9963
ActiveWindow.ScrollRow = 9687
ActiveWindow.ScrollRow = 9225
ActiveWindow.ScrollRow = 8395
ActiveWindow.ScrollRow = 7288
ActiveWindow.ScrollRow = 6181
ActiveWindow.ScrollRow = 5074
ActiveWindow.ScrollRow = 3967
ActiveWindow.ScrollRow = 3045
ActiveWindow.ScrollRow = 2215
ActiveWindow.ScrollRow = 1477
ActiveWindow.ScrollRow = 739
ActiveWindow.ScrollRow = 1
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B2").Select
ActiveSheet.Protect Password:="justme"
ActiveWorkbook.Save
End Sub


I want to add to it that if cell B2 is blank...it gives an error saying
"Invalid Data"

Help is appreciated.
Thanks