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


Advertisements:










Macro To Work Between My TEST.xls File And Another Worksheet


Below is the macro to work between my TEST.xls file and another worksheet which is opened within the same workbook with the TEST file. If there are more than 2 files, the code ActiveWindow.ActivateNext won't work.

The number of files received from my suppliers varies depending on how busy the volume is. It can be as many as 20 files a day.


ActiveWindow.ActivateNext
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[5]


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Macro In .xlt File Won't Work In .xls File
Using Office 2000, here is the code I am using which works on my laptop with Office 2007. This file is initially saved as a template (.xlt) file. This code works if I right-click and open the template and enter data, but when I just double click and open, causing it to open as an .xls file, it doesn't work. (The textboxes are inserted textboxes not from a userform).

VBA:
Option Explicit

Sub Text_Copy()

With Worksheets("Description")

Worksheets("Summary").Shapes("TextBox3").TextFrame.Characters.Text = .Shapes("TextBox1").TextFrame.Characters.Text & " " & .Shapes("TextBox2").TextFrame.Characters.Text

End With
End Sub

View Replies!   View Related
Macro Doesn't Work When Workbook/File Is Shared
I am encountering a specific scenerio where In I am creating a New file by copying one of the sheets And renaming that With todays date. here starts me problem when I try To share the sheet To work around I am seeing that th macro Is Not copying the sheet properly even though i have given PasteSpecial. The code goes here

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing Is Not transferred when copy/pasting, but Is If the keyword uses "quotes".
Sub Newsheet()
sheetname = Format(Now, "dd-mmm-yyyy")
MsgBox sheetname
sheet_count = Worksheets.Count
'Checking for Replication................

View Replies!   View Related
Test If File Is Open
How can I open Book2.xls (in the same folder) in 'read only' mode, using a macro from Book1.xls? Book2 might already be open or it may not. I then need to return control to Book1.

View Replies!   View Related
Test Whether Or Not An External File Exists?
Is there a way to test (from Excel 2002) whether a specified file (not necessarily an Excel file) exists on my computer? I could specify the complete path.

View Replies!   View Related
Test For Grouping On Worksheet
I don't want a macro to run if grouping is already present on a particular worksheet.

View Replies!   View Related
Test/Check If Shape Exists On Worksheet
I'm creating a macro to select, modify dimensions and place shapes on a excel sheet. (I'm talking about pictures insered and stocked in a specific sheet of my workbook)
When the users insert a new picture he has to set a name for each of them.
A combobox contain the choices, when an item is selected, the macro identify, size and place the corresponding picture.

But I have a bug if a shape doesn't exist when I try to select it :

Sheets("fiche de controle").Select
'select the sheet with the pictures
ActiveSheet.Shapes(Item_old).Select
'select the shapes "Item_Old"
Item_Old is a variable corresponding to the picture name.

If the user made a typo, I have a bug.
Someone knows how to test if the shapes exist to display a meesage if not ?
Or somethig to avoid this kind of bug ?

View Replies!   View Related
Save One Worksheet As A .CSV File With Macro
So I am trying to design a workbook that has two worksheets... one with instructions and a button for users to click to "Save as .CSV File", another for the data that will go into that CSV file.

Here are the Macro requirements:
1. The user will be prompted for the File & Location to save the .CSV file
2. If they click 'Cancel', no changes will be made (and unlike my current code, it won't ask them to debug).
3. Confirmation of the filename is not necessary even though it's currently included in my Macro
4. The file will automatically "reopen" so that they only see the new .CSV file without the original Instruction tab.

View Replies!   View Related
How To Get A Macro To Work With Any Work Book Name
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook

the current name is

AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls

i have attached the code in notepad ...

View Replies!   View Related
Count IF Look At A Column C In A Worksheet A And Return How Many Time The Word 'on Test' Occurs
I am using a count if to look at a column C in a worksheet a and return how many time the word 'on test' occurs. This is then returned to a table in another worksheet. I have 5 worksheets in total with the same columns, how can I do multiple countif - so that it will count all the 'on test' in column C for all 5 sheets.

View Replies!   View Related
Macro To Save Worksheet As Text File Without Blanks
find attached an example of the spreadsheet I am working with. Please bear in mind that this is a much simplified version of the version I am currently working on (which needs to have 1000 lines). What I am trying to achieve is allow my team to enter rows of data into the spreadsheet in a format that they will be familiar with - then hit the button on the sheet which will then take a copy of the second sheet (which looks up against the first) and spit it out in a .txt file ready to be uploaded into our computer system.

The main priority that I need to fix is that when the .txt file is opened in notepad it contains a huge amount of blank data rows at the bottom - I assume that it is taking accross all 65536 lines into the .txt where I only want the rows that have data in them in the .txt. At present our computer system will not accept the .txt due to all the blank rows (its limit is 1000 lines).

View Replies!   View Related
Activate Worksheet: The Retro Is Also Suppose To Test Cell H12 To See If It Is Blank Before Running The Msgbox
I do not know if I have this written correctly, I would like to have the sub - Retro run whenever some one opens this worksheet - "FORM". The retro is also suppose to test cell H12 to see if it is blank before running the msgbox.

View Replies!   View Related
Test Speed Of Macro On Different Systems
ran the following macro on the new computer and on two other computers...

View Replies!   View Related
Copy Data: Always Work With File A (active)
I open 2 files : A & B. Once I finish copying data from B into A. I close B and open C and so on. That means I always work with file A (active)

File B, C or D and so on has 3 worksheets with identical names. The filename of B, C and so on also has some common word

I'd like to copy data into sheet1 of file A, always same range : B2:B100 , afterwards I would copy-paste each result from sheet1 to another sheet of file A and delete the data in sheet1. So sheet1 of file A is always active and show the result each time the macro was run as follows.


Cell b2 of file A = cell c1 of sheet 1 of file B
Cell b3 of file A = cell d1 sheet 1 of file B + cell e1 of sheet 2 of file B


View Replies!   View Related
Copy Between Worksheets Doesn't Work (look For A Certain Value In Worksheet A And Copy That Row Of Data To Worksheet B)
look for a certain value in worksheet A and copy that row of data to Worksheet B.

However, it seems to be only copying the row in worksheet A and pasting it. Is there something that a noob VBA scripter has missed out?

PHP PrivateSubGetInfo_Click()
DimrAsLong,LastRowAsLong,StatusAsInteger
DimMessageAsString,TitleAsString,DefaultAsString,MyValueAsString
Application.ScreenUpdating=False

MyValue=Range("A4").Value
Workbooks("invoice.xls").Worksheets("A").Activate
LastRow=Range("C65536").End(xlUp).Row
Forr=LastRowTo1Step-1
IfCells(r,1).Value=MyValueThen
Rows(r).EntireRow.Copy
Workbooks("invoice.xls").Worksheets("B").Activate
Rows("8").Select
Selection.PasteSpecialPaste:=xlPasteValues,Operation:=xlNone,SkipBlanks_
:=False,Transpose:=False
Status=1
Workbooks("invoice.xls").Worksheets("A").Activate
Rows(r).EntireRow.Delete

ExitFor
EndIf
Nextr
Application.ScreenUpdating=True

View Replies!   View Related
Test/Compare Pivot Table Fields Macro Code
I wrote a macro for a pivot table.

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Export!R1C1:R3000C53").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Reference"), "Count of Reference", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields("ACN received date")
.Orientation = xlRowField
.Position = 1
End With

What i want to know is if there is a code line to test if "Non Processed" value exists in column "Status" and then hide the value.

Now, if the macro does not find one value just gives me error.

View Replies!   View Related
Sum Function Wont Work In My Worksheet
The sum function won't work in my worksheet. See the green shaded area in the attached spreadsheet. Does anybody know what the problem is?

View Replies!   View Related
EMailing Work Sheet Trying To Attach File Listed In Cell
I am using a macro to e-mail any work sheet with an address in A1. I would also like to attach a file that will be listed in cell E1 of that worksheet. The file is different for each work sheet that is being sent but will be listed in the same cell of each work sheet.

This is the code I am using (i got it from ron de bruin) when I use the .Attachments.Add (C:est1.txt) it works but i can not seem to figure out how to get it to read the file address in the E1 cell. The code i am trying to use is .Attachments.Add = ws.Range("E1").value.

View Replies!   View Related
Toolbar When Work Papers Are Opened From Within The Engagement File Room
I am an IT Consultant and my client is doing a workstation refresh. They are a CPA firm. They use software called ProSystem Fx Engagement as a filing closet sort of thing. This has a toolbar Excel when work papers are opened from within the Engagement file room. In the Engagement toolbar there is an icon which allows the user to check all boxes. I have contacted the vendor and they said they know nothing about this and it is unsupported. I am trying to migrate to the user's new machine (helps save alot of time) but kinda stuck in a rut. I am assuming this maybe is a custom macro? I am not an Excel guy, I do server stuff etc... but trying to please my client, I included a screen shot of the toolbar and highlighted the button he would click to make this function work. This is Excel 2003 w/ SP4, and Windows XP Pro w/ SP3

View Replies!   View Related
Macros Wont Work When I Protect My Worksheet
I have the follwing macro that is used to hide certain rows: ......

View Replies!   View Related
Doesn't Work If The Worksheet Hasn't Been Saved Yet
I'm trying to simplify a formula that I'm using in a spreadsheet that's being used mostly in Excel 2000.

2 reasons I'm doing this:

1) It doesn't work if the worksheet hasn't been saved yet.
2) It's ugly

=DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2)))+1,0)

The formula returns the last day of the month of the name of the sheet. For example, if the sheet is names "Mar 07", it returns 3/31/07. (yeah, when the 2100 issue becomes a big deal, I'm not going to be ready but I have 92 years to think about it)

Even if I can't make it prettier, does anyone have a way to get the sheet name into a formula without VBA Code in a file that hasn't been saved.

View Replies!   View Related
Range To Work On The Worksheet Before Double Click
I am encountering is that with my knowledge I can only get one range to work on the worksheet_before doubleclick function and thats the sort range.

I need several different ranges with each range giving a target value uinique to that range.

View Replies!   View Related
Macro Doesn't Work On Button, But Fine From Macro Menu!
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.

When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.

The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.

Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!

View Replies!   View Related
UDF Working When Called From Sub, Doesn't Work As Worksheet Function
I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within.

For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found.

My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.

View Replies!   View Related
Copy The Same Cells On Different Worksheet On Different File On A Master File
I have certain excel file that i want to be able to write a macro in order for it to extract certain information from certain cells on different sheet. In other words, i want cell A2 from each of the three different worksheet i have under different files. And have those data automatically update a master excel or access file whichever might be easier.

View Replies!   View Related
Macro Not Work On Another Tab
To run a macro from the first tab of the workbook but have it do it work on another tab.

In the workbook in question I have a tab called "input" were certain information in updated and is linked or used in formulas on other tabs. Some of the tabs have macros that do various functions. I would like to set up one macro button on the "input" tab that would run the macros on the other tabs instead of having to run each one individually from its own tab.

Below is on of the macros that I would like to run from the "input" tab and have it update the "AP Accrual" tab

Sub CopyPriorMonthAccruals()
ActiveCell.FormulaR1C1 = "=EOMONTH(Input!R[5]C,-1)"
Columns("L:L").Select
Selection.Copy
Columns("Y:Y").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B3").Select
ActiveCell.FormulaR1C1 = "=EOMONTH(Input!R[5]C,0)"................................

View Replies!   View Related
Macro To Work Across Many Workbooks?
I need to remove hyperlinks across probably 30-40 workbooks, is there a way of doing creating a macro that would do this across many instead of 1 at a time?

View Replies!   View Related
Macro To Work When Cell Changes Value
Everytime a cell (say B18) changes value I want the following to happen:

If it changes to "Annual" I want row 20 to ungroup

If it changes to "Quarterly" I want rows 22-25 to ungroup

If it changes to "Monthly" I want rows 27-38 to ungroup

From what I can see on other discussion groups I need to enter a code on the worksheet from view code... but I cant work out the code that I would need to enter.

View Replies!   View Related
Macro Does Not Work Anymore
I've been using a Macro that was written in an Excel spreadsheet and it's been working fine. For some reason, the Macro does not work anymore.

The Macro is written in Visual Basic and it is launched with button.

What is the first step I should take to debug this?
I think it's something obvious I'm missing.


View Replies!   View Related
Transpose Won't Work, Perhaps A Macro Will
I am sent an email that contains multiple blocks of information.

They're broken down like this:

Title
Link
ID
Department
Date
Status
Value
News

There are about 100 of these blocks of text in the email, and some of them are duplicates. What I'd like to do is copy them into Excel, and run a macro which separates them into the appropriate column and removes the duplicates. The problem that I'm running into is some of the blocks have a value (highlighted in red above) and others don't have anything at all. I'm looking for a solution that will be able to evaluate the text and if the cell after Status isn't value, I'd like it to insert a blank cell, a cell that says ignore, or something that will keep the format correct.

View Replies!   View Related
Macro: How Work With Weeks
I have a file with date from the whole year.
Now i want that he splits for me in the file the weeks. So that i have all the info for each week. (seperating by 2 or 3 blank rows)

A second thought was to copy paste them to a new sheet for each seperated week.

and if i just splits them by inserting blanks rows between the weeks. I was wondering if it is possible to make a pivot for each week

i thought maybe working with boxname?



View Replies!   View Related
Do While Macro Seems To Work In Stages
I have a macro which loops through data, deleting those rows that are marked with a "Y". However - it appears to always want to do this in three stages. The first time I run the macro, it delete's a large chunk of those marked rows - but it nearly always leaves two rows. The second time I run it, it deletes one of those two remaining rows. And then I run it again and it deletes the final outstanding row. I'm sure i've done something stupid here - the code is below

View Replies!   View Related
Vlookup Does Not Work In A Macro
why the macro "try()" in the attached workbook does not work?

I get the error message: "Unable to get the Vlookup property of the WorksheetFunction class"

Sub try()
Dim i%
For i = 1 To 35
Sheets("Sheet2").Select
myValue = Cells(i, 1).Value
Sheets("Sheet1").Select
n = WorksheetFunction.VLookup(myValue, "A1:A11", 1, True)
Sheets("Sheet2").Select
Cells(i, 2).Value = n
Next i
End Sub

View Replies!   View Related
Work Accurately With Times To Calculate The Work Progress Of The People In The Workshop
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....

I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.

From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.

View Replies!   View Related
Work Out A Formula For My Spreadsheet Which I Use To Work Out Cutting Lists For Timber Frames
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.

View Replies!   View Related
Macro To Work In A Differnet Workbook
I have the below macro that works well

Sub Button68_Click() 'Creates delivery note

Dim stIDSelect As String
Dim stC As String
Dim C As Range

stIDSelect = InputBox("Enter RMA number you want to create a delivery note for")
If stIDSelect = vbNullString Then Exit Sub

Dim idRow As Range
Set idRow = Intersect(Range("A:A"), ActiveSheet.UsedRange).Find(stIDSelect)
If Not idRow Is Nothing Then
Dim i As Integer................

View Replies!   View Related
Macro Combining Work Sheets Into One
I have the below macro in a workbook that I need to combine the two sheets into one, (sheet1 and sheet2). Both sheets in use column "A" for id, but for some reason it is only copying over sheet one? One sheet has 12 columns and the other has 13, not sure if that is making the difference?


View Replies!   View Related
Apply Macro To All Work Sheets
How do I apply 1 Macro to all the sheets in a Work book. That is one Macro should execute the function in all the worksheets of the Workbook.

View Replies!   View Related
Progress Bar To Work While Running The Macro
I was looking at a previous post to get a progress bar to work while running
a lengthy macro. Below is the post on how to incorporate the progress bar
into your code.

That works fine expect for one thing. There is a cancel button on the progress bar, and when it is pushed the rest of my code continues running. How do I end my code if I press cancel on the progress bar?

Sub Main
Dim PB as clsProgBar
Set PB = new clsProgBar

'if your subs are being called from a userform, hide the form first using
Me.Hide
With PB

.title = "some title"
.caption1 = "Executing, this may take a while"
.caption2 = "Doing task 1"
.show
doevents...................................................

View Replies!   View Related
Trying To Get A Workbook.Open Macro To Work
I'm trying to get a Workbook.Open macro to work. I"m using the exact name for the location. For example:

Workbooks.Open ("O:AdministrationPM Weekly's2007PMWeeklyWE")

My other locations are working fine. For example I am using:

Workbooks.Open ("O:AdministrationDCS Week EndingStats"

View Replies!   View Related
Work On Other Files While Running A Macro?
I have designed a macro which consists of a form through it takes the input into excel sheet. The macro is running fine and I dont have any problems with it. The problem I am facing is that after I start running this macro, I am not able to access any other open excel files. Is there any option to make all other excel files available during running a macro?

View Replies!   View Related
Macro That Doesnt Work In 07 But Works In 03
I've been searching around here to see if anyone had an answer about codes and macro changes from 03 to 07. The closest thing I found was something about lists being tables and such. However, I am still unsure of what the issue might be in my case.

Sub ArrangeColumns()
'
' ArrangeColumns Macro
' Macro recorded 3/7/2008 by ****
'

'
endRow1 = ActiveSheet.UsedRange.Rows.count + 1
Range1 = "A1:O" & endRow1

Range(Range1).Select
Range("A3:O39").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects("List1").Unlink
ActiveSheet.ListObjects("List1").Unlist
Columns("A:B").Select
Selection.Delete shift:=xlToLeft
Columns("I:I").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert shift:=xlToRight
Columns("L:L").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert shift:=xlToRight
Columns("M:M").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert shift:=xlToRight
Columns("L:L").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert shift:=xlToRight
Columns("B:B").ColumnWidth = 11.29
End Sub

So here, the red text is what gets flagged when you run the macro. I seem to be getting a Run-time error '9': Subscript out of range error.

I am not exactly sure what the macro does besides clean up and sort a portion of a worksheet. Also, there is not worksheet called "List1," but changing that value does nothing. Is there an equivalent command to those highlighted in red? Or, perhaps if anyone knows of a place where I may reference these commands myself, that would be great as well.

View Replies!   View Related
Make Macro Work In Many Rows
I'm having some difficulty with this one. I have found several posts on how to make a macro work through several rows, but cannot seem to figure out how to apply it to my macro.

Right now I'm having to write the macro for every line and it's a bear! Here's what I'm working with right now and it's ridiculous (I've actually got through line 51, but I'll only post a few here, for your sanity!)- there has to be a better way:

If Range("C9") = "" Then
Range("T9").Formula = "=IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))"
Range("U9").Formula = "=IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))"
Else
Range("T9").Formula = "=IF((T8+U8)>=180,0,(IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))))"
Range("U9").Formula = "=IF((T8+U8)>=180,0,(IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))))"
End If............................

View Replies!   View Related
Macro To Test If A Date Is Earlier Than Today's Date
I'm trying to use the Function Today() in my macro to test if a date is earlier than today's date, and I get a compile error.

View Replies!   View Related
Macro To Work On Increasing/Descreasing Data
I have a spreadsheet with a worksheet for each month, so as a new month begins I add a new worksheet using a macro

Each worksheet has 5 columns:

A = Vendor, B = Date, C = Debits, D = Credits, E = Balance

Row 30 contains the totals for columns C, D, & E, cell A30 contains the text Totals
The problem I have is occaisionally extra rows are added so the totals may not be in row 30

Is there any way that the macro can be changed so that it looks for the word Total in column A and then reads the contents of the corresponding cell E? to transfer that total to cell E2 on the next worksheet

View Replies!   View Related
How Do I Create A Macro To Work In Shared Mode
Ive got a problem with a work book im working on at the moment, my company has various different documents created in excel, like a price list, cost price list & stock levels (all Independant) I have had the idea to join them all into a single shared workbook using an idea I found and modified over a year ago on this site, that had the promise of allowing multiple users to see the nessesary parts of the same document. the advantage of this being any new parts or prices or suppliers added would update all users at once. So ive got this workbook that when you open you get asked for a username & password this then hides/unhides, protects or unprotects to suit the users needs. This works great when unshared but when I share it the login box wont display

The Administrator username is jamie and the password is joshua


View Replies!   View Related
How Can I Make A Macro Work To Hidden Sheets?
OR can I? I get a "Run Time Error '1004' Select Method of Worksheet Class Failed"

Is there anyway around this?

Basically all the sheets are hidden from the user except for the Entry sheet. They input there data on there and then they press a send command button which then needs to copy that information to a hidden sheet. This is where the problem occurs.. Thought maybe their was a way around that or do I need to unhide the sheets during the macro and then rehide them again?

View Replies!   View Related
Renamed The Sheet, The Macro Wouldn't Work
We have a sheet which we use to cost products. To ensure that people don't enter prices incorrectly, I have created a lookup to another sheet which shows current prices. Therefore, I have a macro that if I click a button, it looks up the prices from the relevant document. The way I have written the macro is to clear the sheet of what is already there and then to open up, vlookup and then close the lookup sheet.

The problem I had with this was that if i renamed the sheet, the macro wouldn't work - I sussed that one out by changing filename in the macro to 'ThisWorkbook'.

The problem I now have, is that the boss would like the sheet to magically do the following;

Lookup the prices on two external sheets. IF the main sheet doesn't have the corresponding reference THEN automatically go and Lookup on the other sheet for it.

He doesn't want any references to filenames so that, as long as the sheets are stored in the same place, they can be copied/renamed and moved anywhere.

View Replies!   View Related
Sorting Macro :: Assign Work Shifts
I need a macro that will take a list of 42 names and 27 locations. The names are broken down into group of shifts, 1 shift, 2nd shift and 3rd shift. We need to perform audits on the 27 locations in groups of 2 people at a time. What I'd like is a macro that would take all the names by shift and assign 2 random people to a location. All 27 locations have to be audited each week (safety), and a report filled out and turned into our safety committee. I would like to post the audit sheet with the macro on our shared drive. Can anyone help me with this.

View Replies!   View Related
Macro Not Working Except In One Tab, Must Save And Reopen To Work In Others.
I have a wierd issue. I have this macro for a workbook i use and if i run the macro in the first tab it runs and works fine. When i select the other tab and run it it gives me a debug error. If i close the worksheet save it and reopen it and run it in the other tab it runs fine.

So in otherwords i have to run the macro in the tab desired, save it , and always close and reopen the file to run it in the other tabs. I know the code is correct because it runs flawlessly without errors but i must be missing something.

Also i currently have the macro to run on the active tab/worksheet. Can i have it run on multiple tabs? i have 15 20 and 30 tabs in this example file but in my real file i have many more milage intervals ( some tabs i would not want to select ). It gets tedious having to select one tab at a time, run the macro, rinse and repeat.

For Example Run Macro First, in the first tab. It works perfectly. Go to the second tab and run the first macro. It gives a debug error. If you save the file and reopen it and go to teh second tab and run the macro again it works.

View Replies!   View Related
How To Make This Macro Work For Auto Range
I have generated macro, all I want is when this macro runs on different work sheets it should automatically select the active range based upon column A and copy the formula in the inserted new column B, please see the macro which will make it clear. Basically whenever it is copying a value or a formula it should be for the active range.

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