I have a macro that gets activated as soon as i select the sheet which the macro is situated in. This is not so great because if i am bringing something from the clipboard the automatic macro erases what i have copied and therefore i cannot paste anything. I can make the automatic macro run from a different sheet. Here's how:
[Code]....
I have already got an anwer for this long back from this site. The code was writted by Mr. Krishnakumar
the thread is here :[url]
i need some changes to be made in this code. The existing code creates and updates the details in the sheets automatically from the master data. I just need the sum of Column I in all the sheets after the last row of Column I.
selecting all the sheets and typing the formula in I column is not possible because, the last row in Column I is different in all the sheets.
In sheet 1, the last row of Column I is Row 15, in sheet 2 Row150 is the last row.
I guess something could be done in macros.
follwing is the existing
Sub TestIt()
Dim sWS As Worksheet
Dim Sellers As Range, Seller As Range
Dim lRow As Long, fRow As Integer
Dim CopyRng As Range, ws As Worksheet
Here is the code:
Code:
Option Explicit
Option Compare Text 'for Case-Sensitive matching change Text to Binary
Sub List_Matches()
Dim sPattern As String, sPath As String, sJob As String
Dim sMainDir As String, sCommonSub As String
Dim c As Range, lRow As Long
Dim d As Range
Range("B:B").Clear
lRow = Cells(Rows.Count, "E").End(xlUp).Row
If lRow < 8 Then Exit Sub
[Code] ...........
The red is the part that I tried to manipulate to add the new directory, the problem I can see is that the new directory contains folders with the following name "WO#____" as i can see the # sign is probably throwing the program off because it can't open up the link to that folder in order to look for files. The program as it stands still works fine with looking up the first directory. I also know we had created a function to solve the sign problem for directory 1 file names. How can i use that function to directory 2.
I am trying to find a way to protect and close column groupings of a number of worksheets on workbook_open procedure by looping the worksheet codenames instead of just the worksheet names in order to prevent potential problems with renaming the sheets.
This code didn't work...
Code:
For i = 6 To 25
With ThisWorkbook.VBProject.vbcomponents("Sheet" & i)
.Protect "rbse"
.Outline.ShowLevels columnlevels:=1
End With
Next i
I have a week in sheet1. For eg.
01/07/07 in A1, 02/07/07 in B1 ....... 07/07/07 in G1.
Now in sheet2 I want to continue 08/07/07 in A1 ..... 14/07/07 in G1 and so on and so forth in following worksheets.
How can I automatically obtain the dates in corresponding columns in the following worksheets?
I have data that varies in string length across sheets but I would like to consolidate it based on name. Ideally, a pivot table would make sense to me but I have never used one across sheets (if it's even possible). I've attached a sample sheet I'm working with. This is very simplified, but assume that the shaded, gray fields are locked. Essentially, this is simulating that is getting pulled from software.
View 2 Replies View RelatedI was wondering if there is an easy function that would copy only some part of a list as if like I select a category.
Let me elaborate. As in a previous post i have a list of apartments and a set of categories (locations, tube stations, number rooms, distance to tube stations, etc).
I want to get all the items that are the same from a category and post them automaticly on another sheet to single them out, for instance all of them that are on Liverpool Street station or all of them that have 2 rooms.
Is there a simple way to do this or do I have to get a macro to read my whole list? I ask cause I am continuasly updating the list and don't want to recreate the formula all the time.
I am trying to make some sheets with football teams from one championship and their results. For example i will have a match between TeamA vs. TeamB that will end 0-0. I will enter this value in the sheet for the TeamA results but normally this value will also be found in the results sheet of TeamB.
My question is, how can i make excel copy this value once i enter it for TeamA in the results sheet for TeamB.
I would like Excel to do the following and have no idea how to write the instructions. The function is as follows:
From a general information sheet (sheet #1), XCEL looks at a cell to determine if that cell holds "A" or "B" (will be a word, and choice will be previously fixed by a dropdown menu). It then looks at another cell in the same row to determine if it equals 0 or 1.
If the cell holds A, and the other cell holds 0, XCEL copies the row from the general information sheet onto sheet #2 of the same workbook.
If the cell holds B and the other cell holds 0, XCEL copies the row from the general information sheet onto sheet #3 of the same workbook.
After XCEL has copied a row, it inputs a 1 in a final cell of that row so it will not copy that row again.
On sheet #2 and sheet #3, XCEL begins (and copies to) the first open row in column A (by moving there after performing its last copy function or by checking column A until it finds a cell = 0), so that each new copy function begins in an open row.
I have multiple sheets all of which are identical except for the number of rows containing data. I have been trying to create a macro to update these sheets into one 'Master' sheet but I'm having great difficulties due to me needing to leave Column A and Row 1 blank.
I have uploaded example data of what I am after, sheets 2 - 6 need to be automatically updated to the 'Master' sheet when the macro is run.
Is there any way, in which i can make my code dynamic?? For example, in a code written in VBA, there is a reference to particular sheet, say "ABC". If i change the name from "ABC" to "ABCD" from excel, the underlying code should change automatically from "ABC" to "ABCD".
I want to make my code dynamic..
Would be a massive time saver but can't find anything here or on Google on how to do this.
View 1 Replies View RelatedMy data is in the following format,
Company Name Bill Status
GTAEPL ACCEPTED
GACEPL REJECTED
I need a vba code if the in Bill status column rejected is there all those rows to be deleted.
I have finaly completed my project to include all the bells and whistle needed to make an ok task a bit more exciting. However, i am having a problem when the code is executing. It seems to "break" at random points in the process. I am not able to duplicate this error at my home (nor do i want to) and I can not quite figure out what is causing this as it only happens at my work computer. I have tried my coworkers computer and the code executes flawlessly like at my home.
Strangely enough, when the code breaks I am able to hit 'continue' and it continues on its way until the next hiccup. Please assist as this fluke is causing my brain to spiral.
i have one excel sheet (refer attach) i give some example 7 to 8 customers, but in sheet nos. Of customer name a to z suppose to 3000 customer list. Now i want to create automatic customer alfa- numaric code.
View 2 Replies View RelatedI need to do VBA coding. Got 12 Sheets for 12 month of Sales. Every Sheets are in same Header Format.
For Column R (Status), there's Filter Data "TRUE" and "FALSE". I have to move/copy "TRUE" item into Sheet Aging 2014.
I manage to transfer using only one Sheets using Advanced Filter VBA, failed with other Sheet.
I attached the file : Sales 2014.xlsx
I'm using the following code to copy columns of data in a worksheet of mine. The code once activated will open "notepad" and copy the columns of data in my excel worksheet. Here is the
View 6 Replies View RelatedI have a macro that takes input from the user and replaces certain text on several worksheets. One of the inputs is a username in the format of firstname.lastname. I need to manipulate this input such that the dot is removed and the first twelve characters only are used, all in upper case. e.g. Michael.Jackson would become MICHAELJACKS
I know how to use cells.replace to replace the text but I don't know how to use a formula to manipulate the inputbox text before I do the replace.
I have some sample data in "Sheet1" and another sample data in "Sheet2".
The intended output is shown in "Sheet3".
For example: if "Student1" in Sheet1 match the data for "Student1" in Sheet2, then all the entries for "Student1" in Sheet2 will be copied to Sheet3 (ouput). The same thing goes to other students in the list (Sheet1).
Sheet1 (sample data):
Student1 | Student2 | Student3 | Student4 | Student5
Sheet2 (sample data):
Student2 | Student5 | Student3 | Student4 | Student1
Lesson1 | Lesson1 | Lesson11 | Lesson1 | Lesson1
Lesson8 | Lesson2 | Lesson2 | Lesson23 | Lesson10
Lesson3 | Lesson3 | Lesson31 | Lesson3 | Lesson3
Lesson10 | Lesson4 | Lesson4 | Lesson44 | Lesson4
Lesson5 | Lesson5 | Lesson52 | Lesson5 | Lesson15
Lesson6 | Lesson6 | Lesson6 | Lesson6 | Lesson6
...............Lesson7..................Lesson10 | Lesson7
...............Lesson20.................................Lesson11
Sheet3 (output sample):....................................
I have a dataset that has replicated Data Values for example "Bob 25" in one row, "Bob 32" in another and so on. This is contained in one data sheet. I want to be able to do something like a VLOOKUP however I want to every instance that the data value occurs to be represented sequentially in my table, so that every instance of "Bob" would be in represented in my table.
View 2 Replies View RelatedI had a problem today where I had to make some changes to a couple of large text files (100mb+). It seems that the File System Object (Microsoft Scripting Runtime) can only read or write at any one time so I ended up writing this. It opens two text streams at once , one to read and one to write and creates a revised file. Not very exciting but I thought somebody might find it useful ...
Sub ChangeTextFile()
' Manipulating a text file with VBA
' Loops through text file and creates revised one
' This code requires a reference (Tools > References) to Microsoft Scripting Runtime
Dim FSO As FileSystemObject
Dim FSOFile As TextStream, FSOFileRevised As TextStream
Dim FilePath As String, FilePathRevised As String
FilePath = "c: est.txt" ' create a test.txt file or change this
' adds "_Revised" to your file name
FilePathRevised = Left(FilePath, Len(FilePath) - 4) & "_Revised" & Right(FilePath, 4)........................
I have the following text file. I need to show in excel as follows:
1st column : Company No eg 006
2nd column : Expense Type eg 060
3rd column : Agreement Type eg HIP
4th column : Agreement Number Columns C&D added together.
5th column: Tot Def Expense
The breakdown above should be at an agreement level.
Range("B5").Select
With ActiveSheet. PivotTables("mypivot).PivotFields("team")
.Orientation = xlRowField
.Position = 2
This will move the data row field called "team" to be the second from the left. I would like a way to move whatever (without mentioning the field's name) field is in position 2 to position 3. Similar to an "offset" to the right.
In Cell B1 I'm trying manipulate (either sum or find the difference for) the current and most recent prior values of Cell A1. The value in Cell A1 changes frecuently because it gets its values from a DDE link.
How do I :
1. Display in Cell B1 the current change in value of Cell A1 ?
2. Display in Cell C1 the sum of changes which have taken place in Cell B1 ?
3. Ideally I'd like to also be able to manually reset the value of Cell C1 to zero at any time.
I think this is a macro, or array, or pivot table thing, and is probably simple, but all of that is well beyond my current skill level.
i have a userform..and on clicking ok the excel sheet behind is populated..if the value in a cell is -1 then the cell should turn empty as in the cell should contain no value.
View 1 Replies View RelatedI'm trying to write a macro that will take 5 articles that I have written and separate the sentences out....
So what I need to happen is I take the articles and pop them into excel, then the macro will pick the first sentence of the first article, then the first sentence of the second article, then the first sentence of the third article....and basically repeat this for all the sentences in all the articles also while adding a "|" in between the sentences and adding a starting "{" and an ending "}" for each of the first sentences, second sentences and so on.....
I don't know that I'm making any sence here so here is a very small example of what I need to happen....
Article 1 Paragraph 1: I like blue. I like Green. I like Purple.
Article 1 Paragraph 2: I like flowers. I like dasies. I like tulips.
Article 2 Paragraph 1: I like football. I like basketball. I like soccer.
Article 2 Paragraph 2: I like food. I like wine. I like beer.
Article 3 Paragraph 1: The sky is blue. The sky is dark. The sky is night.
Article 3 Paragraph 2: I love stars. The moon is big. The moon is full.
So each of articles would need to be placed in separate sheets I'm guessing?
Below is what I need the text to look like when the macro has finished.....
Spun Article Paragraph 1:
{I like blue.|I like football.|The sky is blue.}{I like Green.|I like basketball.|The sky is dark.}{I like Purple.|I like soccer.|The sky is night.}
Spun Article Paragraph 2:
{I like flowers.|I like food.|I love stars.}{I like dasies.|I like wine.|The moon is big.}{I like tulips.|I like beer.|The moon is full.}
I hope this all makes sense.....
I already set this up using the record macro feature of excel and it works ok but I end up having to do a lot of editing because it will put to many brackets in or not enough so I was hoping for some advise or possibly some code example that could get me headed in the right direction....
I have a variable COMTXT that loads (via loop) several short lines of text to form a "mouthful" of comments together. But have 2 issues with it.
1- I need to add a carriage return after loading each short line of text. [code] Module1.COMTXT = Module1.COMTXT + Sheets(3). Cells(Module1.COMCODE, 1)
[?CODE]I have try'd [& vbCrLf &] in many syntax's to no avail.
2- When displaying COMTXT to a sheet, its too big for the cell ! and not able to enlarge the cell without major disortion to rest of sheet. How do I acheive this and allow the text to display like a textbox or label, covering many cells and rows.
I routinely work with RUMBA mainframe display and was trying to create a VBA macro to enter data from a spreadsheet into the mainframe but after pressing enter the mainframe has random wait times on the status bar saying "Host Busy...Please Wait" and then changing to "Ready" when able to accept my next command.
how I can make a method of waiting for the mainframe to be ready before excel continues its sendkeys.
I'm trying to learn about class modules and how to create object that i can manipulate within a sheet.
i'd like to create a simple cirlce object that i can visibley see on a sheet.
is this possible and if so any idea on how i start?
I am working on a file that is going to import and then manipulate data from another excel file automatically. I would like this to be as smooth as possible and I am running into a problem. The file I am importing the data from is password protected, so I am opening it in read-only. Also, the file contains links to other excel files.
What I would like to do is indicate in the code to open the file in read-only format so that the user doesn't get a message box asking for the password. Also, is there a way to suppress the message box about updating the links? I have tried application.displayalerts = false but that didn't solve my problem.
