|
Recorded macro
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 |
Recorded macro
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 |
Recorded macro
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 |
Recorded macro
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 |
| All times are GMT +1. The time now is 07:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com