I am trying to solve below issue, but cant think of a way to solve it:
All the columns has alot more data, with more groups/fruits/clients etc., this is only a short version.
I need to create a Macro to help me converting the Data to the Convertion template, based on which Group is difined in Cell B17.
I've attached 2 test files, one is the database master file containing the projects (each row represents a project, unique reference number in column A) and the other is the blank template file i'm hoping to export data into and then save down with the naming convention "column A_column B.xlsx"
Kept the test files simple but would need to modify any code to apply to much larger database consisting of many more fields etc.
Master.xlsx‎
Template.xlsx‎
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
how to create a copy of my employee attendance template. Is there a way to create a copy of the template by entering an employee name in the "name" cell of the template and it automatically renames the sheet that employee name and also saves or recopies the template for use with the next employee?
View 8 Replies View RelatedI Have a number(30+) of excel files based off of one template. I have since updated the template, thus making the the old ones obsolete. Is there a way to update the older files to use the new template without having to copy and paste the addition's row by row column by column?
None of the additions to the template will change the placement of existing data, nor will it change the data itself. It will however add Values to cells that were previously empty.
Hints, Tricks, or Ideas?
1)
I created a one-sheet template, and a new workbook from that sheet. When in that new workbook I go Insert>Sheet>Other and select the aforementioned one-sheet template, Excel crashes, or if not, it adds the new sheet, but then no longer saves the workbook and starts producing error messages (like: "An unexpected error has ocurred. AutoRecover has been disabled for this session of Excel.").
1a)
Now, the complication is that this is working when I do a model operation with a generic template sheet. So I checked the template that I actually want to form sheets after, and Excel finds no errors, nor does it's name contain any unusual characters. What could Excel prevent from working with a template like this?
2)
How do I edit a template? The only way I can find is to manually find the spot in finder, open, and save with the same name. But: if I do that, the documents basing on that template don't change accordingly. If they're intended not to, the whole template procedure makes no sense. I could then just as well copy a file. — I've been searching for tutorials on that, but google doesn't even return a single result on Excel "edit template". Therefor my very basic question here.
I'll start with the attachment since it's always easier:
Attachment 327508
This file is far from finished so there is some useless stuff there. What I'm interested in is in the sheet RecapTable (and in Etiq1 to Etiq4).
I'd like to write a macro that'll populate my Etiq1 -> 4 according to the RecapTable. Data would be set up as in Etiq1 A1:A3.
I don't have to have 4 template sheets and it could change if it's easier to do something else. The goal is to print labels so I maybe only 1 Etiq is needed, and the macro could populate, print, erase, finish populating, print again
1. Count entries in RecapTable (found something that does that! yeey!)
n = Worksheets("RecapTable").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count
((2. Divide that by 65 (which is the number of labels per sheet) and round at upper number. For example: if n=100, 100/65 = 1.54 so 2 sheets needed)) [maybe a useless thing to do]
[Assuming I only populate 2 cells]
If v is the row# in RecapTable,
If w is the column# in RecapTable,
If x is the row# in Etiq,
If y is the column# in Etiq,
v=1, w=1, x=1
[code].....
Need to fit my raw data (Raw Data Sheet) to fit my table template.
Need to convert my daily data to weekly to fit to my table template.
Please see attached file for reference.
I have a list of invoice #'s on a sheet named "Temp Sheet".
I have a VBA macro that created a new tab for each entry and named it the invoice #. So basically the vba code created a new tab ( based on the number of invoice #'s on my list ), and named each tab an invoice number. So if I had a list of 10 invoice #'s, named S1-S10, the vba code created 10 tabs, named S-1, S-2,. S-3.....
Now to my question.
I have a template sheet I want to copy from ( "Template" ), and select any sheet that starts with "S", and copy/paste this template to.
I need to create a "purchasing programme sheet from extracted data in a different workbook.
In short I need to open the Allocation, Add stock qauntities to the the boxes with a quantity in it and if its a zero i need (Once the button has been pressed) the information to be extracted to the corresponding boxes in the Purchasing Programme Document: Description, Part Number and quantity.
able to press the create report button ive created, which will open a template report sheet....
View 13 Replies View Relatedpicking things up. At the moment I am trying to create a macro using VBA to do the following in this exact order:
1. Delete Column A and B (once only)
2. Automatically pick up credit card number (In column A)
3. Copy all instances of this credit card number (and all data on same row)
4. Open a template I have set up
5. Paste the copied data onto a specific sheet on this template
6. Save as file name "xxxx - branch name - XXXX - DDMMMYY"
7. Repeat (loop?) this for all credit card numbers on the sheet.
There are approximately 90 credit cards, and I need a new workbook for each one to work on separately.
I have an excel template populated with information. But I cannot extract the data to run pivot table.
View 3 Replies View RelatedI've set myself a project to try and automate some repetitive filling in of word documents and would like a point in the right direction. I've done some research on the MSDN and some sites on the web. The way I was thinking of doing it would be,
make a template with either Fields or Bookmarks (which would be best?) for each piece of data on the paper work.Put the repeated data into an excel sheet (with a button for the macro to be assigned to).
write some VB script that will, define and "label" the data in the XL sheetopen the word templateenter the data into each Field/BookmarkSave as a new file in a "New" folder (name of file and folder taken from the Data)open the next template and repeat.
would this be the best way of approaching this problem?Whats the best way of defining points in a word template, Fields, Bookmarks?what objects would I need to use to rename the folder?
I have a .dot word template that has the little 'grey' boxes that is awaiting information to be filled in, this information is already stored on a master excel sheet and the doc is simply for 'archiving' and users benefit, from my point of view its pointless.
So what I want to know is...
If I always have a .dot file which has :
Username : DATAHERE
Password: DATAHERE
Email: DATAHERE
and I have an excel file which is A1 : Username B1 : Password C1 : Email how easy / hard is it to create a button macro to automatically go to the .dot file location, open it and then put the create data in.
I've been looking around to find a solution for my problem and as a last resort I have decided to make a post, and I will get straight to the point. I have 3 xl Files of relevance:
1 - is my "database" which consists of multiple sheets with different information in each. Each sheet is correlated with each other by one common ID. 2 - is my "template" which is 2 sheets, which functions as a report. The template serves as the Report which will be printed. The data from each row from the database can fill the spaces in the template.
3 - "the tool" xl file is where i have my macros and the mapping for the which columns from the database belong to which cells in the template.
Basically what I'm trying to get to work is: User opens Tool clicks "create Reports" and the tool should then open the database, pick the range of rows from the database put the data into the relevant cell in the template save the "template" with the info on it, close it and do it again with each row of data from the database. So if I pick rows 4 to 34 in my tool, it should create 30 xls files from the template and fill in the data from each row into each newly generated "report".
I am trying to create a template which when filled out will automatically transfer multiple cell information to an excel database, and everytime the template is filled out - transfer the new information to a new row (sequentially) - I do not want the info to remain on the core template - it needs to be blank after every use.
I tried the template wizard - which worked to a degree but I was unable to move the database file to an alternate location and still have it work - I was also unable to alter the code created by the wizard due to password protection.
to sum up I need a template that updates numerous cells on a database in sequential rows that can be transfered to another computer / location.
I am trying (and failing) to set up a mail merge with a Word document. I would like for the doc to take information entered in specific columns and display it in the right field. I've set up the field and the excel doc and went through the mail merge wizard's steps for setting it up but it doesn't seem to be doing what I want.I am attaching the doc and spreadsheet below.
MichaelFields TEST.Contract Entries.xlsxFields Test.Contract to Provide Legal Services.docx
I have .Raw data in 3 different configurations, which can be opened in Excel.
I want to automatically extract relevant data (Columns) from these 3 different types of .Raw data before running analysis using chart using Macros.
Now here is the problem, Data from the .Rawdata needs to be accumulated. That is, I have multiple sheets of excel, which I have to open manually and extract specific data from individual columns onto another sheet to accumulate every data before running analysis.
How to tackle this situation?
1. Able to detect the right type .Raw data for use in Excel.
2. Extract data from many excel onto one main sheet, using Macros.
I have a workbook with three sheets: Collections, Client and Interest New sheets created from a range in Sheets("Interest"). The range in Sheets("Interest") holds the client names.
Then the template in Sheets("Client") is copied and pasted into the new sheets
For each new sheets cell B6 holds the client name and .Range("A10:A1317") holds the dates
Sheets("Collections").Range("D10:D1317") holds the dates and .Range("D8:WC8") holds the Client Names
if Sheets("Collections").Range("D8:WC8") = new sheet.cells(6, 4) then
if Sheets("Collections").Range("D10:D1317") = new sheet .range("A10:A1317") then
copy the value in Sheets("Collections").Range("D10:WC10").offset(0, 3) to new sheet .Range("C10:C1317")
end if
move down on row and repeat the steps.
I am currently building a workbook with 2 tabs.
The first is a data dump tab. The number of rows used in this tab will change each time it is used.
The second tab currently has a header row and in cells A2-H2 are formulas based on data in the first tab.
I am creating a macro to autofill those formulas, but I don't know how to determine what is the last row of data in the 1st tab and use that as reference in my macro.
I currently have a large spreadsheet that multiple people fill out. Each person fills out all the information in a row. At the end of the row, I would like a button that says "Generate Form" so that when clicked, a new sheet automatically opens with a template form that I created and is already filled out with the information that was just inputted into the spreadsheet. Also, there are four different template forms that could generate. For example, there are forms A, B, C and D. If the user inputs "B" into the first column of the row, then when he goes to click "Generate Form", a new sheet is created with all the information filled out in Form B.
View 3 Replies View RelatedI want to pull data from my excel file (using VBA) into Ms Word. I created a template in Word and wrote a macro to do this, it worked, however, anytime a new row is inserted or deleted in my excel spreadsheet my macro produces wrong results in my Ms Word template because the cell position has shifted, thereby producing the wrong result. How do i make it that my result remain the same when new row is added to my spreadsheet.
Example:
Excel row1: vicky 528
row2: sam 532
row3: john 092
row4: Own 211 word template: 092
This is what happen next:
Excel (New row added) Macro: ThisDocument.ScreenedPatients.Caption = wb.Sheets("CSAs").Cells(16, 1)
row1: pat 542
row2: vicky 528
row3: sam 532
row4: john 092
row5: Own 211 resulting Word template(running macro): word template: 532
But I want John to automatically go on the Word template without going into macro to change it all the time when a new row is added.
I can find code that allows me to open a word document and insert the value of a TextBox into the bookmark.
I have a bookmark in a OFT file (Outlook Template) and can't seem to make the same thing happen.
My question: Can you insert the value of a TextBox into the bookmark in the body of an email?
(Code at the bottom of this message)
I have a file with a roughly 20 workbooks. I need to split the data in each workbook into two sheets, rename each sheet, then export the data to template with multiple sheets. After splitting the data into the appropriate sheets in the original workbook, Sheet1 in has only 1 row (plus header) and needs to be transferred to "Project Entry" in the template; Sheet2 can have any number of rows (less than 100) and needs to be transferred to "Activity Entry" in the template. I need to save the template based on the value of cell A1 on "Project Entry" sheet of the template. The organization of the columns in the original workbooks is different than that of the template, so this is not a straight copy and paste operation. The cell formatting in the template must be retained.
I have half of this under wraps. I've cobbled together some code that splits the original data where I need it split, copies it to new sheets and saves the files. I now need to figure out out to get it out of this saved file and into the template.
The crosswalk of values between the original data (Sheet1) and the template(Project Entry) needs to look like the below. So, variable 1 exists in column B of the original and needs to populate column B of the template, and column Q in the original needs to populate column G in the template. The variables and column relationships are different for Sheet2/Activity Entry.
SPREADSHEET COLUMN
Variable
Original
Template
[Code]....
I need to read data for a group of charts into a master template file.
Sometimes my chart data range needs to be
AS31:AT35
Other times, when different data is read in, the same chart may need to only read
AS31:AT33
Is there any to name a range to read until row AT reaches it's first blank?
I have a one column spreadsheet. The column contains this data:
1 Name
2 Address
3 City
4 State
5 Zip
6 Telephone
7 Fax
8 URL
9
10
11 Name
12 Address
13 City
14 State
15 Zip
16 Phone
17 URL
18
19 Name
20 Address ... and so on
Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.
I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.
I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.
I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form:
| Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.
reformat a table in which data for each named person is presented in one row with mutiple columns into a table in which each named person has multiple rows and one column of data. The solution to to this has eluded me so far.
The attached example shows before and after.
I've created a single worksheet that is essentially a "dashboard" type of report that displays data for a single employee/person. This worksheet (let's assume it's my "template") contains formulae that is driven by a single lookup cell (in this case, the employee's ID, in cell, for example, A1).
This "dashboard" report (for just one individual) needs to be duplicated for the entire employee population (let's say ~100 people); this employee population is stored as a list in a separate worksheet, in a single column.
Is there a vba solution that will copy my template and populate the cell A1 (of each duplicated template worksheet) with the the employee IDs - of course, it would be ideal if the solution can search in my employee ID list and continue down until there are no more IDs and create the same number of worksheets as there are IDs?
Because the macro is creating numerous worksheets, is it possible to name the worksheets based off of an adjacent value (in the same sheet as the IDs) to each respective ID (I'm thinking of a concatenation of the employees' IDs and names)?
One major wrinkle in this process: the above process would be ideal if the "template" worksheet won't change (i.e. no changes to formatting, no additional data elements, etc.) However, it's likely that the client will want to add/remove/change items/elements to the "template" dashboard. Assuming, at this point, all 100 worksheets for each individual/employee have been created, it would be a huge pain to manually add/remove elements from each sheet. Is there another macro that would delete ALL employee sheets? That way, I can re-modify my single template and just run the original macro to re-create my duplicate sheets (but populated with different IDs) again.
