I have 3 different sets of macros and i want to Join them together so i only have to run 1 macro to do all 3. I am not sure how the best way is to do this.
Code 1:
Code:
Public Function UserName()
UserName = Environ$("UserName")
End Function
Private Sub RefreshTeamQueryOnWorksheet(worksheetName As String)
[code]....
I'm in the process of combining a lot of worksheets into one and found the code on the following site:
[url]
which almost does what I need. I understand how it works (i think!) but as a vba novice I can't work out how to adapt the code to copy more than one range from the source workbooks.
What I want to be able to do is copy say the following cells from each workbook:
A1
D1
d4:D7
I think what I need to do is call the getdata macro multiple times with different ranges, but I can't quite work out how to do that without simply creating multiple copies of the master file.
I have two macros and I would like to assign them both to one button. How to make a connection between these two codes in VBA?
the macro should first sort the columns: A and B and then compare them. Here are two codes that need to be connected:
Sub Sort()
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("B:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C1").Select
End Sub.................................
When I paste the second code below the "Sort" code, the whole code doesn't work like it should.
I have 2 macros ready and i need to merge it to one to make it run.
First macros will do the calculation and the other is adopted from a website as an sample to display % of process. Now the issue is i don't know how to merge it. Can any one help me to setup this file please. Copy of sample file is attached for your reference.
Calculation Macros:
VB:
Sub Premium()
Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet
Dim cel As Range, cel2 As Range, cel3 As Range, cel4 As Range, found As Range, rng As Range, age As Variant
[Code]....
In Sheet1 --> Emp id, Emp Name and Emp dob having some 'p' rows
In Sheet2 --> Emp id, Emp address, Emp Designation and Emp blood group of some 'q' rows
Now, i want in Sheet3 --> Combination of both Sheet1 and Sheet2 data. Note: Emp id in Sheet1 and Sheet2 are same which indicates an unique identifier. I attached a file for your reference..
The company I work for have several templates in the map xlstart.
They want that all the macros that are present in these templates can be merged into one template.
Is it possible to do that without using VBA?
Because they want a solution that everyone can use without knowing a thing about VBA.
I have 4 different workbooks that I want to merge into one workbook. I tried moving the sheets from these workbooks into one workbook but to my surprise all the vba / macros didn't transfer over to the other workbook.
I searched here but all I can find were people that wanted to make a summary workbook or merge workbooks into a worksheet which isn't what i want to do.
I just want to simply merge these workbooks into one workbook without losing all the marco and vba stuff that are in each workbook.
I need to use to prepare for analysis a large set of individual participants' Excel data files collected using a psychological reaction-time experiment (a modified Stroop task). This experiment was created using a program called Superlab by Cedrus, so I posted an inquiry at the Superlab forum [url]. Their tech support suggested I might need to use macros in Excel but couldn't offer further assistance. I'll try to briefly describe (a) our Stroop task, (b) the operations I'm trying to carry out, and (c) the format of the individual Excel files,
Our Stroop task: The participant gets some instructions and practice trials, then a series of stimulus words are presented one word at a time in the center of a computer monitor in one of several colors. Word presentation order is randomized for each participant. The participant must press a key corresponding to the color of each word as quickly as possible. For each keypress (including the spacebar, pressed on instructions screens), the reaction time (in msec) is recorded in their Excel spreadsheet under the 'Reaction Time' variable. The 'Error Code' variable indicates whether the participant pressed the correct (C) or incorrect (E) color key in each trial. There are 8 types of words for a total of 48 trials. (Actually, 4 sets of emotion words and 4 sets of control-animal words, each set composed of 6 words each. The task basically measures how much peoples’ attention is captured by emotionally relevant cues in the environment.)
For each participant's data, I need an efficient way (macros?) to:
(1) clean it [i.e. discard or ignore the instruction & practice trials as well as trials where the participant was too impulsive (reaction time < 100 msec), too distracted (rt > 3000 msec), or gave the wrong color response (Error Code= E)]
(2) determine whether enough valid trials remain for further analysis (>39 trials), and if so:
(3) calculate a mean reaction time score for each of the 8 word types [where denominator for each word type is based on # remaining (non-discarded) trials for that word type]
(4) merge each participant's summarized data as single rows in a common spreadsheet (for importation and further analyses in SPSS), like this:
---------------------------
ID#...... MeanRTW1.......MeanRTW2...... (etc. for word types 3-8)
101.......1056................2013...............
102.......2148................2594...............
103...
---------------------------
Excel File Format: I've attached a sample file, and here's a simplified overview of a spreadsheet (irrelevant columns and some rows removed). First the ID number is recorded (e.g., 393E), then some unnecessary junk (exp name & date, intro, practice, instructions), then data for the 48 actual trials of interest:
-------------------------------------------
........A...............B..................C.................D............
1 393E
2 stroop.xpt
3 Wed Jan 1, 12:33:00 2008
4
5
6 Trial Name.....Trial No..... Error Code.....ReactionTime
7 introduction..... 1................C............561243
8 practice.......... 2................C............... 2062
9 red-worried........14.............C................1001 (*the first practice trial)
10 (9 more practice trial rows, deleted here)
19 instructions........3..............C................5000
20 red-chicken......19..............E............... 1205
21 blue-tense.......32...............C.................782
* (46 more actual trial rows, deleted here)
--------------------------------------------------
To clarify:
-The variable 'Trial Name' indicates nominally what stimulus was presented for each trial.
-The variable 'Trial No.', indicates the unique numerical marker for each stimulus word. (It probably should have been labeled 'Word Type'. It doesn't reflect the order of stimulus presentation. E.g., the word "tense" is always 'Trial No.' 32 for all participants, regardless of when it presents.)
-A subset of actual trial words (e.g., worried) were also used for the 10 practice trials. Thus, the 10 practice trials must first somehow be discarded or flagged to be ignored before calculating average reaction times for the remaining 48 actual trials.
I have 3 files out of which Inv.txt is the main file.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140701 Inv.txt and it adds the data from 20140701 INv.txt column 7 to the last column in INv.txt.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140701 Inv.txt and it adds the data from 20140701 INv.txt column 7 to the last column in INv.txt.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140617 Inv.txt and it adds the data from 20140617 INv.txt column 7 to the last column in INv.txt.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140617 Inv.txt and it adds the data from 20140617 INv.txt column 7 to the last column in INv.txt.
The two file names that it looks up from are always with a date stamp (Current date-6 Days) so this needs to be dynamic.once it has all the data in Inv.txt just save the file.
I am required to merge a group of blank cells in my Macro, but for the life of me I Cannot Get it to work. I have spent some time googling with no luck.
Basically I just wnt something like:
Range("O96:R96").Merge
Can anyone tell me if my Syntax is off at all?
Couple ways:
1) Format the cell as Text: Format->Cells->Text
2) Enter the zip code with a ' preceding the number, i.e. enter 00345 as
'00345
3) Enter the zip code with the formula ="00345" ...
In a big data sometimes I have identical rows (maybe the 'Quantity' column has different value). I would like to merge them into one and add the quantities together. I have to use B and C (I need both) to find out if these rows are identical.
For example:
A1=date B1=111222 C1=ABCD ... and G1=quantity (1)
A2=date B2=111222 C2=ABC ... and G2=quantity (1)
A3=date B3=111222 C3=ABCD ... and G3=quantity (2)[code]......
I'm trying to teach myself VBA code and macros, and I can't work out where the code should go. Do I double click on the worksheet, and put the macro code there, or do I have to create a 'module' and put the code there? I have tried to find online tutorials but I really need a 'for idiots' guide.
Does anyone have a VB macro that will copy information from a cell (this will be variable based on criteria) and paste it into the middle of another cell that contains HTML code?
The criteria for the copy/insert/paste is that the information in the cell that is to be copy/inserted must match up with the correct number (part number).
I hope this makes sense. I have attached an example spreadsheet. (Pictures are worth 1000 words. ) Hope someone can help. I checked this site and couldn't find anything that was solved that matched the specifics.
I have developed a series of spreadhseets in which I use a macro to derive and insert the data from a SQL Server db. These spreadsheets may have one or more worksheets, some of which start as blank "canvases", others are preformatted with static data.
They are used for client reporting so are heavily formatted and include graphs; some manual preformatting, some coded, some graphs drawn completely by the code, some already in place - I just populate the data source. The VBA for the most complex one extends to around 5-6000 lines of code.
While this code does not add a huge amount to the footprint of the files, I feel that it is inappropriate for the spreadsheets to be distributed to the end clients with all this code included. The code is password "protected" but we all know how effective that can be although that is not really the point anyway - I simply feel it would be more professional if the code were not there at all.
There doesn't seem to be any options on a "Save As" method which enable the resulting sheets to be saved without the macros. I have tried recording a macro in which all I did was to delete the macros concerned - nothing was recorded. So a macro to remove macros seems to be out of the question.
These spreadsheets are held in a master folder on a server and are set as "read only". The users start the a sheet, run the macro (via a menu item which displays a selection form) and then save the resulting spreadsheet as another file in another location. I am wondering if there is some way I can cut into this process and develop my own "save" routine.
I'm currently working with the "Original"sheet shown on the attached file. From this, you will see that there is generic information contained in columns A:D then columns containing a month date and the associated FTE figure.
What I'm trying to do is as follows:
-Insert two columns after the "Investment Role" column, with column headers of "Period" and "FTE"
-Then, for each month column for each record, create a new row one under the other, copy the values in columns A:D and paste them into the newly created row,
-Then move the Date value for each month and the associated FTE figure and put these in the "Period" and "FTE"columns.
I've attached a file showing the 'Original' dataset and 'Desired Outcome' to look at.
Although the values in all of the fields may change month on month, and the number of rows may change, the number of columns will remain the same.
Merge Columns Test.xls‎
I need something that will exit all macros currently in place. The reason i am asking for this is because i have code that uses Application.Run to run another macro within the current code. If the other macro called on in Application.Run has a msgbox which has an OK/Cancel option, i want the Cancel button to exit all macros, not just the one that is used to access the message box via the Application.Run.
this is the portion fo the code that shows the cancel button and my attempt to use Exit Sub; however, this only exits the current sub, not all subs.
Code:
If TnC4 = vbCancel Then
msgbox "This application has been cancelled.", vbInformation, "CANCELLED APPLICATION"
Application.Run "CLEAR"
Exit Sub
End If
In a workbook with several worksheets i would like (as part of a backup function) make a copy of one of the worksheets.
The code of the complete project is distributed over a module (module1) and several Microsoft excel objects ("thisWorkbook", but also in a number of the worksheets (e.g. in "sheet3 (Risk Management Plan)").
The backup copy of the worksheet (in this example also containing e.g. the Microsoft excel object "sheet6 (Backup of Risk Management Plan)" should be free of any macro code. this to avoid unintended problems by inexperienced users, but also to keep the file size limited.
Probably it would be possible to copy the worksheet without of copying also the code. Or it would be possible to strip the code only from this particular worksheet without influencing any other code, forms, ... . Or, perhaps, there would even a totally different approach.
I use Windows XP pro Sp2, and Excel 2000 Sp3.
I need to copy data from multiple cells into a comment box. Right now, I'm highlighting each text in each cell and pasting into the comment box. So A1 data, I am going to the formula bar, highlightin the text and pasting individulally into the comment box.
Also, is there a way to merge multiple cells into one cell . I have text in cells A1:A14 and want all that data into one cell. Is this possible?
I have been looking everywhere and I can't seem to find what I am looking for. I want to create a Macro that will go into a folder, search every Workbook (each with multiple Worksheets), and copy and paste the data onto a single Worksheet. The data that I want will begin in Row 3 of each Worksheet (the first two rows are titles and headers). The formatting will be the same for each sheet.
The only other part is that Column G has the following formula in each cell that I would like to keep if possible.
Code:
=IF(AND(ISNUMBER(F4),ISNUMBER(D4)),"Complete",IF(ISNUMBER(D4),TODAY()-E4,0))
if there is a way to select a group of cells, select another group of cells and keep the previously group of cells selected.
Example: ...
I have had a good look around and have found some scripts that look like they can help but do not. here is my situation
I have 300+ questionaires in excel that contain around 20 questions with each question having the possibility of 5 answers
i.e
A1
A1Do you like this service, answers will be scored in cells F1,G1,H1,I1,J1
What I would like is for a master document to combine all the data into 1 document so I will know how many of the 300 have F1,G1 etc.
In the filed F1 the variable may be X or it may be a number, I would like to add up the number of non blanks in that field, from 300+ closed workbooks
I have hundreds and hundreds of excel files. but in every file, there is the same column lets say column D which has all the information I want. In stead of opening hundreds of worksheets and copying and pasting over the data into a new sheet. Is there a code I could write that would open all these files and copy the data from the same colum over into my new sheet? so column D in the first work book will copy to colulm A in the new work book. Then colum D in the second workboko will copy to the new worksheet in column B ect ect ect.
I am writing a database for a construction site to monitor all the goods that come in and what apartment Blocks they end up in.
I have three worksheets from different areas of the site. They are simply lists of items coming in and are getting longer everyday. They are all in the same format and have the same column headings. Two of which are date and Location. The location will be Block A to Block G. I want to merge the 3 worksheets into 1 and be organised by date order so that I can then duplicate that worksheet for each Block and filter using the Location Column. The 3worksheets have data manually inputted each day and I would like the merged worksheets to update automatically.
I have two columns one with the variables names and the other with the values now I have to assign vales to teh variables dynamically using the macros and vba code.
View 7 Replies View RelatedI have used the following Macro that deletes the Macro code. It then saves the file and quits excel.
*************************************
Sub removeAllCode()
Dim awi
Dim awcl As Integer
Dim count As Integer
Dim i As Integer 'loop counter
On Error Resume Next
count = ActiveWorkbook.VBProject.VBComponents.count
For i = 1 To count
Set awi = ActiveWorkbook.VBProject.VBComponents.Item(i)
awcl = awi.CodeModule.CountOfLines
awi.CodeModule.DeleteLines 1, awcl
Next i
Set awi = Nothing
ThisWorkbook.Save
Application.Quit
End Sub
*************************************
However when I open the Excel File, I still get the Macro Prompt. I have checked in Macro list as well as in VB Editor and no Macro code exists
If I have the file manually (or do a save as) and then reopen the file, I no longer get the Macro prompt.
I have tried various examples listed in different forums/sites but nothing works.
I prtected several sheets with the xlVeryHidden command I got from you. But when the user does not allow Macros to be executed, also these protections are not executed. Does there exist any solution to this problem? Something like "when macros are not allowed do NOT open file"?
Sub workbook_open()
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Application.AskToUpdateLinks = False
Windows("rechenfile.xls").Activate
Sheets("Daten").Visible = xlVeryHidden
Sheets("Koeffizient").Visible = xlVeryHidden
Sheets("Investitionen").Visible = xlVeryHidden
Sheets("Konsum").Visible = xlVeryHidden
Sheets("Zinssatz_permanent").Visible = xlVeryHidden
Sheets("Preise").Visible = xlVeryHidden
Sheets("Nettotransfers").Visible = xlVeryHidden
Sheets("Beschäftigung").Visible = xlVeryHidden
Sheets("Löhne").Visible = xlVeryHidden
Sheets("Nettoexporte").Visible = xlVeryHidden
End Sub
I am encountering an error(-2147417848) when running my existing code. I did a little research and found on MSKB Q319832 information that suggests that I might try qualifying my code (not that I know what that means : D) In the article, it suggests using something like the below:
Dim oXL As Excel.Application
Set oXL = New Excel.Application
oXL.Visible = True
.....
.....
oXL.Quit
Set oXL = Nothing
I tried this approach and it fails as soon as it hits the event portion of the macro:
If oXL.Target.Address = "$C$2" Then
....
End If
I need to understand how the oXL object is used against information in my existing workbook.
I would like to merge multiple workbooks into a master workbook. All the individual workbooks are identical and only have 1 sheet, and I would like to append them to the master as worksheets then sum them all together. The front sheet showing the totals from all the merged ones.
View 5 Replies View RelatedI'm having an issue where I want to merge lots of tables held in different spreadsheets into one large table so that I can use it in a pivot table. The attached spreadsheet demonstrates what I mean:
So there are 3 different accounts held in the first 3 tabs. These all have the same columns and format. I want to merge these automatically into the table that is in tab 4 so that I can use the pivot table also demonstrated in this tab.
What I want to happen is when I add rows to the tables in the first 3 tabs, these are automatically populated onto the 'overall' table in the 4th tab. (I'm aware I could do this manually, but for what I'm using this for these will be dozens of tables with hundreds of rows updated monthly).
Is this possible? Basically it doesn't matter how it's done, as long as i can show the information held in the pivot table each month end.