Macro To Work In A Differnet Workbook
Sep 24, 2009
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 9 Replies
ADVERTISEMENT
May 20, 2009
if it's possible to write a VBA or a code tat can merging diff workbook together. for example if i've a Folder call XXX in this folder there is
1.xls
2.xls
3.xls
4.xls
and the data all exist onli in columns. which means all the four .xls will join and be come 1 without duplication will be the best if not is ok.
Second question can i sort row by colour? for example if i've 10 rows of record and which have different colour can i write a code tat works like.
a loop to loop from the first to the last then
if .Cell.colour = Red then move to the bottom
if .Cell.colour = black then move to the top
View 9 Replies
View Related
May 29, 2007
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 9 Replies
View Related
Jun 5, 2014
I cannot run a macro because I am encountering error 1004, intersect of object. i am using a protected workbook, and I dont have the password to unprotect it, but it needs to be fill out using a macro from a different workbook. Is there a way around this?
View 1 Replies
View Related
May 24, 2006
Well this excel workbook has lots of sheets each for a specific region eg, Europe, austin etc and also sheets which have relevant data that gets used in the formula.
Now, say for eg lets considered the sheet called austin.
it does the calculations on the headcount needed for call center.
so the columns refer to a formula called gets() which then calls the erlanc function.
Now the problem here is this gets() function takes a value called calc
and this calc just a cell in one of the sheets called table. Now if I change the headcount value in the austin sheet nothing happens. Then I need to change the value of calc cell say make it to 5 or 6 and hit enter. It starts calulating the values to forecast thye headcount, but it does so for all the sheets . so it is taking a lot of time.
how to get make it run only for one sheet.
View 9 Replies
View Related
Aug 30, 2006
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 9 Replies
View Related
Nov 23, 2009
I have a macro code which gets the file list from a folder i specify and puts it in to an excel sheet as a column. I have then made a drop down list from this so the user can select the file they want.
From this file i wish to copy the data on a sheet that i specify. For example the sheet "dump" from file FR7_19.11.2009.xls (which will be a closed workbook) and paste its content in to the sheet "dump" in Summary.xls
I have a macro which opens up the closed workbook FR7_19.11.2009.xls and copies the sheet "dump" and then creates a new sheet of the same name and content in my current workbook (Summary.xls). However when i wish to select a different file to load in to Summary.xls "dump" the formulas i have been calculating information from this sheet all come up with #!Ref errors. I know this is because the macro i use deletes the old dump sheet before re adding a new one containing new data.
I am therefore looking for a macro which will simply just copy and paste the data from any file i select in to a sheet named "dump" as the data is always set out the same in every file but the values are different. I assume this will then mean that any formulas i use relating to this "dump" sheet in Summary.xls will work because the sheet is no longer being deleted and re-added the data within it has just simply been copied over.
View 2 Replies
View Related
Aug 24, 2009
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 8 Replies
View Related
Jul 23, 2013
I have some user defined function in an .xlam file that works.
Right now since that .xlam file doesn't auto open. I have a button on specific workbooks that will open that xlam so all the udfs work.
Is there a way to embed the udfs into a specific workbook so that workbook is a standalone without the need for opening a separate xlam file?
View 7 Replies
View Related
Nov 25, 2012
When I first open a workbook most of the functions don't work. for example the vertical scroll bar does not move. When I close the workbook and then reopen it for the second time it works fine.
Happens with any workbook I have.
View 2 Replies
View Related
Oct 11, 2013
I want this private sub macro to change work in all workbook. How it possible?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
With Target.EntireRow.Interior
.ColorIndex = 37
.Pattern = xlGray25
.PatternColorIndex = 24
End With
End Sub
View 4 Replies
View Related
Oct 12, 2008
I'm trying to open a file and perform various tasks. This is the structure:
Dim xlApp As Object
Dim xlWb As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open(BackupFile) ' backupfile evaluates to c: esting est.xls
xlWb.Worksheets(1).Activate
Debug.Print xlWb.Worksheets.Count
Dim ws As Worksheet
For Each ws In xlWb.Worksheets
Debug.Print ws.Name
ws.Activate
Next ws
I think I'm totally missing something. The above works, except for the loop. None of the names show up in my immediate window, and none of the worksheets are activated. xlWB evaluates to nothing, which is where the problem has to be...but why would my first .Activate and Debug.Print lines work? Also, If I declare xlWb as Workbook then absolutely nothing works after the file is opened.
Basically what I want to do is open the file, do a bunch of a stuff with it, then save and close it. I have this all working without creating another object, but the opening of the file is still visible to the user. Seems like turning off ScreenUpdating doesn't truly work.
View 9 Replies
View Related
Apr 21, 2009
I am using Excel 2003.
I tried to change the number of new sheets in a new workbook, e.g. from 3 to 1.
But the option has no effect on new workbooks. They still show 3 sheets.
View 9 Replies
View Related
Jun 13, 2014
I used vlookup function in cell C3 to find ID of employees in Sheet 1 in source workbook and then, I imported the Sheet 1 in master workbook.
The vlookup didn't work in master workbook. The cell C3 in sheet 1 that I imported is blank.
The original got the ID in cell C3, then I don't see the value in cell C3 in master workbook.
View 1 Replies
View Related
Dec 12, 2012
is there a a way to get an Array formula to work in a shared workbook? when this was put in the workbook It worked perfectly but I didn't think about the fact the workbook was going to be shared !
The formula is:
Code:
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(S$3:S$40,SMALL(IF(COUNTIF(L$3:L$38,S$3:S$40)=0,ROW(S$3:S$40)),ROWS(T$3:T3))-ROW(S$3)+1)))
View 4 Replies
View Related
Aug 14, 2007
I have a sumif formula in one workbook that relates to information off of another workbook...when the other workbook is open the information populates no problem when you close the other workbook I get the #VALUE error. Is this typical of SUMIF when referring to another workbook? I have a vlookup function that refers to the same workbook and it works fine when the other workbook is closed?
View 9 Replies
View Related
Aug 22, 2012
Using Excel 2010.
I've created a spreadsheet at work that has two summary tabs which contain hyperlinks to around 30 separate sheet tabs.
On each sheet tab there is a list of unique values in column A (and other information relating to each value in columns B to D which are repeated for more than one unique value). In column E, users enter a test script name against each unique value they wish to 'reserve', and the macro picks out the unique test script names and via the COUNTIF formula counts the frequency of each test script name for each of the different values in column B.
My problem is that the macro seems to work fine if the workbook is not shared, but errors if the workbook is saved as shared. The error is 'Run time error 1004 - Unable to select the MergeCells property of the Range class'.
Here is the macro code:
Sub Get_Policies_Per_Script(updCol As Long, ShtName As String)
Dim rowctr As Long
Dim tgtrow As Long
Const ppsformula As String = "=COUNTIFS($A$3:$A$65000,I$24,$E$3:$E$65000,$G"
If updCol = 5 Then 'test name column has been modified
[Code] ..........
View 9 Replies
View Related
Dec 15, 2008
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 12 Replies
View Related
Jan 31, 2007
I have created a workbook with vb code that include 4 modules, 1 userform. These all work in the original workbook. When I copy and paste the workbook onto another laptop, none of the scripts work? Both laptops run the same os plus office 2003.
View 2 Replies
View Related
Feb 4, 2009
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 9 Replies
View Related
Jul 1, 2014
I've got a PDF file that I was hoping to convert into an Excel sheet, but it doesn't look like that's possible, xince I'm working with only Adobe Reader, not Acrobat. Anyway, I need to compare 1 column of this report to another report that IS in Excel. Is there a way to have a macro compare an Excel file with a PDF??
View 2 Replies
View Related
May 28, 2009
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 2 Replies
View Related
Apr 17, 2007
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 9 Replies
View Related
Sep 5, 2008
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 9 Replies
View Related
Oct 22, 2008
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 9 Replies
View Related
Apr 7, 2009
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 9 Replies
View Related
Sep 19, 2006
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 9 Replies
View Related
Jun 22, 2007
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 3 Replies
View Related
Jan 11, 2009
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 4 Replies
View Related
Mar 3, 2009
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 9 Replies
View Related