VBA will not yield result if assigned to button

keithkemble

Board Regular
Joined
Feb 15, 2002
Messages
160
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
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

Board Regular
Joined
Feb 15, 2002
Messages
160
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

Board Regular
Joined
Feb 15, 2002
Messages
160
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,087
Members
414,501
Latest member
mdhaumyu

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
Top