Excel宏代码帮助 [英] Excel Macro Code Help

问题描述

我需要帮助解决这个看似不可能完成的任务。

我使用下面的代码将2张excel表连在一起，以便能够互相更新我在哪个页面，它工作得很好。 但是，我收到一个错误（对象'_worksheet'的方法'范围'失败）或当我
尝试添加其他工作表以互相交互时Excel崩溃。我的目标是连接5张或更多张，以便从我所在的任何页面更新其他页面。

有人可以帮助吗？

这个是一个代码示例，我使用了第一个  2，它完全符合我的需要：

  Private Sub Worksheet_Change（ ByVal目标作为范围）
 Dim r1作为范围，r2作为范围
设置r1 =范围（"a2：z100"）
设置r2 =表格（"Sheet2"）。范围（ "a2：z100"）
如果Intersect（Target，r1）为Nothing则退出Sub 
 Application.EnableEvents = False 
 r2.Value = r1.Value 
 Application.EnableEvents =真
结束子 

解决方案

原因是你的床单可能有不同的布局，合并的单元格等。

由于您的代码很慢，为什么不将这些工作表分组？之后，您在一张纸上键入的内容也会写在其他工作表中。

禁用您的代码并将下面的代码复制到代码模块"ThisWorkbook"中。

Andreas。

 Option Explicit 
 
 Private Sub Workbook_SheetSelectionChange（ByVal Sh As Object，ByVal Target as Range ）
 Select Case Sh.Name 
 Case" Sheet1"，" Sheet2" 
如果不相交（Target，Sh.Range（" A2：Z100"））是Nothing然后
'将工作表分组
'注意：这可以更改活动工作表，... 
 Sheets（Array（" Sheet1"，" Sheet2"））。。选择
'...因此：激活我们的工作表
 Sh.Activate 
 Else 
 'Ungroup 
 Sh.Select 
 End if 
 End Select 
 End Sub 
 

I am in need of help on this seemingly impossible task.

I used the below code to link 2 excel sheets together to be able to update each other no matter what page I was on and it has worked nicely.  However, I am getting an error (method 'range' of object '_worksheet' failed) or Excel crashes when I try to add additional sheets to interact with each other. My goal is to have 5 or more sheets connected to update the others from any page I am on.

Can anyone out there assist?

This is an example of the code that I used with the first 2 that worked exactly for what I needed it to:

Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim r1 As Range, r2 As Range
    Set r1 = Range("a2:z100")
    Set r2 = Sheets("Sheet2").Range("a2:z100")
    If Intersect(Target, r1) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        r2.Value = r1.Value
    Application.EnableEvents = True
End Sub

解决方案

The reason is that your sheets may have different layouts, merged cells, etc.

And as your code is slow, why not just group the sheets? After that, what you type in one sheet is written also in the other sheets.

Disable your code and copy the code below into the code module "ThisWorkbook".

Andreas.

Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  Select Case Sh.Name
    Case "Sheet1", "Sheet2"
      If Not Intersect(Target, Sh.Range("A2:Z100")) Is Nothing Then
        'Group the sheets
        'Note: This can change the active sheet, ...
        Sheets(Array("Sheet1", "Sheet2")).Select
        '... therefore: Activate our sheet
        Sh.Activate
      Else
        'Ungroup
        Sh.Select
      End If
  End Select
End Sub

这篇关于Excel宏代码帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！