VBA - Manipulate Worksheet By Code Name
May 21, 2014
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
View 7 Replies
ADVERTISEMENT
May 21, 2012
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.
View 1 Replies
View Related
Jun 18, 2014
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]....
View 6 Replies
View Related
Jan 20, 2009
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 Related
Jun 3, 2009
I 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.
View 4 Replies
View Related
Feb 9, 2010
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):....................................
View 6 Replies
View Related
Jun 15, 2006
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 Related
Jul 15, 2006
I 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)........................
View 4 Replies
View Related
Aug 9, 2006
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.
View 2 Replies
View Related
May 2, 2007
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.
View 2 Replies
View Related
Mar 8, 2009
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.
View 10 Replies
View Related
Jan 29, 2013
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 Related
Oct 9, 2009
I'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....
View 10 Replies
View Related
May 12, 2006
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.
View 4 Replies
View Related
Apr 15, 2008
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.
View 5 Replies
View Related
Feb 23, 2014
I am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,
How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".
View 6 Replies
View Related
Oct 16, 2008
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?
View 3 Replies
View Related
May 6, 2009
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.
View 6 Replies
View Related
May 28, 2009
I have a pivot table created from a data table with three columns: Date, Sales, and Customers. I have the Date column in the Report Filter and I want to change the date based upon a value in a cell range named Date, of all things. The pivot table is located in another worksheet and the range Date is changed by a spin button in the active worksheet.
My code is:
Sub ptDate()
Dim pt As PivotTable
Set pt = Worksheets("Pivot Tables").PivotTables("PivotTable1")
pt.PivotFields("Date").ClearAllFilters
pt.PivotFields("Date").CurrentPage = Range("Date")
End Sub
View 9 Replies
View Related
Jul 19, 2007
I retrieve data about local reservoirs (elevation, precipitation, gated release, ...) from a web site [url]. Retrieving the reports requires manipulating three drop boxes ("Select lake", "Year", "Data Type"). After that, I copy the data to the clipboard, and, back in Excel, push a button that pastes the text and extracts the data.
I'd like to programmatically manipulate those controls so that data retrieval can be completely automated.
View 4 Replies
View Related
Mar 22, 2008
I have 2 workbooks, one containing all my code and userforms and another containing all my data (which is continuously updated/ dynamic). I want to be able to manipulate the data without ever opening the workbook (at the moment I use the open workbook method, but it is extremely slow). I have found some code (Jwalk) which I think meets my needs (and is instant). I have adapted this so it points to a specific path rather than the active path as below etc. but I need to adapt it further so that:
1. When the data is opened it is formatted exactly the same as it is in the original sheet which includes any coloured cells, bold fonts, cell width, height and so on
2. Any changes I make can be saved back to the original sheet in the closed workbook.
3. The data always opens in a named specific worksheet i.e. "TempSheet" which sits in the workbook containing the code (my code is called from a userform within this workbook).
The code I am using from Jwalk is as follows:
Sub GetDataDemo()
Dim FilePath$, Row&, Column&, Address$
'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Book1.xls"
Const SheetName$ = "Sheet1"
Const NumRows& = 10
Const NumColumns& = 10
FilePath = ActiveWorkbook.Path & ""
'***************************************
I am still learning VBA and can adapt/write small pieces of simple code, I have tried incorporating the paste special method, but in all honesty, don't know what I am doing and it results in lots of errors. Can anyone help me get this working/point me in the right direction? Also, what does 'Const' do? how do I bring in the data without specifying the number of rows, as this will be different every time?
View 4 Replies
View Related
Feb 4, 2009
I am currently trying to manipulate date ranges for monthly and quarterly reports, and am having trouble doing this. I have attached a file with an example of what I would essentially need.
I would need the "Nbr of Projects", "Nbr of Days worked", "Nbr of International Projects" and "Nbr of Local Projects" cells filled in under each respective report, based on the data in the top left.
If a project falls in two months, such as "18.12.2008 to 15.01.2009", this would be treated as 1 project for December and 1 project for January. Also, I would need only "NETWORKDAYS" included in the solution, so 11 days in this example.
View 7 Replies
View Related
Mar 9, 2009
I am trying to create a calculator in excel that has five parts. I want to manipulate each part based on the other parts. I cannot figure out how to remove the formula and replace it with the derived number without a circular reference. The circular references slow down the main macros of my program.
View 2 Replies
View Related
May 2, 2008
I m working with bar charts and as I've heard these labels can be a bit of a pain. I've got the labels in roughly the right position 90% of the time however that other 10% has me in rolling fits.
My issues
1. A label within the chart area is wrapped on two lines, how could I restrict Excel from wrappign the label
2. A Labels position covers some of the data reported and thus makes the graph messy
how could I find the position of the end of the bar and also the length of the datalabel so I could reposition it. Note I am using 2003 and that I am trying to use
activeChart.seriesCollection(s).item(i).Datalabel.left
for finding the position although this is only really where im getting to. finding the wrapping position and also being able to manipulate the length of the label is proving tricky.
View 6 Replies
View Related
Mar 31, 2007
I have column A and column B:
I will be inputting data into column B.
I need excel to check to see if the data I input into column B is an exact match to the data in column A.
If it is an exact match, then column B will remain blank.
If the data in column B is different, I need column B to show the following:
No match: <data>
Example I input in column B the following:
Column A Column B
1. Car Car
2. 4357 9999
3. fsd34d 4erd
4. 98dkf 98dkf
Spreadsheet should show:
Column A Column B
1. Car
2. 4357 No match: 9999
3. fsd34d No match: 4erd
4. 98dkf
(Cell 1 and 4 in column B are empty because they are exact matches to Column A cell 1 and 4)
My questions:
1) How does the excel formula need to be written for this to work?
2) Is there a way to set it so that when I do a mass copy to data into column B that the formula will not be overwritten and it will still check to see if the data I copy and pasted into that column matches the data next to it in column A?
View 14 Replies
View Related
Feb 12, 2014
I have the code below:
[Code]....
That will send the worksheet 'DAY SHIFT' in pdf format via e-mail.
I want to add the worksheet called "NIGHT SHIFT" to this .
View 14 Replies
View Related
Nov 5, 2007
I am looking for VBA code to help with a tracking spreadsheet I have created.
I'm still a beginner in VBA, but I know what I need it to do, just not how to execute the command. I believe it's something that would be simple for someone more familiar with VBA and Excel.
What I would like is when a Completed Date is entered in column J on Worksheet 1 (named Work in Progress), that row entire row of information would automatically be cut and pasted into Worksheet 2 (named Work Complete).
Sounds simple enough right? lol But I can't find code online that would do this and the edits I've tried to any code close to what I'd want do not work.
View 12 Replies
View Related
Sep 11, 2006
I'm having an awful time specifying the sheet I want to:
a) derive the data from
b) put the processed data into
Suddenly (never had this problem before) Excel keeps switching the definition of Application.Sheets(1) and Application.Sheets(2) so I have no way of controlling where the data is going.
How do people usually specify the sheets they want? Obviously my method is unreliable, but I'm not sure which one I should use.
View 9 Replies
View Related
Feb 27, 2008
I need to to use VBA to copy a worksheet (which i've managed to do!), but I need the new worksheet to have a Worksheet_Change event. Now when I copy the worksheet, the event doesn't copy over (obviously as its a cut and paste jobby). Any ideas on what code I need to add in the Worksheet_Change event just after the new worksheet is automatically created?
View 14 Replies
View Related
Nov 12, 2008
Kill Worksheet Code. I am using
View 4 Replies
View Related
