Copying tabs in a workbook and buttons in new workbook still referencing the last workbook

**Dan_VBA** · 07-30-2021, 02:21 PM

Hi,

I am fairly new to VBA. I have created a worksheet that has a number of macros. I am trying to copy this worksheet in the same workbook. When i copy the tab all new buttons in my worksheet reference the old sheet. This means i have to go to each button and assign them to a new macro name. i.e. Sheet2Hide_series needs to be assigned to sheet3Hide_series.

Would anyone be able to help explain how i can copy worksheets and automatically update the links in my buttons rather than manually assigning all links?

thanks in advance

**Vraag en antwoord** · 07-30-2021, 02:30 PM

And how are we supposed to know how it all works?
See the yellow banner with instructions on how to place a sample file. (with your code)

**Dan_VBA** · 07-30-2021, 05:32 PM

I've attached an example of the issue. You will notice that when i copy and paste sheet 1 the buttons "add" and "delete" do not work.

I can assign the button to the new sheet but in sheets that have lots of buttons it takes a long time. Any idea?

**achammar** · 07-30-2021, 05:58 PM

It will work if you put the code in a separate module. Here is an example file.

**Vraag en antwoord** · 07-31-2021, 08:27 AM

Defined names are not necessary. Do not use select and selection. Make the layout so that it does not bother you when copying. To insert a row:

Sub VenA_add()
  x = Application.Match("Total", Columns(2), 0)
  Rows(x - 1).Copy
  Rows(x - 1).Insert
  Cells(x, 2).Resize(, 5) = Array("[Item]", "", 0, 0, "Update")
  Application.CutCopyMode = False
End Sub

To delete the active row:

Sub VenA_delete()
  x = Application.Match("Total", Columns(2), 0)
  y = ActiveCell.Row
  If y > 3 And x > 5 And y < x Then
    Rows(y).Delete
   Else
    MsgBox "Unable to delete the item."
  End If
End Sub

**Dan_VBA** · 08-02-2021, 06:14 AM

Thank you both!

Copying tabs in a workbook and buttons in new workbook still referencing the last workbook

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copying tabs in a workbook and buttons in new workbook still referencing the last workbook

Re: Copying tabs in a workbook and buttons in new workbook still referencing the last work

Re: Copying tabs in a workbook and buttons in new workbook still referencing the last work

Re: Copying tabs in a workbook and buttons in new workbook still referencing the last work

Re: Copying tabs in a workbook and buttons in new workbook still referencing the last work

Re: Copying tabs in a workbook and buttons in new workbook still referencing the last work

Thread Information

Users Browsing this Thread

Similar Threads

Referencing and copying from another open workbook

[SOLVED] Copying tabs (which mach the name of tabs in cuurent workbook) from another workbook

[SOLVED] Index Match Function Within One Workbook Referencing Cells in Another workbook error

[SOLVED] Trouble copying data from closed workbook into active workbook, referencing help

[SOLVED] Extract data from a workbook with multiple tabs, into a workbook with corresponding tabs

Excel 2007 : Cross referencing across multiple tabs within the same workbook.

loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions