I have an Excel Spread Sheet that lists all of the people who have been issued Keys in your workplace, so the row has multiple information columns (Name, Department, Key Code, etc). One row is titled "Left Workplace" and you can select either a Yes or a No. Based on that selction I want it to copy and paste into a different spreadsheet (Either Inactive - if "No" is selected or Active - if "Yes" is selected) What is the best way to go about this?
I would like to copy and paste the values from a worksheet (HS-Detail) in a file to different tabs depending on the value in column A (Regions). I have a macro that works but it pastes the formulas and it takes quiet a while to run due to the number of records. How can I alter this to paste just the values and speed up the macro?
I've two sheets: "present" and "removed". I would like to have a whole row copied and deleted and existing rows moved up in "present" when I type the word "yes" in column E. The deleted rows are to be pasted into "removed", with two extra pieces of data - date removed and name of person removing it - to be asked in pop up boxes and inserted into col F and G respectively in "removed".
I've searched for a solution on the board and the internet too but to no avail; I am trying to create a macro that will select the entire column based on a cell reference G1 which is infact a Date.
Column G to Column S are pre-defined with a specific Date in row 8. If the Date in one of these columns (G8 to S8) match with the Date in cell G1, say it finds a match with H8, then I want to copy the range F14:F2000 and paste as values in range H14:H2000. The possibility of a match found is limited to a single columns from G to S.
I have a file that has two sheets, I have some formulas in the first sheet named "Data",What i would like to have is, If column G in the sheet Data is "Closed", then i want that particular row to be cut from the sheet " Data" and pated in to the Sheet "Done".
Please see attached sample worksheet. Column A will be generated by the user manually.
I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.
I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.
I have a spreadsheet and I was wondering if there is a way to copy from a specific range in say A1:A5 in Sheet1 and paste into Sheet2 where the column matches cell A1 in Sheet1.
I am trying to do this in VBA and I was wondering if there is a way to do this.
This code basically copies data from one worksheet to another worksheet based on the date and works great.
However, I have been asked to changed the format of the spreadsheet and the code needs to be changed. The code is in "This workbook".
The code takes the data from the summary tab and copies it to the archive tab. I now need it to take it from the summary tab and copy it to the archive 2 tab.
I have attached a copy of the spreadsheet. Data and Archive2.xlsm
The idea is to find the new references that have been created every month in sheet 2 and paste into Sheet 1. How to find the new ones is easy .However I don't know how to move the data around sheets!
Sheet 2 (A1: AQ25135) A B C D
FIND NEW PO Purch.DocItem Vendor Vendor Name
200095185710 200095185710 15152695 UAB Pajurio Gabija
[Code] ......
I need to do the following:
Whenever you find the word "NEW" in a row of sheet 2 in range A2:A50000 copy the row from B to AM and paste it into sheet 1 Column A to AL.
However Column A contains already data. The data should be pasted right below the last A row with data
In sheet 2, column A contains the following formula
I am trying to copy data from one workbook to another. I have an excel Database (Sheet1) with a macro that prompts the user to select an excel workbook which has the data I'm trying to copy.... I got this part down....From this opened workbook, I want to copy the Range("C2:C12") and paste into my database(Sheet1) workbook.
However, I need a dialog box to pop up for a user input to designate which row the data must be pasted into. The dialog box will search column H in the database (Sheet1) workbook to designate the appropriate Row # in which the data will be pasted.
Now the ranged that I copied earlier will need to be pasted selectively if possible. Range C2:C8 will be pasted from Column HG:HM and C9:C12 will be pasted from HO:HR. Is it possible to selectively paste a range in such a manner or should I just split it up? I'm not really sure the order in which I should do these commands or how to go back & forth from one workbook to another. I would like for the workbook filename to not be a factor.
I have created an action plan in Excel, with a column containing the values Yes, No and Partial to show prgress against the actions. I have used CountIF (in cells D72 to D74) to count the number of each Yes, No and Partial. I want to be able to put these figures in a chart each month. I am using columns F to Q for the chart data. the headings of the chart columns are month values, e.g, F1 is "6" (for June).
I have used Month(Now()) to generate the current month value in cell B1. I now need a macro that will check the value in B1 against the chart table headings in F1 to Q1 and if it finds a match, will copy the values D72 to D74 into the chart table below the relevant heading.
I have a time card report which will record the time spent on work for each employee. Each month, i have to generate the time card report and copy the data to my master file. Is there any code that can auto copy the time card entry directly to the master file? I only need some of the entry on the time card report, some of the column can be ignored.
I was thinking of creating a macro that will prompt me to choose the file to import as there are different file for different individual. Attach here with the master list (Demo.xls) & the time card for one individual (Nov-KTTHAM.xls) for reference.
I want to copy the data on column B of the time card to the column A of the master list, column C to column B, column D to column C, column E to column D, column F to column E and column G to column G.
In the .xls I am working there is a column with miscellaneous info about the job such as "Copy machine HP3X1A requires service"
I have been charged with going through 1 year's worth of calls (1248) and pulling that printer name (the "HP3X1A) out of the misc. column and putting it into a new column titled "Printer Name"
The printer names vary a bit but usually start with the same three letters.
Is there any function or formula or whatever I can use that will be able to identify these printer names and export them to another column?
i.e. COPY all "HQPxxx" from column 7 and PASTE to column 2
This would save my an ungodly amount of time. I've been doing it for a bit less than an hour and I'm only on to October.
The sheet I have attached has three tabs one is a master sheet another is the production input and the last is an engineering input section, I have coded the production input section to input into cells and email the engineers when they have a new job, the issue i have is with the engineering input.
The engineers can currently go into this sheet and input the job number onto the engineering input section and it will show them the current issue from the master sheet (I have already entered 1002 in it), what I want them to be able to do is input into the white sections below the description and when they click on update entry a macro takes the job number entered into B3 and saves the information onto the mastersheet in the correct column/row which corresponds to the job they are updating.
However the engineers may need to go into the same job several times and update different information therefore the fields need to not paste into the cells if the input sections are blank.
I need to copy and paste the specific dates from the specific city that the user will insert in the "dash" sheet. Basically, I need the macro to read the city and range of dates that the user will type into the "dash" sheet and copy the columns "AA" to "AD" from rows specified by the dates input. Once it has been copied, it has to be special pasted just the value onto the sheet "1" beginning at location B2.
the the data will not be available as it is vlookups to another excel sheet that i have not provided. I have deleted the data, but you can pretty much put 1's everywhere.
I have a workbook with 2 worksheets, Sheet1 and Sheet2 have the same column headings in Row 1 but they are not in the same positions.
What I need to do is read the column header in Sheet2 Column A (Cell A1) and copy the data from A2 to last row and paste it in Sheet1 in the first empty cell under the same column heading, which may be the Column D position instead of Column A like in Sheet2.
It needs to loop through all the columns in Sheet2, copying the relevant data to under the correct header in Sheet1.
Ive been trying to find out how i can paste information in the next available column but between certain columns. Here is an example of what im trying to do. There is a calculator which represents 3 machines. It runs different senerios if you change the % of product going into it (cells to change this are light blue). What im trying to do is take the information the machine outputs to the right and organize it on the next worksheet.
The data on the next worksheet is a combination of all the machines performance (in yellow) together and the machines personal performance (in red). when the button is pushed currently, I have the overall scenerios information filling down rows. What I would also like it to do is see if im using a machine (because it can be turned off by changing the usage to 0%), and if I am, copy the results form the individual machine to the next worksheet. When it copies it needs to see if the first machine slot is open and if not the second and paste in the available location. I cant have a specific spot for each machine on the real worksheet cause there are maybe 100 machines and if 15 are used I only want the first 15 machine info slots filled and im gonna hide the rest of the columns. The way the columns fill also cant be like the rows where it looks for next available free spot. It has to only be for the specific range in red because there is info to the right of where this is going.
Sub Send_Data() Sheets("Calculator Sheet").Select Range("AD9, AE9, AG9").Copy Sheets("output sheet").Select Range("B65536").End(xlUp).Select ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub
I've been using a macro that, among other things, will split a set of data into different sheets based on one column. For example, if I have Column A populated with numbers 1-10, each entry with a "1" in column A gets pasted into a sheet entitled "1", and so on. Some of the numbers I group together (for example, let's say I would group the 2s and 3s together).
I've been using the code below, but it seems to be very fickle. Half the time I run the macro, I get a "Run-time error 1004" and it points to the line "Sheets.Add. Name = WhtSht". Is there a different (easier) way to accomplish what I'm trying to do?
Dim rng As Range, StrtSht As String, WhtSht As String StrtSht = ActiveSheet.Name For Each rng In Range("A1:A" & Range("A65536").End(xlUp).Row) WhtSht = rng.Value If WhtSht = "21" Then WhtSht = "7" If WhtSht = "34" Then WhtSht = "33" If WhtSht = "36" Then WhtSht = "33" If WhtSht = "37" Then WhtSht = "33" If WhtSht = "56" Then WhtSht = "55" If WhtSht = "57" Then WhtSht = "55" If WhtSht = "76" Then WhtSht = "75" If WhtSht = "97" Then WhtSht = "96" If SheetExists(WhtSht) Then Rows(rng.Row).Copy Sheets(WhtSht).Select Range("A" & Range("A65536").End(xlUp).Row + 1).PasteSpecial xlPasteAll Sheets(StrtSht).Select Else Sheets.Add.Name = WhtSht Sheets(StrtSht).Select Rows(rng.Row).Copy Sheets(WhtSht).Select Range("A1").PasteSpecial xlPasteAll Sheets(StrtSht).Select End If Next
I have a workbook with 2 - worksheets "DATA" and "LETTER".
The "DATA" sheet has columns "First Name" "Last Name" "Address"...etc.
The user can enter in either a "X" or a "x" in cells B6:B100 on the "DATA" worksheet.
After the user enters a X or x and hits the macro button I need it to copy certain cells from "DATA" worksheet to "LETTER" worksheet. I have listed a example below.
In this example if a "X" or "x" is entered into B6 on "DATA" worksheet and the macro button is hit, then: ....
in creating a Macro which copy and paste the data from "Sample2.xlsx" file to "Sample.xlsx" file after row count in each column. Macro should not overwrite any previous data present in workbook.
Note: Sample2 workbook is containing a Variable Columns. Macro should match the headings of the Column in both workbooks and then copy and paste the data after row count.
Tabs 1, 2 and 3 have a column of data (Column A) on each sheet. They all start from the same cell. Each cell of data are just numbers. The column (an array) of data will not have blanks between.
But, they are not the same number of data. They vary.
Meaning, Tab 1 may have 15 numbers (A1 to A15), Tab 2 may have 20 numbers (A1 to A20) and Tab 3 could have 5 numbers (A1 to A5). Each iteration of the workbook may have different number of data in the A column on these tabs.
Now on Tab 4, I want combine the data from all three tabs into one column (in column A).
So, Tab 4 has a column A with data from Tab 1 copy and pasted to (A1 to A15) as values, then (A16 to A35) have Tab 2 data copy pasted as values and (A36 to A40) have Tab 3 data copy pasted as values.
Basically, the macro on Tab 4 has to count the number of rows on each Tab that are populated with data values and figure out to copy all the data on Column A from each three tabs and paste the data value into the Tab 4 in one column of data (in values).
I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.
I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.
I have rather large Excel file that spans around 245 columns & has multiple users responsible for certain columns. Columns are chunked to provide data about a specific group. Every 72 hours, I need to provide to managers whats currently on this file in a specific summary format.
Ive recorded a macro that gets me half way there. Heres where Im clueless. After my macro deletes unnecessary columns, what remains are the columns for Main Data group + each of the 5 Business groups (Investors, Lawyers, Credit, Finance, and Support) which would have identical column headings.
(1) Name (2) Address (3) ID# (4) Control#
Furthermore, for each of the 5 Business groups, there are 9 slots (ie- a user can enter up to 9 unique entries for a particular group.)
Example- The Investor group contains 9 slots. Each slot will contain the 4 columns mentioned above. So there are 46 slots in total 1 for Main Data, 45 (5 groups x 9 slots) for Business groups.
MY PROBLEM: I need a macro to now cut & paste all data from each of the 45 Business groups slots & then paste at the bottom of the MAIN DATA slot.
The final file would be 4 columns (as listed above) wide. Data from each of the 45 business group slots would be one below the other- all of it underneath the data in the Main Data group.
I attempted to record a macro where Im copying a sections data range starting from row2 to row4000 (I know there will never be 4000 entries. I did this just to ensure that all future data would be captured) then pasting that after the last entry in the Main Data.
Example- Id copy data from Investor1 slot(starting at column E2-H2) & go about 4000 rows down. Then Id press CTRL+Down Arrow key in column A & then click the down arrow one time to take me into 1st empty cell where I would paste my copied data. I was going to repeat this process for the remaining 44 slots.
Ive added some extra entries to test the macro & the problem is that the recorded macro is pasting data in specific cell location in column A instead for looking for the 1st empty cell in column A & then pasting the copied data.
So far I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work. Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A. I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work.
Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A.
Sub transposedata() Sheets("ConsolidatedYTDReport").Select Range("E2:H4202").Select Selection.Copy XXXXXXXXXXXX ActiveSheet.Paste End Sub
I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).
For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.
So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)
I am new in Excel. Recently I have to copy a cell data from Pivot table (example value in B1 cell) to new sheet by this week. My problem is this value could move to C1 by next week & so on. I try to use the column label instead of hard coding like cell column name (B1).
