使用宏将数据从一个工作表移至另一个工作表的问题求助

你的核心问题是录制的宏依赖Select、ActiveCell这类当前活动工作表的操作，所以代码会在你当前打开的工作表里运行，而非指定的COSHH List。下面是修改后的代码，直接解决这个问题，同时优化了冗余操作：

Sub Insert_Chemical()
    ' 定义工作表变量，明确指定操作对象
    Dim coshhSheet As Worksheet
    Dim newChemSheet As Worksheet
    Set coshhSheet = ThisWorkbook.Worksheets("COSHH List")
    Set newChemSheet = ThisWorkbook.Worksheets("New Chmical")
    
    ' 在COSHH List的第3行插入新行
    coshhSheet.Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    ' 直接赋值单元格，无需使用Select/ActiveCell
    coshhSheet.Range("A3").FormulaR1C1 = "='" & newChemSheet.Name & "'!RC[1]"
    coshhSheet.Range("B3").FormulaR1C1 = "='" & newChemSheet.Name & "'!RC[1]"
    coshhSheet.Range("C3").FormulaR1C1 = "='" & newChemSheet.Name & "'!RC[1]"
    coshhSheet.Range("C4").FormulaR1C1 = ""
    coshhSheet.Range("D3").FormulaR1C1 = "='" & newChemSheet.Name & "'!RC[1]"
    coshhSheet.Range("E3").FormulaR1C1 = "='" & newChemSheet.Name & "'!RC[2]"
    coshhSheet.Range("F3").FormulaR1C1 = "='" & newChemSheet.Name & "'!RC[2]"
    coshhSheet.Range("G3").FormulaR1C1 = "='" & newChemSheet.Name & "'!RC[2]"
    coshhSheet.Range("H3").FormulaR1C1 = "='" & newChemSheet.Name & "'!RC[2]"
    coshhSheet.Range("I3").FormulaR1C1 = "='" & newChemSheet.Name & "'!RC[2]"
    
    ' 修正排序范围：用整列排序，避免数据增多后漏排
    With coshhSheet.AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=coshhSheet.Range("A:A"), _
                         SortOn:=xlSortOnValues, _
                         Order:=xlAscending, _
                         DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

关键改进说明 #

• 明确绑定工作表：把两个工作表赋值给变量coshhSheet和newChemSheet，所有操作都基于这两个变量，彻底摆脱对当前活动表的依赖
• 移除冗余的选中操作：录制宏会自动生成Select、ActiveCell这类代码，直接对单元格赋值不仅更高效，还能避免切换工作表的错误
• 修正排序范围：原来的Range("A1:A4")是固定小范围，改成coshhSheet.Range("A:A")后，不管列表里有多少数据都能完整排序
• 清理重复操作：你原来重复设置了D3单元格的公式，这里保留了正确的那一行，去掉了冗余的重复代码

新手友好提示 #

1. 把按钮放在New Chmical工作表上，用户填写完表单直接点击按钮即可，无需切换到隐藏的COSHH List
2. 即使COSHH List处于隐藏状态，宏运行时会自动在后台操作，无需先取消隐藏
3. 以后写VBA尽量避免用Select、ActiveCell、ActiveSheet这类操作，明确指定操作对象是减少错误的核心技巧

内容的提问来源于stack exchange，提问作者James