Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Macro To Go To A Worksheet


So far I have been writing macro codes to open a workbook and used hyperlinks to go to a particular worksheet. Is there a way to use a command button to go from one worksheet in the workbook to another worksheet in the same workbook?!!


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Worksheet_change: Macro In One Worksheet To Run When Any Cell (in A Given Range)on A Different Worksheet (dataentry) Is Updated
I want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub

If Target(1, 1).Address = "dataentry!H5:IV72" Then
If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then

On Error Resume Next
Application.EnableEvents = False

View Replies!   View Related
Macro - Delete Row From Worksheet 1 And Add To Worksheet 2
I am maintaining an issues list in excel. File contains two worksheets/tabs: 1) Open Issues & 2)Closed Issues

One of the columns is Status. When an issues is in 'Complete' status, it is removed from Open Issues and added onto the 'Closed Issues' worksheet.

There are many issues closed daily and its quite a manual task to individually delete rows in one sheet and paste them at the end of the last row in the other sheet. Want to have a button/macro that would find any rows that have the cell for the status column w/ value 'Complete', delete it from 'Open Issues' sheet, and append it to the 'Closed Issues' shseet.

ive done basic c++ programing years ago, but know nothing about vba or excel macros.

View Replies!   View Related
Macro To Copy Six Specific Columns From One Worksheet To Another Worksheet
What I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)

The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.

In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas


View Replies!   View Related
MAcro To Copy Worksheet And Worksheet Events
I have a worksheet that utilizes a Worksheet Change Event. I created a macro to copy the sheet and that works fine except for the fact that it doesn't contain the Worksheet Change event.

View Replies!   View Related
Worksheet Change Macro Takes Too Much Time When Run With Update List Macro
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.

My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?

View Replies!   View Related
Macro SortWorksheets Stopped By Each Worksheet Until The Macro Is Ended
I have in my Workbook_open a macro,called Workbook_SheetActivate
with a line : Msgbox ActiveSheet.Name ....

In a sheet I have a macro SortWorksheets ....

My problem is , when I run the macro SortWorksheets it always stopped
by each worksheet until the macro is ended, this because the macro by opening workbook.

How can I make the macro SortWorksheet in one run?


View Replies!   View Related
Macro - Row Search Macro In Worksheet
to write a Macro which will be able to read a numeric value in a cell and find the equivalent of the row number in the sheet.

For instance, if I type "12" in cell A1, the marco will be able read cell A1, goto and highlight Row 12 of the same work sheet.

View Replies!   View Related
Worksheet Macro
I need it to be able to complete the following task in a workbook. I have a main sheet with data, and I need to be able to assign a word or numerical to say column "H", and it would take the information in that row and create another worksheet named the same as information in "H". And everything with the same "H" would combine to that particular worksheet.

I attached a file, excel.jpg as an example.

View Replies!   View Related
Macro: Go To A1 On Worksheet
I have a sheet with a button that, when clicked, opens a workbook and shows sheet1. The problem is that it shows the sheet in whatever state it was saved in last. I need to be able to write some code that sets the view to the top of the sheet and left of the sheet.

View Replies!   View Related
Macro To Copy A Worksheet New
*My original workbook consists of 3 worksheets lets call them (W1,W2,W3)
*I am copying W2 in a new book and pasting content of W1 via a macro.
*But if i am to run this macro more then once it doesn't work as the name of the new workbook is different.

see script below:

View Replies!   View Related
Macro: Copy From One Worksheet To Another
I have a workbook with two worksheets - Catalogue Request Summary (CRS) and Presentation Server Types (PST). The PST page is basically a form with validated entry fields, hence the information is updated constantly. The CRS is a summary tablised version of the information in the PST page.

I have setup a macro which copies across the information from the PST sheet and pastes into the CRS sheet. The problem is that when the macro is run, it over-writes the information instead of pasting the new data into the next row.

So basically I need the macro so that will copy any new information to a new row in the CRS page whenever new information has been added to the PST page the the button clicked.

I know it would be a lot easier if there was an attachment but there doesnt seem to be the option. Please pm your email address so I can send it through.

Edit - I have uploaded the file to an online storage site if anyone wants to download it. Link:http://www.4shared.com/file/91957046...ue_Test_a.html

View Replies!   View Related
Macro To Recalculate Worksheet
I have sheets 3, 4, 5 which I would like to only _manually_ recalculate (with a macro -- the sheets are locked at other times), and say sheets 1, 2, 6 which I would like to automatically calculate.

Is there a way to do this in excel? I can only see a way to manually/automatically calculate the entire Excel application, which is really not ideal.

View Replies!   View Related
Pass Value To Macro In Another Worksheet
I have one worksheet with a macro which calls a macro in another worksheet. I would like to pass a variable from the first worksheet to the second worksheet.

Worksheet1 macro includes the following line of code to call Macro3 in worksheet2:
Application.Run "'Worksheet2'!Macro3"

Worksheet2 has the following code in Macro3:
Do While response 1 And response 2
response = InputBox("Would you like to print your report on one page or two pages?", "Select number of pages", 1)

[rest of code]

I would like to be able to pass a value of '1' to Macro3 and bypass the message box when Macro3 is called from Worksheet1. However, if the user has Worksheet2 open, macro3 should display the message box.


View Replies!   View Related
Worksheet Combining Macro
I'm trying to write a macro that selects file A, copy some particular columns in file A, copy them into a new workbook, then repeat the process for file B, and copy and paste the columns into the same working workbook. Does anyone have any suggestions on using any one of the workbook, worksheet functions to do so?

View Replies!   View Related
Macro To Create New Worksheet?
I need to create new worksheet with all the rows which has qty (column A) value of 1 and above by clicking on a submit button....

View Replies!   View Related
Macro To Navigate Around A Worksheet
I needed to create even the simplest of VB Code and i'm struggling to get a macro to navigate around a worksheet.

Here's what I have so far...

Sub Macro2()
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.ClearContents
Selection.End(xlToRight).Select
Selection.Cut
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
(At this point, I want the cell 2 above the last selected to be highlighted)
ActiveSheet.Paste
End Sub

View Replies!   View Related
Possible To Have A Macro Rename A Worksheet?
Is it possible to have a macro re-name a worksheet. The context I want to use it would almost as a conditional formula.

Example: IF A1 = "time sheet" then sheet1 is then re-named to "time sheet"


View Replies!   View Related
Macro- Shared Worksheet
I have a shared worksheet my self and a few others key pricing into weekly, more or less a price check for stores we service. I have created another workbook that has the same basic template with links to the "shared" worksheet. In this workbook Im trying to run a macro to copy the pricing "template" into a new tab in the workbook. I keep getting this error as it pertains to "sheet". I would like the tab that is created to be named whatever the current day is if possible.

Sub StoreWeeklyData()
'
' StoreWeeklyData Macro
' Macro recorded 3/16/2009 by : Creates a tab for current week pricing data.
'
' Keyboard Shortcut: Ctrl+s
'
Sheets("Template-Link").Select
Range("A1:AM61").Select
Selection.Copy
Sheets("Template-Link").Select
Sheets.Add
Sheets("sheet").Select

View Replies!   View Related
Remove A Macro From A Worksheet
how can i remove a macro from a worksheet/book?

View Replies!   View Related
Macro-worksheet That Has Several Columns
I have a worksheet that has several columns.

Column C = Project numbers with a validation list to choose from ("PN1, PN2, PN3, PN4")
Column D = Expense Category with a validation list to choose from ("ODC, TVL, MAT")
Column E = The account number that is associated with each specific project number/expense.

Now for several projects, the ODC and MAT go on the same account number. But on another ODC and MAT have their own account numbers.

For Example,

If PN1 / ODC then E = 1.001
If PN1 / MAT then E = 1.001
If PN1 / TVL then E = 1.002
If PN2 / ODC then E = 2.001
If PN2 / MAT then E = 2.001
If PN2 / Mat then E = 2.002
If PN3 / ODC then E = 3.001
If PN3 / MAT then E = 3.002
If PN3 / TVL then E = 3.003

The range of the rows are 4 thru 500.

Is there a way to write a macro to automatically populate the account number in Column E based on the criteria selected in columns C and D?

View Replies!   View Related
Macro To Jump To A Worksheet
I am looking to create a macro, so from a drop down list the value chosen, the work book will open that sheet.

worksheets listed in the drop down list and then on the open sheet a back button back to the startpage.

View Replies!   View Related
Macro To Add Worksheet
I'm looking for a macro that will insert/add a new worksheet, and then move that newly created worksheet to the end of the workbook. Sounds simple but I can't figure out how to do it.

View Replies!   View Related
Protecting A Worksheet With A Macro
I am looking for code which will protect a worksheet based on the value of a particular cell.

i.e. If cell A1=1, then the worksheet will be protected (ideally with a pswd)

View Replies!   View Related
Macro - Next Unhidden Worksheet
Is it possible to have a macro which navigates to next unhidden worksheet? I am currently using the macro noted below, but they pick up hidden worksheets. Next sheet:

View Replies!   View Related
Macro For Button On Worksheet
I am trying to make a button that puts the formula back into the cell after a manual entry

View Replies!   View Related
Update Of Worksheet Using Macro
I need to be able to open a workbook named "Cost Price List" and press an update button which will open Another workbook called "Numerical Pricelist" that will then look up the part number of the item and change the corresponding description only, using the "Numerical Pricelist as the correct master document. There are approx 5000 part Numbers but i have cut a small sample section.

View Replies!   View Related
Using Macro On A Worksheet In A Different Workbook
This macro uses data validation and copies new entrys to my Company List sheet. In a different workbook (2.xls). Can this be changed so it copies the nesessery data to workbook 2.xls?

This works if i take the [2.xls] out but only on the same workbook. But i need 1 workbook with the list in and 1 workbook that grabs information from that list and adds to it.

View Replies!   View Related
How To Reference Another Worksheet In A Macro
I am writing a macro that will use information from the worksheet in use and the one prior to it. The problem is I do not know how to reference the worksheet that will always come before the worksheet in use. To fill you in, I have a monthly budget worksheet that tracks all my monthly financials. What I like to do is compare this months balances (cash, equity, bonds) and see the amount of loss/gain and percentage from the month before.

Here is what i currently have implemented.

This macro was written in the month of October. For this function the macro I wrote will take Octobers cash balance and subtract it from Septembers cash balance. This will give me the gain or loss. Now the problem is if I were to run this macro in November it will still reference September not October. I always want the macro to use the month before (the 1st tab to the left) not a specific tab. I wrote this macro using relative references so I do not understand why it will not use the tab before. Can someone please shed some light on this for me?

View Replies!   View Related
Macro To Select Worksheet
I have a workbook with around 350 worksheets. A column in the main sheet contains a list of part numbers which correspond to the names of the worksheet tabs. When a cell on the main sheet containing a part number is selected I would like a macro to find the corresponding worksheet, move that worksheet to the end of the row of tabs and open it.

I am using excel 2007

View Replies!   View Related
Delete A Worksheet Using A Macro
I have recorded a macro to copy a worksheet, perform some operations on the copy then delete the copied worksheet. My problem is that when running the macro I get a dialog box asking me if I am sure that I want to delete the sheet and I need to press OK. How can I get the macro to say it is OK to delete the sheet?

The last line of my macro is ActiveWindow.SelectedSheets.Delete. I have tried things like:- ActiveWindow.SelectedSheets.Delete = TRUE

View Replies!   View Related
Worksheet Merge Macro
Disclaimer: I know almost nothing about VBA or Macros. I started with them yesterday. I took the following code off Ron de Bruin's website. I am attaching his example spreadsheet. When you click on Test 2 it does everything I want BUT copy the header row over. The workbook I want to apply this too has 12 sheets++ which have the same exact columns and headers, but different row counts. Ron directed me back to his site and the following code:

View Replies!   View Related
Macro On Protected Worksheet
I have the following macro and when it "reprotects" my worksheet I need it to also turn on these Protect Sheet properties:

Format cells
Format columns
Format rows
Insert hyperlinks
and I need it to leave on the defaults of Select locked cells and select unlocked cells

Sub Spell_Check()
ActiveSheet.Unprotect Password:="pmo"
Cells.CheckSpelling "SRdictionary.dic", SpellLang:=1033
ActiveSheet.Protect Password:="pmo", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

View Replies!   View Related
Automatically Run Macro With Worksheet Changes
I want to run a macro I created whenever there is a change to any cell on my worksheet (A1:IV65536). So, for example, if cell A1 were to change from a 1 to a 2, I would like my macro to run.

View Replies!   View Related
Combining Worksheet Data (macro)
I have about 600 patients' charts, which I audit every 3-6 months. I have created a macro which imports each patient's workbook into a single workbook (one worksheet per patient). Now I'm trying to create a macro which will import each patient's most recent audit results onto one worksheet. Here's how the results are organized:

2/1/2010 Audit 3/1/2010 Audit 4/1/2010 Audit
Y N N/A Y N N/A Y N N/A
x x x
x x x

...etc., with each audit taking up 3 columns and 32 rows. My code, however, is not working,

View Replies!   View Related
Macro To Retrieve Data From Another Worksheet
I need a macro that will ask for a crew # and then select all the rows off another worksheet that contains that crew #. Then take the selected rows and copy them to the active worksheet for reporting.

Possible?

View Replies!   View Related
Macro To Copy Data From Worksheet To Another
I have one worksheet called WEEKS.xls which lists weeks 1 to 8, week1 being columns A,B,C, week2 being D,E,F, and so on up to week 8

I have another work sheet called supplier.xls which is a worksheet imported from a database. In this worksheet I have columns A to Q. I am only interested in the data in columns C, P & Q.

I have buttons along the top in the WEEKS.xls sheet, one for eack week. On week one when the button is clicked I would like a macro that would look in folder “Supplier Stock” for the worksheet suppliers.xls and then take the data from columns C,P,Q and paste them into the WEEKS.xls in columns A,B,C. On week 2 the data will be pasted into D,E,F and so on. On the supplier.xls there is a few gaps down the sheet , like row 15 & 16 and alo 19 & 20 if possible I don’t want the gaps in the WEEKS.xls. Also on column P it is a date format I would like that pasted into the WEEKS.XLS in a dd/mm/yy format

I have added both sheets to show you what I mean

View Replies!   View Related
Hide Worksheet If No Macro Is Executed
I got two worksheets visible for user to enter information with vba codes behind the sheets(i.e. Private Sub Worksheet_SelectionChange(ByVal Target As Range). Most cells of the worksheets are protected if the vba is executed. However, it was found that if user chooses not to run the macro when prompted, the worksheet can be edited whatever user want. So, how can the worksheet whole worksheet is protected if macro is not run?


View Replies!   View Related
Macro To Print A Worksheet With A Button
I am trying to create a simple macro to print a worksheet with a button. The worksheet will have additions/deletions on a daily basis, so it need to adjust accordingly. I cannot figure out how to do the loop macro.

View Replies!   View Related
Macro Stalls When Worksheet Shared
have had to try to automate a function which will transfer all rows of data that an operator "checks" in a check box, to a seperate sheet before printing. Written under pressure with boss at my elbow so I will tidy later.

My problem is that there is a command within the macro (after the filtered data is copied over to Priority sheet) which will not allow the macro to fully execute when the file is shared.

Copy file attached.

View Replies!   View Related
Macro Change The Name Of The Worksheet It Copies
i created a sheet and called it "Original" (which somehow is a template), in another sheet i created a big Button named "Create account" ,then i created a macro which copies that "Original" sheet and by default it names it "Original 2 " now that i need a new account each time i click that button to create a new sheet and rename it by the client name i need, in order to keep the "Original" intact as a template. It happens sometimes by mistake that i rename the "Original" one ( The template one) which therefore returns an error when i click the button!

Is there anyway to add to that macro a code which rename that "Original 2" something else lets say "Account" to differentiate it from the Template one?

Or to Protect only the "Original" sheet from being renamed out of the whole sheets i have , in order not to rename it accidentally?



View Replies!   View Related
Worksheet Lookup Combo Macro
I need a macro to search for certain info on different sheets
combine them and place it on a Report sheet.
This is extensive and complicated so it would be easier if I
emailed the .xls file

If you think you can help, let me email you the file.


View Replies!   View Related
How To Have Macro Allow Select Of Workbook And Worksheet
The last few days I have had great support in answering my questions. I would like to tweak the code one more step.. The RFQ LIST workbook grabs data from Inventory workbook after select the workbook.

This code in RFQ LIST allows to select the workbook, I would also like to select the workbook.sheet as found out they want to tab the data and keep long term history.

View Replies!   View Related
Browse For Worksheet And Running Macro
I am trying to create a program that will allow the user to browse for an excel worksheet and execute a macro that will format the worksheet with a click of a button.

Here is an image of what I want the template to look like:
Picture 2.jpg

Now I need to know what language I should use and the codes to make it run.

View Replies!   View Related
Macro To Automate Updating Of Worksheet
Here is my scenario:

4 step process or more?

1)I have a text file (with delimiters) that I want to be copied over to an existing workbook (because of the header)

2)So now I have a temporary workbook that includes the header and the data from the text file. I want this temporary data to be transferred or copied to a sheet in another workbook(s), wipe out previous data and store the new one. Save the workbook(s) and close.

3)Close temporary file. Don't save.

4)I want this process to be automated -- run it daily everyday at say, 6:30 in the morning.

View Replies!   View Related
Macro On Multiple Worksheet, Different Ranges
I am looking for a way to modify the following macro so that I can run the same code for multiple ranges that are on different worksheets.


Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim TimeStr As String

On Error Goto EndMacro
If Application.Intersect(Target, Range(" schedule!H4:H35,J4:J35,K4:K35,M4:M35,N4:N35,P4:P35,Q4:Q35,S4:S35,T4:T35,V4:V35,W4:W35,Y4:Y35,Z4:Z35,AB4:AB35")) Is Nothing Then
Exit Sub ..........................

View Replies!   View Related
Looping Through Each Worksheet And Calling A Macro
I had create a few macro with the macro name the same as the worksheet name.

How can i create another macro to loop through all the worksheet and if there is any data in Cell A1, it will call the corresponding macro. If there is no data, it will go onto another worksheet.

View Replies!   View Related
Macro Security Changing Worksheet
I have created a workbook. That has 35 worksheets within it. I have created a series of buttons and put in a macro code so that when the user clicks on the button it changes the active worksheet to what they clicked on.

So basically when they click button b it changes to worksheet b and if they click button c it changes to worksheet c, ETC.

The problem I have is that since it is a macro it works fine on my computer but when it is sent to other users, because of security settings on their pcs it will disable the macro [thus the buttons will not work or show up]. I have created a digital trust certificate for it. However it is alot of steps to install this for the basic user. Is there a way to create a button that will do what I want without using a macro code. In that it will be able to go to many different users and wont have to worry about the buttons not working because of security settings?

View Replies!   View Related
Macro To Paste Data To A Worksheet
I have a workbook that has a master data sheet which is used daily to import data. I also have worksheets named: 1, 2, 3, etc. based on the day of the month.

I have been manually pasted the master data for a particular day onto that corresponding numbered worksheet.

I was thinking of taking the date and and separating it using the deliminated feature then somehow looking at the day of the month and tell it to paste to that same worksheet number.

View Replies!   View Related
Renaming Worksheet Tabs Macro
I would like to create a macro that would bring up an input box or preferably a list box that will allow me to input information for a sheet/tab name where where "TBL NPL NGRPL" appears in the code at the end of this message. The macro needs to be available to any new file created

The only worksheet names needed are below.

TBL NPL NGRPL
TBL NPL NIAU7
TBL NPL NIAU8
TBL NPL NIA10
TBL NPL NNDU4

This is the extent of my ability:

Sub Macro1()
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "TBL NPL NGRPL"
Range("A1").Select
End Sub

I created six macros, but there must be an easier way.

View Replies!   View Related
Macro Open The Worksheet In Which It Was Written
I've written a macro and i've added a button to the toolbar so that it's easy for users of the spreadsheet to run the macro. the problem i'm running into is that the sheet that the macro's written in is a template. the macro formats some downloaded junk. soooo, every time someone downloads stuff, they copy-paste into the template, push the button, and it formats it.

my problem is that when they Save As, the button's assigned macro gets reset to the name of the file they saved as. therefore, when someone else comes along and press the button, it doesn't run the macro out of the original template sheet which they have open, instead it tries to find the last sheet that was saved and run the macro through it.


View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved