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!
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!