VBA will not yield result if assigned to button

[keithkemble]

Folks,

I have a macro that will run perfectly if I alt F11 and step through it, or if I run to cursor and then step through it, BUT it will not run correctly when I use a macro button and assign the VBA.

The project consists of 2 workbooks

no 1 is the destination and both wbs are identical
no 2 is the source and at anytime only 1 third is visible and it is these visible ws that is copied to wb1

wb 2 has 3 ws that are always visible, the remainder changes dependant on region selected.

When I step thru (using alt F11) the routine is perfect, selecting and copy/paste only the visible regions wsheets.

IT is only when I put the final touch (button on the menu of WB1) that when pressed copies the first hidden section of wb2 and copies /paste to wb1

For some reason it is not selecting the fourth visible sheet but the fourth physical sheet

Does anyone have any ideas?

I can post code if required

 

[NdNoviceHlp]

You will probably have to post code to find a solution to this problem. It seems that you haven't been specific enough in referencing workbbook/worksheets. Try compiling your code after adding the command button as this may also help. Good luck! Dave

 

[keithkemble]

The issue , as stated befoe, is simply that the sub does not guarantee to activate the 4th visible sheet in wb2, and then at the end of the loop move to the next visible sheet (still in wb2)

Any help would be appreciated


Sub copytouxbridge()
Dim KK As Worksheet
Dim i As Integer ' wb counter start
Dim c As Integer ' counter a
Dim s As Integer ' ws counter
Dim st As Integer ' ws counter start
Dim centre As String
Dim Check, counter
Check = True: counter = 0 ' Initialize wb variables.
i = 2 ' first wb to look in
Dim Checkb, counterb
Checkb = True: counterb = 0 ' Initialize sh variables.
st = 4 ' first worksheet to look in

Workbooks(1).Activate
Application.ScreenUpdating = False
For Each SH In ThisWorkbook.Sheets
SH.Unprotect "password"
Next

' start of count wb loop
c = Workbooks.Count
Do ' Outer loop.
Do While counter <= c ' Inner loop.
counter = counter + 1 ' Increment Counter.
If counter = c Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If

' select next wb then each visible sheet in turn
' and paste to wb 1 then loop

Workbooks(i).Activate

Sheets("menu").Activate
On Error GoTo errHandler
Application.Run ("visiblesheetcount") ' run function call to count visible ws less 3
s = visiblesheetcount
s = s - 3
Worksheets(st - 1).Activate ' selects 3rd ws ready to move to next ws in loop routine
Range("a1").Select
Do ' Outer loop.
Do While counterb <= s ' Inner loop.

Workbooks(i).Activate ' ensures call back to source wb
Dim y As Worksheet
Set y = ActiveSheet.Next
ActiveSheet.Next.Activate ' move to next visible sheet in loop

counterb = counterb + 1 ' Increment Counter.
If counterb = s + 1 Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If

Range("H1").Select
centre = ActiveCell.Value
Cells.Select
Selection.Copy
st = st + 1



' change back to wb 1 and paste
Workbooks(1).Activate
Sheets(centre).Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("H1").Select
' change back to source wb
Workbooks(i).Activate

Loop ' selects ws loop
Loop Until Check = False ' Exit ws outer loop immediately.

Loop ' exit wb inner loop
Loop Until Check = False ' Exit wb outer loop immediately.
For Each KK In ThisWorkbook.Sheets
KK.Protect "password"
Next
Workbooks(1).Activate
Sheets("menu").Select
Application.ScreenUpdating = True

errHandler:
For Each KK In ThisWorkbook.Sheets
KK.Protect "password"
Next
Application.ScreenUpdating = True
' protect model
Exit Sub
End Sub

 

[keithkemble]

I have managed to get the routine working by pointing at the physical sheet, not the visible sheets.

It is not the way I wished to go, but needs must.

Does anyone know a routine for selecting a visible sheet amongst hidden/visiblke sheets ?