I Have a Sheet that Have ove 5000 rows and 13 colums of data andn i would to archive this info in a different work book or a different sheet in the same workbook. without copy and pasting and i want to auto update as i type in new data.
View 9 RepliesI have attached a sample in my second post here.
I have searched for many hours and I cannot find what I would like to do or indeed anything similar.
My issue is that I have been asked to build a spreadsheet (I am of novice ability) to create a league of sorts relating to percentages earned by 8 different shifts at work. These percentages are then converted to points and via that through a ranking formula into a weekly table. That I have done but I have been asked if it is possible (I know it must be somehow) to somehow retain last weeks positions. So for instance, weekly percentages have been added the table has been populated, next week the same happens but what I need is prior to new information being added the previous weeks "league positions" are transferred over into a "last weeks" league positions table. Whether this can be automated or manually via button click I have as yet no idea. This problem has me now reading a "VBA for beginners" book but I do not think I will be proficient quickly enough!
I am trying to move (archive) data from one sheet to another sheet based on a date range provided by the user’s input. If the date on sheet “list” in the cells in range R9:R10000 fall within that date range the following takes place. The rows that meet the date range per the user’s input in the range of R9:R1000 are copied and just the values are pasted into the next empty row starting with row 7 on the “report” worksheet. The cells that are copied on the “List” worksheet are NOT deleted but the contents are cleared. The reason for this is because there are formulas/links in those cells.
I have attached a worksheet example and have some VBA code but it does not work.
Here is what I want to do in words- how do I translate it into VBA? I have 2 sheets in a workbook (Tally & Archive) The Tally sheet has aa set of buttons which record tasks completed (the range of these is E9 : E29) I have a current date box ( A" which uses the =today() formula). I want to archive the range E9:E29 in Tally to consecutive daily columns in the Archive sheet
My logic has got me thus far:-
select Archive sheet
look at A1 to the end of the row
If the active cell is empty then
put the Value of "Tally" A2" into the active cell
Copy the range E9:E29 in Tally to the column under the active cell
Return to the Tally sheet
I have the need to be able to open a closed file to archive some data from a
current file.
what coding i need to be able to kick the "open file" bit off?
way i can save an open workbook using a macro in that workbook
but as a certain filename and have it placed into a certain folder!
I have a spread sheet that pulls data from several different locations.
I need to update this spreadsheet once every two weeks.
I need to archive the VALUES of the spreadsheet every two weeks.
I have a recorded macro that only seems to work from Personal.XLS Modules and I need it to work from the Workbook itself so that others can use it from the shared drive that it is posted on.
The workbook sheet names are DO7, DO8, DO9, Features, and Source Explanation, but I only need the first 3 listed to archive to the new folder. The range could be A1:J100 on each sheet if the entire sheet can't be copied.
I know there is a way to utilize temp files and would prefer to use that method instead of the kindergarten level of a macro that I have pasted below.
Sub AutoArchive()
YesNo = MsgBox("This will save the document and save an archive file. Do you wish to proceed?", vbYesNo + vbCritical, "WARNING: This document will self destruct in 10 seconds!")
Select Case YesNo
Case vbYes
'Insert code for "Yes"
ActiveWorkbook.Save
Sheets("DO7").Select
Range("A1:J100").Select.....................
I'm using Excel 2011 on a MacBook Pro. Here's my situation:
I'm running a monthly budget report that's got three spreadsheets within the workbook. Spreadsheet #1 is summary sheet with a few pivot tables. Spreadsheet #2 is the data sheet, where all of the information is entered, and is what feeds the pivot tables on Spreadsheet #1. Spreadsheet #3 is just a list page that populates the drop-down list on the second sheet.
As this is a monthly budget report, I'd like to find some way to archive the first spreadsheet (the summary sheet) every month. Ideally, I'd like to be able to create a macro-driven button that, when clicked, does the following actions:
1) Creates a new spreadsheet, with each click, named for the current month and year.
2) Archives the exact contents of Spreadsheet #1 by making an exact copy of its current contents, and places this copy into the newly created spreadsheet.
3) Clears the data from Spreadsheet #1 AND Spreadsheet #2.
To summarize, I'm trying to use a button that, when clicked, runs a macro that archives the current month's data into a new spreadsheet, and then clears the data from Sheets #1 and #2 so that I can start each month with a clean slate. If there's a way to only clear the data from certain cell ranges (as opposed to a complete wipe of the sheet), that would be even better.
Code, couldn't make it work with VB HTML Maker. I'm new with VBA Basically my idea is when someone open default file, insert data, try to save file, macros save workbook with inserted data as another file (*.xlsx) and make pdf in another folder. Pdf part is working fine, but with second part just can't find right option.
Pdf part
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim Name As String
Dim i As Integer, j As Integer
Name = ThisWorkbook.Path & "PDF Archive" & ActiveSheet.Range("F6") & " Invoice " & ActiveSheet.Range("B11")
[Code]...
Excel part
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String
[Code]...
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies View RelatedI have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.
If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
I am attempting to create a macro to generate emails based on data in a sheet. The goal is to run the Macro, and have it generate emails to send to contractors letting them know what they are going to be paid. For instance:
Name in Column J
Email in Column L
Memo in Column N
Balance in Column T
Due Date in Column P
Week Ending Date in Column H
Now what I would like to happen, is to tie a macro into a button that will create the email as follows:
To Field: Email address from Column L
Subject: "Company Payment Remittance Payment Date *Date from Column P*"
Body: Hello *Name from Column J*,
For *WE Date in Column H* you will be paid *Balance from Column T* for the time worked of *Memo in Column N*
Now the tricky part is that I want the email to contain all line items for each email address. So instead of sending one email per line, have the macro automatically put all of the information that needs to be sent to one email address into the message. I don't know if that is possible, but it sure would make my life easier if it was.
I have attached a sample workbook of the data that will be used
Example Workbook for Email Macro.xlsx
I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:Â
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:Â
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
I have a workbook that includes 4 seperate sheets that are used to record time and expenses for 4 members of staff. I want to write a macro to select the data I need from each sheet and colaberate together in a 'data' sheet so I can combine all the info to run time and expense reports per client showing combination of all time and expense incurred from all 4 staff.
I have named cell ranges in each of the 4 time-sheets. I proceed to record a macro, select the first named range, copy and paste into my data sheet, do a control home then control down arrow, then one more down arrow to get to the first blank cell and repeat the process for all four time-sheets.
This works until I add a new line and then the data will only appear for the last time-sheet (last row of data).
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
I have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?
It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I am using Excel 2010
I have a table in the format below with about 3500 rows
Column A
Column B
0001
All vehicles, Retirements
0002
All vehicles, Retirements, Addition
0003
All vehicles, Retirements, Addition, Deletion from Y
I would like to change it to the following format:
Column A
Column B
0001
All vehicles
0001
Retirements
0002
All vehicles
0002
Retirements
0002
Addition
0003
All vehicles
0003
Retirements
0003
Addition
0003
Deletion from Y
I have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files.
My question is how do I write the code so the source files populate the master in a specific order.
For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit
Sub ConsolidateSheetsFromWorkbooks()
'Author: Jennifer Starr
'Date: 7/12/2013
'Summary: Open all files in a folder and merge data (stacked) on all
[Code] .....
Looking for a formula to accomplish the following:
I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".
I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
I have a question about using conditional formatting in excel (2010); I made a table with the following columns:
"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.
The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?
I am working on a project that has 5 worksheets. I have been able to figure out everything else I need to do but this has me stumped. I have data in Sheet1 A6, that i want to place in Sheet2 A6, Sheet3 A6, Sheet4 A6 and Sheet5 A6 and keep data and formatting(BOLD AND UNDERLINE). So I change Sheet1 A6 and the other 4 sheets change also. I'm using Microsoft Excel 2007.
View 1 Replies View RelatedA web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.
The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.
I can't talk to the people who changed the web program.
I have two sheets, a data sheet with all our customers by ref, name and spend; and a presentation sheet.
In the presentation sheet I want to display the ref, name and spend of the Top 50 customers by spend, price high to low.
The workaround:
Copy all data from data sheet, sequence top down by spend, manually delete all after 50.
My only issue is that each month the data sheet will update and I want the Top 50 to auto update, without performing the workaround above.
Is there a way to do this without VBA i.e. pivot tables etc
I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.
The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.
BRIDRequirement Bug DescriptionBUG ID Alt Bug ID
PQ115 PQ115-Login Bug Description text xihllloloj43712 123
PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126
For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.
These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath
BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do
BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do
So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.