excel VBA 的宏不会在同一模块中运行第二个子程序 [英] Macro for excel VBA will not run second sub in same module
问题描述
以下是我目前用于某些发票选项卡的宏.工作簿中的第一个工作表设置为使用工作表名称作为超链接进行索引.这个模块被设置为由 crtl+shift+n 激活.第二个子工作的前几次我尝试过,但现在它没有将数据从填充的前一行复制到填充的第一个空行.它似乎在第一个 sub 之后停止.有什么想法吗?
Below is a macro i am currently using for some invoice tabs. the first sheet in the workbook is set up for indexing using worksheet names for the hyperlinks. this module was set up to be activated by crtl+shift+n. the second sub worked the first couple of times i tried it but now it is not copying data from the previous row filled to the first empty row filled. it seems like it stops after the first sub. Any ideas?
Option Explicit
Sub NewRequistionRecord()
'
' NewRequistionRecord Macro
' Used for creaing a new requistion record to be auto updated in master
Requisitions worksheet
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.sheets("BlankReq")
Dim wbBefore As Workbook: Set wbBefore = Application.Workbooks("OPP Stores
Orders Macro.xlsm")
Dim wsBefore As Worksheet: Set wsBefore = wbBefore.sheets("BlankReq")
Dim answer
Dim NewName As String
Application.ScreenUpdating = False
ws.Select 'Goes to template worksheet
ws.Copy Before:=wsBefore ' Forces a copy to be made always before the template so it is always at the end
sheets("BlankReq (2)").Select
sheets("BlankReq (2)").Name = "Enter Req Number" ' changes the name to indicate a requisition number needs to be entered
Range("A1").Select ' hyperlink goes back Master Req index page
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Application.ScreenUpdating = True
sheets("Enter Req Number").Select
answer = MsgBox("Please rename the sheet with the Requisition number", vbOK)
If answer = vbOK Then
Another:
NewName = InputBox("Requisition number - ?")
ActiveSheet.Name = NewName
Range("D2").Select
NewName = InputBox("Requisition Description- NO SPACES!! USE UNDERSCORE ?")
Range("D2").Value = NewName
wsBefore.Select
Range("H2").Select
NewName = InputBox("Please enter Requested By ")
Range("H2").Value = NewName
wsBefore.Select
answer = MsgBox("Please select your data to copy and paste into this sheet. Line Cost must be selected seperately from the items.", vbOKOnly)
End If
End Sub
Sub Filldown()
Dim strFormulas(1 To 6) As Variant
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.ActiveSheet
Dim wbBefore As Workbook: Set wbBefore = Application.Workbooks("OPP StoresOrders Macro.xlsm")
Dim wsBefore As Worksheet: Set wsBefore = wbBefore.sheets("BlankReq")
Dim LRow As Long
Dim xRow As Variant
Application.ScreenUpdating = False
With ThisWorkbook.sheets("Requisitions")
Range("A1").Select
Columns("B:K").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End With
Application.ScreenUpdating = True
End Sub
推荐答案
快捷方式可能设置为 Sub,而不是 Module.因此,第二个 sub 可能默认没有运行.
The shortcut is probably set to a Sub, not to a Module. Thus, the second sub is probably not running by default.
要先运行第一个子程序,然后再运行第二个子程序,请尝试以下操作:
To run the first sub first and then the second sub, try the following:
Sub TestMe()
NewRequistionRecord
FillDown
End Sub
这篇关于excel VBA 的宏不会在同一模块中运行第二个子程序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!