Macro trigger problem

ebacskai

New Member
Joined
Jan 17, 2008
Messages
2
Hi All,

I'm hoping you can help - I have a macro which performs perfectly when I step through it in VB, but when I click the button which the macro is attached too, it doesn't work properly (it won't copy the first two rows from the first sheet and paste them onto the second sheet being added).

Here is the code:

Sub InsertNewSheet()

Application.ScreenUpdating = False

Call RenameSheets **I've included the code for this below**

Sheets("Blank").Visible = True
Sheets("Blank").Select
Sheets("Blank").Copy After:=Sheets(3)
Sheets("Blank (2)").Select
Sheets("Blank (2)").Name = "Sheet1"
Worksheets(1).Activate
Rows("1:2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Rows("1:2").Select
ActiveSheet.Paste
Sheets("Sheet1").Activate
Range("B3").Select
Sheets("Blank").Visible = False
Application.ScreenUpdating = True

End Sub

Sub RenameSheets()

Dim WS_Count As Integer
Dim I As Integer
Dim currencycode As String
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count
If Sheets(I).Visible = True Then
Sheets(I).Activate
currencycode = Range("B3").Value
ActiveSheet.Select
ActiveSheet.Name = currencycode
End If

Next I

End Sub

The only other thing I have happening is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B3")) Is Nothing Then
Exit Sub
Else
Application.ScreenUpdating = False
Call RenameSheets
End If
Application.ScreenUpdating = True
End Sub

Essentially what I am trying to do is create a macro that will copy a hidden sheet and insert it, and then copy the information that the user has entered from Row 1 & 2 of the first sheet into the copied sheet. This works fine when I step through the macro in VB - but when I press the button that it is attached to, it won't copy Row 1 & 2.

I am tearing my hair out working this one through - any help is appreciated!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
At a glance, I did not see any problem with your code. However, I simplified it a bit. Try this version and if it still does not work, we'll go from there...

Code:
Sub InsertNewSheet()

    Application.ScreenUpdating = False
    Call RenameSheets

    Sheets("Blank").Visible = xlSheetVisible
    Sheets("Blank").Copy After:=Sheets(3)

    Sheets("Blank (2)").Name = "Sheet1"
    
    Worksheets(1).Rows("1:2").EntireRow.Copy Sheets("Sheet1").Range("A1")
    Application.Goto Sheets("Sheet1").Range("B3")
    Sheets("Blank").Visible = xlSheetHidden
End Sub

Sub RenameSheets()
    Dim I As Integer
    
    ' Begin the loop.
    For I = 1 To ActiveWorkbook.Worksheets.Count
        If Sheets(I).Visible = xlSheetVisible Then
            Sheets(I).Name = Sheets(I).Range("B3").Value
        End If
    Next I
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("B3")) Is Nothing Then
        Exit Sub
    Else
        Application.ScreenUpdating = False
        Call RenameSheets
    End If
End Sub
 
Upvote 0
ebacskai

First of all, Welcome to the Board!

Right_Click's code looks like it should work fine for you.

Just a couple of pointers to help in the future. Using ActiveCell or ActiveSheet in VBA can give some unpredictable results. I once had a very simple macro to copy a sheet from one workbook to another, but it had to remove the protection first. It was using ActiveSheet. Never could get it to run from the Command button. It ran great using the editor. Just a quirk with VBA I guess. I had to change the logic of the code for that one.

Also, right_Click's code should run quit a bit faster than yours. The trick with VBA is that you very rarely need to select a cell to work with it. Look at Right_Click's code carefully. You'll see he copies and pastes in the same line of code.

If you keep this in mind, your VBA will run much faster in the future as your VBA skills improve.

Best regards,
 
Upvote 0
Thank you very much to both of you.

This has fixed some of the problem - the main problem now is that the data that the user entered on the first sheet (by selecting an item from a validation list) does not copy across.

Each sheet must contain the staff members name and the details of their trip - and rather than get them to re-enter it on every sheet, when they created a new sheet I wanted the information to copy across from the first worksheet.

The formatting etc now copies across, but it won't copy their name or the country that they visited etc.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top