Making a macro run from a button in different tab.

[fredaldous]

Hello,

So glad I found this site I am truly stuck. Hopefully someone here can help me...

I have an excel spreadsheet with two tabs.One called "menu" and one called "website"

I want to put a button in tab one (menu) and assign a macro. I need the macro to run/output in sheet 2 (website) not in sheet 1 where the button is placed.

No matter what I do I can't figure it out. The macro always runs in the same sheet as the button.

Can anyone help?

[Andy Pope]

I assume you code has no explicit sheet references so just runs on the active worksheet.

Quick fix, make the 2nd sheet active at the start of your code and the 1st at the end.

Proper fix, make your range references to the require worksheet.

[fredaldous]

Wow rapid response, thanks very much.

I am a complete novice though. I simply recorded a macro in sheet 2 and went to sheet 1. I made a button and assigned a macro to it.

here's the code....

Please Login or Register  to view this content.

Had to delete alot of the code as the post was too long.

Could you tell me in idiot speak what to do please?

Thanks again

[Andy Pope]

Start by removing all the irrelvant stuff the recorder produced.

Please Login or Register  to view this content.

[fredaldous]

Thanks so much for that amazingly quick and simple help you gave me.

I'll certainly be sticking around here.

Thanks again for your help

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[antnash81]

Hi All, im pretty new to all of this and hoping someone can help me...

I have a workbook with 7 sheets which i have renamed at the bottom of the workbook, each sheet will have a separate macro that will populate a single word.dox, on the last sheet i have created a button which i would like to run all macros to generate the single word.dox template

I currently have 3 issues:

Macro1: Works completely fine when i run the macro from the first tab: Code below

Sub ReplaceText()

Set ws = ThisWorkbook.Sheets("Pre Req")
Dim WordApp As Object
Dim wdoc As Object
Set wAPP = CreateObject("Word.Application")
wAPP.DisplayAlerts = False
wAPP.Visible = True

Set wdoc = wAPP.Documents.Add(Template:="C:\Users\ANash\Desktop\Site Survey VBA TEST 001.docx", NewTemplate:=False, DocumentType:=0)

With wdoc
.Application.Selection.Find.Text = "<<Customer>>"
.Application.Selection.Find.Execute
.Application.Selection = Range("C2")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "<<Customer Site>>"
.Application.Selection.Find.Execute
.Application.Selection = Range("C3")
.Application.Selection.EndOf

End With

End Sub

Macro 2 - I need this macro to run directly after the 1st macro and extract information from the second sheet into a section in the word document; problem i have is that when i run macro 2 nothing happens in the word document .
Macro 2:

Sub ReplaceText()


With wdoc
.Application.Selection.Find.Text = "<<RO12>>"
.Application.Selection.Find.Execute
.Application.Selection = Range("B12")
.Application.Selection.EndOf



End With

End Sub

Third issue i have is when i use the button to run the macros the word template opens.. but absolutely no information is extracted from Excel into the word.dox template ( but when i run the 1st macro in its sheet it will open the word.dox and pull the required information into the document.

Macro 3:

Sub Button1_Click()

Call Module1.ReplaceText 'Macro1
Call Module2.ReplaceText 'Macro2

End Sub


Any help or guidance will be greatly appreciated

[alansidman]

antnash81

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Additionally, when posting VBA code, please use code tags which are specified in the rules you agreed to when you joined.

I am closing this thread.