Select specific worksheets & copy - code problem

Hi All I've tried to write the following code to: - find worksheets in active workbook with "Planned" in cell A1, - then select all those worksheets and copy them into one new workbook - then format each sheet within the new workbook (e.g. select a named range & copy/paste values etc) The workbook and worksheet names are dynamic I'm sure I've got the "End If"'s / "Next" in the wrong place (this always confuses me) At the moment it copies the active worksheet (which does not have "Planned" in cell A1) and a blank new worksheet? - and then it stops. Any help would be greatly ap

Error Handle problem

Hi, I tried to copy some filtered rows. If there is no rows to copy, it will encounter: Run-time error '1004', No Cells were found. ie the code stops at "result.SpecialCells(xlCellTypeVisible).Copy" Then I change the code to "If result Is Nothing Then " and continue, It smoothly completes the process. However, it fails to copy when there are rows to copy ie the code skips copying the rows and reach at End If Codes Extract Sheets(1).Select Dim Lrow As Long Dim result As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row Set result = Range("A2:G" & Lrow + 1

touch screen apps

just wondering if anyone has developed apps that work with a touch screen. any info would be appreciated. thanks -- Gary Keramidas Excel 2003

Pasting to Cells to the Right.

A sheet contains AC NO, DATE, and Amount of Clients. 116530 17-Feb-10 2000.00 39361 17-Feb-10 300.00 40236 18-Feb-10 350.00 10563 18-Feb-10 100.00 116530 19-Feb-10 250.00 Ac Nos are names of the excel workbook (116530.xls) located at D:\conference\ AC No Workbook looks like this. A Date, B Time, C Country, D Rate, E Minutes, F Total, G is blank for remarks, H is date and I is Amount. 3-Mar-10 7:37 PM UK 1.00 58 58.00 2-Mar-10 200.00 3-Mar-10 8:46 PM UK 1.00 57 57.00 3-Mar-10 9:30 PM UK 1.00 25 25.00 4-Mar-10 500.00 1. I have to find first empty cell

Odd behavior using CONCATENATE

Hello, I have one spreadsheet where I am trying to use CONCATENATE to combine some text and a number. The cell T6 contains "AT COST X " The cell S7 contains a number 1.18 The cell S8 contains =CONCATENATE(T6,S7) I get a #VALUE error on this, and I can't figure out why. Any ideas?

Printing of option buttons

I have a file that contains 3 option buttons on one of the sheets. Printing of this sheet is prevented except via a command button on the sheet, which activates a custom print Sub that I wrote. (I used the BeforePrint event and a Boolean variable to restrict this). For some reason, the hard copy prints of this sheet do not show which of the 3 option buttons is currently selected. The option buttons are printed, but the "hole" for each one is empty in the print even though one is clearly selected on the screen. I have another file with an apparently identical situation in whi

Macros Help Needed, Thanks in Advance

I have an Excel workbook with approximately 50 worksheets; I have been able to enter into the VBA the following a) automatically name worksheets, b) sort worksheets by name,and c) utilizing uppercase characters in specified areas. My problem is with the first worksheet, which is a master inventory sheet, I created a macro and appropriately used the shortcut "Ctrl A"; the Macro works perfectly when the sheet is unprotected; the problems begin when I lock all the sheets, for obvious reasons, then I get a message error. Is there a way to by-pass this; I cannot leave the workbook u

Repost - checking Forefront updates

Using VBA does anyone know how to retrieve the latest Forefront updates date? -- Steve

Copying Data from a workbook to another workbook

I am trying to copy data in a merged cell from workbook/worksheet to another workbook/worksheet where the receiving cell may be a different location and different size. This is to be done via a VB macro because I will be doing multiple worksheets.

Pivot

-- Krystal K. Peters

BeforeDoubleClick Event not working

I have this BeforeDoubleClick event, below, that I am trying to run. Everything was working fine yesterday now, it just jumps from the "If" to the "End If" statements. I can't figure out what's going on. Any ideas? Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim errorWS As Worksheet, siteWS As Worksheet Dim i As Integer, rw As Integer Set siteWS = Worksheets("Site Milestone Dates") Set errorWS = Worksheets("Error_MarketList") errorWS_lastRow = errorWS.Range("A65536").End(xlUp).Row If Target.Address() >= "$A$5" Or Target.Address

Index match formula required

So I think what I need is a index match formula but not 100% sure. Basically what I need is an insurance calculator based on age, sex and smoking status this is what your premium would be. One work sheet one are my variables. Colum B I have used validations so only the specific cl can not change them. A B Enter current age 30 - 34 Male or Female Male Smoker or Non Smoker Non-smoker Desired amount of coverage 50,000 Units of coverage required 50 Worksheet 2 r

Create OptionButton at run time

I their a way I can create an option button at run time using vba and place it on a specific worksheet named "Admin" Thanks.

not like syntax in querry not working

Hello all- I am pulling data from an access dbase and have ran into a syntax issue. Does anyone what is wrong with the last line? The data pulls, but ignore the not like statements and i get all the items with gfm and cfm in the material name along with the data im looking for. I need to exclude all records with GFM or CFM in the material field. Please help strsql = "SELECT PEGG_TASSRAW.Plnt, PEGG_TASSRAW.Material, CJI3ByReplen.Description, CJI3ByReplen.PurchDoc, PEGG_TASSRAW.[Replenishment Element], PEGG_TASSRAW.[Grouping WBS el], PEGG_TASSRAW.[Assigned WBS], PEGG_TASSRAW

How to print with a gap in the middle of a sheet

I am trying to setup a print button with code to print a range of data on a 11x17" sheet of paper, portrait layout but to have a break in the middle of the sheet always so if you fold it in half you will not be folding over text. I need something like a footer in the middle of the page. I have 2 seperate parts of the data range. One is for first shift and the other is for 2nd and 3rd shift. So if you fold the 11x17 sheet in half you get 1st shift data on the front and then the 2nd&3rd shift info on the other half but with a break in the middle where the crease is so no text gets folded

Modeless User Form Is Blank

I want a modeless user form to appear, then my program does some work (clearing the screens of all drawing objects, which takes a while), and then the user form to unload. I've gotten this to work, except that the user form is completely blank and white. The label control with my message doesn't show. I've tried DoEvents before showing the form, repaint in the form initalize (as suggested in other posts on this topic), and this doesn't help.

How do I add numbers in two column based on the date in another?

Hi Please help. Using Excel 2003, I am trying to find a way to determine if a date changes in a range, total amounts in two columns, by date, by route otherwise return a blank. A=Route# B=Date N=Total Hours(of a call) O=Time Between Calls P=Total of N+O A B N O P 01 16/02/2010 1.25 FALSE 01 16/02/2010 1.17 0.167 01 16/02/2010 0.50 0.083 01 16/02/2010 1.25 0.083 4.503 01 17/02/2010 1.00 FALSE 1.00 I trie

update in the workbook by different users

I have a worksheet shared by 6 people at sharespace. Please let me know how can I control the parallel updates of all in the sheet. e.g Let us say all users open up file at same time t. If user 1 make some change and save it at time t+1. Then if user 2 is saving it at t+2, updates entered by user 1 will be missed out. Please advise how can I manage this using macro or sharing control. regards sanjay

Out of context error in user defined function

Apologies to anyone who has already read this in Functions. I realised after a few daysa of no response that I had posted in the wrong stream. Excel 2007 query Error received in VBA window (Debug) = <Out of Context> Error on Spreadsheet = #VALUE# I have 'formatted' this function in much the same way as my twenty or more other functions in this spreadsheet, yet I end up with Out of Context. I have tried various approaches including an 'On Calculate' called sub-routine, but all to no avail. The code for the function is stored in Module1 of an xlam, which is where all my o

Sumproduct formula needed

Data************************ A B=85..col 001 40000 002 25000 999 3200 005 11360 920 -3000 992 -165870 170 3290 210 31090 991 -9220 ******************************* Hi all, I need Sumproduct formula in cell C1 which should check those values in column A which starts with 9 and then do the SUM of values in column B. I tried formula (see below) but its not working. SUMPRODUCT((A1:A9=3D9**)*(B1:B9)) I can solve it by SUMIF formula but I want to do it in SUMPRODUCT formula. Please can any friend help me

How to reset the range name to refer to another cell?

I have set "G" as the a range. In the later part, I need to use "G" as a reference to set itself as another range. what is the correct syntex? Set G = Worksheets(E).Range("B21") .... Set G = Range(G).End(xlRight) <-----Error occurs here Please help!

Call Stack Available ?

When browsing the Locals Window, I happened upon the call stack display. I looked in Help but could not find a way to access it with VBA. It might be handy in error processing in knowing how you got somewhere. If it's available, how do you access it? Thanks. -- Neal Z

ShowDetail

When data is extracted using ShowDetail, the data is always extracted to a new sheet. Is there any way that this data can be extracted to a named range?

Excel Crashes when trying to open a User Form

When I open the workbook and click on the worksheet to open the user form excels stops working. I get Microsoft Office Excel has stopped working and is trying find a solution, but it never does. I have saved it in both 2007 & 2003 versions and both get the same problem. What is odd is if you open the Userform Workbook, then open the VBA code window, both userform versions run fine, but if you do not open the Code window Excel locks up everytime. Here is the code. Sheet 2 Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub Us

Unprotect Workbook with Function

I unprotect and protect all worksheets in a workbook with a User Defined Function that returns the password. Sub MyExample() ActiveSheet.Unprotect Password() ' do stuff to the worksheet ActiveSheet.Protect Password() End Sub Function Password() As String Password = "AdTech" End Function This works great! But for some reason when I try to unprotect or protect my workbook I get a Run Time Error. I know the password is "AdTech", because I can manually unprotect and protect the workbook, but VBA doesn't like to do it thru code. Sub MyExample() ThisWorkb

ComboBox and rowsource from an external sheet

Hello, I'm trying to fill a combo box from a column of names in another workbook. Here's what I have: menu.ComboBox1.RowSource = "'C:\[data.xls]'!A1:A100" This appears to be correct. I get an error code 380 - could not set row source property. Can someone tell me what is wrong with this code? thanks,

if / then structure with macros

Hi there, I am using a macro to count colored cells. You can see the code below. Then I use to count the cells with =SUMPRODUCT(--(ColorIndex(Sheet1!F33:F285)=35)) where 35 is the colorindex (i got it with =colorindex(a1) ) now i need to and an if statement:) something like, scan the range of F33:F285, find the cells containing number 3, check if these cells has colorindex 35. with a noob approch it'd be like =IF(COUNTIFS(Sheet1!B33:B285,3),SUMPRODUCT(--(ColorIndex(Sheet1!F33:F285)=35))) I thing I cannot figure out if/then structure right:( any ideas? thanx in advance.

Manadatory break after 10 days of work formulae

Through the assistance of the exceptionally talented moderators/ contributors of this group, I was provided with the formulae shown below. It works to assisst me in scheduling crews of workers based on a 10-4 or (10 days on and 4 days off) or a 21-7 (21 days on and 7 days off) schdule. A new twist now requires that I modify this formulae slightly to include a mandatory 2 day break following 10 days of work. As such, the 10-4 schedule works just fine, but I need to have two "off" days appear after 10 working days for those people working the 21-7 shift. Thank you in advance for any

WorksheetFunction Calculation Error

errorWS.Range("B3").Value = Application.WorksheetFunction.CountA("A5:A" & errorWS_startRow - 1) The above line of code is always 1, even when errorWS_startRow is much > 1, i.e: errorWS_startRow=144, 749 etc. Any ideas what is going on?

Chart datalabels linked to cells

I have an old Excel 2003 macro that duplicates a sheet with a column chart on it. The column chart has data labels that are linked to cells in the sheet. Problem: Now that we're in Excel 2007, the datalabels on the chart on the duplicated sheet still refer to the original sheet. So we want to adjust the macro so that the labels point to the same cells on the active sheet. Can someone just give me the syntax? e.g. Activechart.Seriescollection(1).datalabels(1).formula="=data!C3"

Modify code from paste values to change font color / multiple rang

Hi, I was wondering if you could help me out modify a code that currently paste values to change the font color to automatic or black and to add multiple ranges (new ranges are E8:E17, E24:E33,E40:E49 and E56:E81) The current code is below and works Great! Thanks to everyone that helped/will help me out!! Sub PVPrImpct() With Range("b48:b74").Offset(, Worksheets( _ "Summary by month MTD").Range("A4") - 1) .Value = .Value End With End Sub Thanks again!

Launching from IE

Hiya, I've built an Excel file with a user form that provides boxes to search our SQL database and populate the spreadsheet with the data it finds. This works absolutely fine when opening from a local or network drive, but when providing this on our intranet, the form works the first time, but when clicking the New Search button (which just invokes the form) I get VBA error messages. Any ideas? Thanks

Need a accumulator driven from second cell

I am new to VBA, just got book yesterday. Found some code for entering on a column works get all by itself, but does not help with problem. Also found code for a single cell accumulator, also work real well all by itself. Need to marry these two codes, so that I input on column C and get an accumulative total on column F. Here are the codes: Sub ChkColC() If ActiveCell.Column = 3 Then If Not IsEmpty(ActiveCell) And ActiveCell <> "" Then MsgBox "Has Data" Else MsgBox "No Data" End If End If End Sub -AND- Dim Val Private Sub Worksh

vLookup with comma break

Greetings all, I have a worksheet where cell B8 and B10 perform a vLookup based on a data validation in B6: =IF(B6="","",(VLOOKUP(B6,Position,2,0))) IF(B6="","",VLOOKUP(B6,AD_Groups,2,0)) I have the following code in place to perform a line break after each comma in the returned value. It works great the first time, but then anytime I change or add something to any cell in the range B1:B10 it adds another line break to B8 and B10. Anything after B10 is fine and doesn't affect the previous cells. I guess what I'm asking is if there is a way to alter this code (which I fo

If statement in macro to find blank cell/value in another cell

Trying to find a macro that will allow me to find a blank cell in a column, and if the corresponding cell is populated, then tag that cell with a certain value. So, if Column AG is empty, but Column AM is not, then put an * in column AG, then loop it to look in other columns as well. So if AG = blank, but AM is not blank, then place * in AG, if AN = blank, but AT is not blank, then place * in AN, if AU = blank, but BA is not blank, then place * in AU, if BB = blank, but BH is not blank, then place * in BB, etc.... For a total of 15 segments

Excel 2003 VB referencing #N/A not working but works in Excel 2007

I have a very simple macro that works in Excel 2007 but hangs up constantly in Excel 2003. Does anyone have a work around? Problem: When referencing cells that have a #N/A value, Excel 2003 craps out even if I use the ISNA application function before it. The following 2 statements will cause Excel 2003 VB to stop regardless of order if the cell that is referenced comes up with #N/A. Series = Cells(RowNumStart + X - 1, SeriesCol).Value If WorksheetFunction.IsNA(Cells(RowNumStart + X - 1, ColNum)) Then Further background.... I am actually wanting to find #

How to add a worksheet from template without duplicate cell styles

I need to add worksheets based on worksheet templates to a workbook. I would like to use the cell style "normal" as defined in the workbook. However, all cells in the added worksheet with style "normal" now have style "normal 2" and will not use the formatting set in the original workbook. Is there any way to get around this without going through all cells in VBA and changing the style?

update sheet with data at bottom of present data

I have 2 excel 2003 workbooks. Workbook one has 5 column with headings. Workbook 2 has same five colums with headings. 2 gets updated every month with data in A2:E5 (under the headings). I need this data range to go into the workbook 1 at the bottom of the last entry. This is 4 rows and five columns of data. I Need it to update only when called to update.

Userforms with CommandBar Popups

I've reposted this item as I answered the first post on this subject myself and think therefore it may have got lost in background clutter. I have created a class module that displays a treeview within a userform. The treeview can display several data types. For example, the user gets presented with a tree of service providers which are indexed uniquely by name and country. If the user chooses to add a new service provider, then he will be presented with a second userform ( a second instance of the same userform) that contains a treeview of the countries they may choose from. Ea

Inconsistent behaviour of attributes accessed through structured r

I have a table that represents information in an outline structure. The table has two columns, "Level" and "Requirement" that look like this- Level Requirement 1 Top Level 2 Next Level 2 Next Level again 3 Further sublevel 2 Back to Level 2 Now, what I want to two functions that will- 1) Set the indents of the "Requirements" column based on the level numbers, or- 2) Se the Level numbers based on the indents of the Requirements column. Sounds easy, And one way it is! Can anyone explain why this works perf

reading data from .xls file

Hi, My boss "ask" me to prepare so called application, which will be able to create report about certain data. Some people are working on their files /.xls/ which consist of lots of data, but I only need to get from those files only certain data /e.g. name of customer, date, number of order, quantity/. I don't want to open their files /using vba/ to copy data because it's too long /and I don't know when my cooworkers are using their files/. The question is - is there any method to read /retrieve/ data from that files without openning them on my computer ? thank in advance

Manipulate filtered data

I have a shortage report generated daily by the company server. It has PartNo (Col A), Owner (Col B), Comments (Col C), Qty (Col D). Part numbers will only appear in this file if qty falls below certain value. I already have a macro that can filter the daily shortage report by owner (which is my name). Based on the filtered list (all part numbers that belong to me), I will update the comments column. Now I created a master file with all the part numbers that I need to take care of. It has 2 columns: MyPartNo (col A) and Comments (col B). I also created a macro that can m

Password for running Macro

I need to hide and unhide some columns using a macro and then prompt for a password when unhiding. I used the protect worksheet function and set a password, and this worked fine. My user has now informed me that the protect worksheet will already be in use..... so now I just need to have a macro that hides some columns, but the unhide is prompted by a password. Is this possible without using the protect worksheet function?

Adding daily updates to other master sheets

Hi Please can nayone help me in following There are an activity sheet which will contain update related to orders in coulmn D. I need to tranfer the updates column H and Column J of this worksheet to different worksheet as per match found in column D. Column J entries need to be replaced in other sheets which I am able to do as per macro_run. For B = 2 To 50 For A = 3 To 50 If Worksheets("Activity").Cells(B, 4).Value = Worksheets("IP_MPLS").Cells(A, 1).Value Then Worksheets("IP_MPLS").Cells(A, 32).Value = Worksheets("Activity").Cells(B, 10).Va

Situation when file is "read only"

My workbook is used in a networked environment by 5 users. If one user opens the workbook, that workbook will appear as "read only" to the remaining 4 users. So, if one of the 4 users opens this file in "read only" mode, I want to inform them who currently has write access to the file. Are there any VBA functions which return the username or user ID of the person who has current access to the file?? Thank you!

Last used row within filter

Lost of threads here mentioned about how to get the last used row but I couldn’t find one about the last used row within a filtered range? Say my used rows are from A1 to A20, row A20 may or may not show up after filter, what is the code to get the 20 as variable? Any idea please? Regards

compare value of cell in one sheet to range of another sheet

Still learning how to use VB coding to it's most effectiveness... I have two sheets. sheet1 and sheet2. I want to pull the value of cell(2, "d") from sheet1 and compare it to every value of column "d" in sheet2, highlighting as it goes by. Once it has finished I want it to go to cell(3, "d") in sheet1 and do the same thing, comparing itself to all the values of column "d" in sheet2. I know how to pull the variable I want, I know how to do the highlighting code, and I know how to do the loops and whatnot. What I do not know how to do is referance seperate sheets. help??

Responding to Keypress event on a sheet

I want code to respond to the keypress event on a worksheet. How can i do that?? Actually i need a code that runs a predefined when some key is pressed while the sheet is active. In particular, i want to disable the enter key (ie. the selection of cell should not shift to the next cell) and then it should run a macro. Thanks in advance.

val() is not work in my code

wrote the below code and i'm haveing problems when i get to: " StrAStart = CInt(Val(Left(SltArea, StrM))) StrAEnd = CInt(Val(Right(SltArea, strL - StrM))) " part of my code, it looks like it is when i get to Val. When the program gets to these lines it doesn't put anything for variables StrAStart and StrAEnd. Varaible SltArea will contain something like "$A$35:$J$43". I am trying to get the starting, and ending row that was pasted by program. If you know a way to fix my code to make this work or even a better way to get the info i need, it would be greatly appreciated. Pr

Distorted display in spreadsheet apps

A recent issue (2 or 3 months) has become a real nuisance. In several spreadsheet apps I have designed, when data is entered into a cell on a worksheet, the display is distorted with a row from the 1st two worksheets. If I scroll out of range and return or if I deselect the sheet and come back to it the display returns to normal. This is happening to dozens of other users as well in multiple locations in my company. My IT dept says this could not have resulted from any network change. I have been designing these apps for many years but this distortion has only recently occured. T