Recorded macro

[Gaurav]

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

 

[Don Guillett]

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

 

[Roger Govier]

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

 

[Gaurav]

Thanks Roger. That worked.

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

 

[Gaurav]

Thanks Don.

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
(e-mail address removed)