Macro To Compile Info From WS To WS
Feb 12, 2008
I'd like to build two macros that are slightly different but are both along the same lines.. The first one will take only certain columns of information from the "Master" sheet and dump them into the "Public" sheet. (I want only certain information to be seen by others). I.E. say I only want columns A, H, K, L from the master sheet to go in A, B, C, D in the Public sheet. I hope thats enough detail.
The other macro I want to build will take the A, B, C, D information from the Public worksheet and dump it all into another workbook called "Client Lists". The link to these two documents would be over a network so there shouldn't be any issue there... This way, all the people I work with can have this document and have up-to-date information on clients etc.ed...
View 9 Replies
ADVERTISEMENT
Feb 6, 2010
Currently where I work we print out registers to keep track of student attendances. This is done by merging an excel document that contains all the details of the names, days, dates , times, student groups and rooms of each lesson (each row contains the details for one lesson) with a word document containing the appropriate list of students in each specific group along with blank mail merge fields that relate to the excel document.
There are often 100 lessons a week meaning it takes an age to merge all the documents to create the registers.
Instead of this method I'd like the tutor to print off their own register by opening the excel document and clicking a button at the end of the appropriate row that contains the details for the lesson they are teaching. This would then take the details from that row and generate the appropriate register on another worksheet. Importantly it would have to list the correct group of students for each lesson - the names of these groups would be listed on other sheets.
Im guessing macros would be the right way to do this, but I have no real knowledge of them so alternatives would be helpful. Can anyone point me in the right direction? I've included a sample of how it would look with just one lesson in the excel doc.
View 8 Replies
View Related
Dec 3, 2013
Basically I want the macro to look through a column of values and if any cell in that column has the value 'LOCK' the macro would hardcode 'n' into the adjacent cell. Additionally, because the column in which the LOCK cells would appear is part of a vlookup, it returns #N/A which I would like my macro to ignore and then continue to evaluate the next cell all the way to the end of the column.
The code is the following:
Sub Hard_Code_Cells()
On Error Resume Next
lastrow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
[Code] .......
I've been receiving the error message 400 when I trigger the macro and I'm not sure how to correct my code to resolve the issue.
View 8 Replies
View Related
May 9, 2008
I am writing a grading program, which during the course of functioning must run other code that has errors. I have used On Error Resume Next code to allow the program to keep functioning when it has run into errors so far. However, when a subroutine is called that calls another subroutine with incorrect parameters, I get a VBA error message pop up. Inserting On Error Resume Next directly into the other code doesn't stop this problem. I either need a way to have it skip over any call to a subroutine with improper parameters, or to check if a project can compile before grading it.
View 3 Replies
View Related
May 29, 2013
I am running into a Procedure too large error when running my macro. My macro is designed to replace a cell value with another cell value in a list. When the cell value is replaced a vlookup brings in new data to my workboook. Then the macro refreshes all the pivot tables and saves the workbork. I want my macro to repeat the above 60 times, so I have copied the code 60 times and changed the ActiveCell.FormulaR1C1 to point to the next value in my list. (Is there another way to select the next value from the list without changing the Row and Column number?) This is where my macro fails.
Below is my code.
VB:
Sheets("Data").Select
Range("B2").Select
[COLOR=#FF0000] ActiveCell.FormulaR1C1 = "=RC[25]" ' AGC GRP_ID[/COLOR]
Sheets("Ship pivot and cum triangle").Select
ActiveSheet.PivotTables("PivotTable4").RefreshTable
Application.DisplayAlerts = False
[Code] .....
View 9 Replies
View Related
Jul 30, 2014
I created three macros to work with reporting for my company.
One sorts a field, another deletes unwanted columns, and the third double checks columns for answers--I work for a telecom polling firm.
It was working last week on my computer, and it still works on another computer at the office. However, I keep getting an error message when I try to run it.
The message is "Compile Error: Expected Function or Variable". The code is below, and bolded where it breaks down.
--selection.Autofilter
[Code] .......
What could be the rationale for the sudden breakdown? I was testing it on a file that I have tested with before without issue. I also tried a second file, and even restarted my computer. I am currently trying to write a fourth macro for counting responses and giving percentages, so while I don't need to have the perfectly cleaned data to do so it would be nice to have.
View 4 Replies
View Related
Feb 11, 2010
I am creating some code to unprotect a workbook, unhide columns, delete a series of cells, rehide columns and then reprotect the workbook:
View 14 Replies
View Related
Jun 21, 2009
i'm trying to run a macro I recorded then amended to define a final row, but when I step in to the macro it says Compile Error - variable not defined. Please can someone help, surely I am defining it so I don't know why it doesn't work? Is there anything wrong with my FinalRow line in this macro?
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 21/06/2009 by Richard Shaffer
'
'
Sheets("Core Finance").Select
FinalRow = Cells(65536, 1).End(xlUp).Row
Range("A3:O" & FinalRow).Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
End Sub
View 9 Replies
View Related
May 30, 2007
I'm done creating a macro. how can i do to compile it. I know there is an option in the tool bar, but i don't get any .exe file after clicking on that option. Do i need any compilator library or something?
View 5 Replies
View Related
Apr 1, 2014
I'm trying to use the macro below (twice but with different variables) but everytime I get the error "Compile error: Constant expression required".
[Code]....
View 3 Replies
View Related
Jun 18, 2009
I'm trying to use MATCH to identify the Column number that contains "DL_Error", then I'll use this Column # to select a cell (found Column # & "34"), but I get the error shown in the title of this post.
Here's my current
Sub Macro15()
' Macro recorded 6/18/2009 by me
Range(Match("DL_ERROR", "A31:CW31", 0) & "34").Select
End Sub
View 9 Replies
View Related
Aug 24, 2007
I have recorded a simple macro ( copying a cell, and then pasting the formula into various others), and I get the following error
Compile Error: Argument Not Optional
I have highlighted where the error first happens
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/08/2007 by Michael Traynor
'
'
Range("K7:K8").Select
Selection.Copy
Range("K167:AJ168").Select
Range("AJ167").Activate
Range("K167:AJ168,K175:AJ176").Select
Range("AJ175").Activate
Range("K167:AJ168,K175:AJ176,K183:AJ184").Select
Range("AJ183").Activate
Range("K167:AJ168,K175:AJ176,K183:AJ184,K191:AJ192").Select
As I've said I didn't write this, it was recorded from Excel.
View 7 Replies
View Related
May 12, 2014
I would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies
View Related
Aug 22, 2014
I have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
View 1 Replies
View Related
Jun 13, 2014
I'm trying to use the "clear contents macro" for merge cell, but I keep receiving this "compile error : Expected End Sub" error.
FYI, I have named my merge cells to "myMergedCells"
[Code] .....
View 3 Replies
View Related
Jan 27, 2009
I keep getting this error:
"Compile Error: Invalid outside procedure"
What I am doing is having a macro execute every time my Pivot table is refreshed, the macro invokes a format change.
View 3 Replies
View Related
May 29, 2007
I need to make a macro to get sales information from sheet one to sheet 2 in a different order with a few other things that need to be added in everything I need is in sheet 2.
Sheet 1 is what I work with and I need it to look like sheet 2 using a macro I wrote what I need in sheet 2A13-...
View 14 Replies
View Related
Apr 2, 2009
There is a webcreated excel document that generates dates as the date of such as:
4/1/09 12:39:00 PM
I want to write a macro that will remove the date part and just leave the time. How would I write that? Times are in columns C and D and I want it to scan all the way down to row 600 and replace.
View 9 Replies
View Related
May 28, 2009
my macro is set up to do the following in my book once the user inputs a row of information. the user will go to the opproperate sheet based customer and input all info.
the macro is run (ctrl+z)
the macro takes the last row in the active sheet, and pastes it into the last row of the "ALL" sheet. then it goes and sorts the sheet in order.
i want to adjust it so that the user will input the row of information into the last row of in the "ALL" sheet. then based on the given informaiton, paste the same row in both the corresponding customer and location sheets.
im assuming im going to need to use some variables?
attached is the test spreadsheet.
View 9 Replies
View Related
Nov 4, 2009
I need a macro to clear data on 2 worksheets that i can add to a button. lets say sheet1 B55:Y55 and sheet2 b102:y102
View 3 Replies
View Related
Nov 20, 2009
I have a template work book that has several worksheets with numerous formulas thorughout.
For example;
=SUMPRODUCT(--ISNUMBER(SEARCH("MCSH",P9:P218)),--ISNUMBER(SEARCH("HWD",G9:G218))).
When I set up another template for a different crew I manually have to go through the entire work book and change the crew identifier (MCSH) in every formula.
View 4 Replies
View Related
Jun 26, 2014
I am looking for a formula or Macro if its the only way which does the following:
I have a set of locations in the below table which also has exactly the same name as a tab in the same workbook. So for example I have 1 tab called "summary" which contains the below table and than another tab called "London453" & another tab called "New York45" & another tab called "Paris3232" & last tab called "Vancouver43"
In the "Reconcilation needed" column I would like a forumula/macro to start in Cell B2 and look at the cell next to it in Location column [Cell A2] and then go to the corresponding tab in the workbook and if there is a value in cell G22 in that tab, than I would like the formula/macro to take the first 7 charactors of Location value otherwise put nothing.
Example :
in tab "London453" there was a value in cell G22 --> hence 7 charactors "London4" in below table
No value in Cell G22 in the "New York45" tab --> hence nothing in below table
Value in "Paris3232" tab in cell G22 -> hence "Paris32"
No value in "Vancouver43" tab -> hence nothing
Location
Reconcilation needed?
London453[CellA2]
London4 [Cell B2]
New York45
Paris3232
Paris32
Vancouver43
View 3 Replies
View Related
Jul 11, 2008
I have a userform which is designed to get and enter data into one worksheet.
When I call the userform up from a different worksheet it doesnt refer back to the data source worksheet?? How can I do this?
I have used
sheets("dataworksheet").activate
in the user form initialise event but this still doesnt work.
View 9 Replies
View Related
Jan 29, 2014
I need a macro thaw sends a mail to a specific address (and CC) based on a critiria in a cell. This is the code I have. It does not send the mail.
[Code] .....
View 4 Replies
View Related
Jun 4, 2009
I have to worksheets, the first is filled out and at the end Ive place a button that I would like to open the second sheet and fill in cells that have duplicate info from the first, so just copy & paste but kind of automated for speed.
I have only got as far as opening the second document with the macro, after that im stuck!
The Info I would like to be copied across from the "service Diary" to the "Service Report" would be As follows:
*CUSTOMER
*CONTACT
*MACHINE TYPE
*SERIAL NUMBER
*QUOTE NUMBER
*ORDER NUMBER
View 14 Replies
View Related
Apr 24, 2013
What the Macro has to do is this.
It takes an specific word from a certain number of files that you can choose from an add file window.
Then when you choose those files the macro starts to run every file and look for an specific word, in this case is "ROFO March 2013".
When it finds it, The macro creates a new workbook( You create the name of that workbook y the first place of the macro, when you choose the files that you want to look for that word).
So, When the macro finds the word ROFO March 2013, it copys all the columns that has that name, and keeps looking until the file ends.
And paste all that info in the new file, A consolidate File.
I have change all the names of variables of the macro so you can understand it.
Code:
Sub copy_data(ByVal Origin_File_route As String, ByVal destiny file As String)
Dim Origin_File As String, RsBusq As Range, ROFO As String
Dim Rango As Range, Fila As Long, lastrow as Long
origin_file = Dir(origin_file_route)
[Code] .......
View 2 Replies
View Related
Apr 17, 2014
I have a table like this:
1/2/14
2345
1/6/14
34665
1/7/14
5473457
For Column A-I would like to create a macro that will copy the info from cell A1 and paste in in A2. This will continue all the way to the last row containing info which will vary. So after the first copy/paste, it well go to A3 and copy paste onto A4, A5 will copy and paste onto A6 etc.
For Column B- The same only it will copy the contents of B2 and paste it on B1, B4 copy/paste to B3 all the way until the last row of data.
View 1 Replies
View Related
Jan 19, 2009
When I open my workbook I get a popup that says: Welcome Back...
Using a module with the following
Sub auto_open()
MsgBox "Welcome Back..."
End Sub
I want to have it say: Welcome Back, Phixtit
Where "Phixtit" comes from the cell "C15" on my "INFO" sheet.
Example:
='INFO'!$C$15
View 9 Replies
View Related
Nov 13, 2009
I am comfortable with getting a macro to copy & paste where I need it to, however how to get the macro to recognise when a certain value has stopped.
For example in Column B on "DataDrop" are all the divisions that exist in my company i.e East Anglia, Essex, London etc. There is an empty row between each division.
View 4 Replies
View Related
Sep 6, 2008
At job, Mac OSX and Address Book 4.0.6 with contact info including in many cases emails. I'd like to get it of there, into Excel to manipulate, and ultimately into a FileWrecker Pro database. How to export 1087 entries from Address Book including only selective data fields is the question, and I know that's "slightly" OT, but I do want to import into Excel, so I hope that vindicates me! If not, and you can point me to a good MacForum, I'll settle.
View 9 Replies
View Related
