every month a create two different spreadshets and I'm tyring to figure out what is the easiest or the best way to transfer the date from the sample2.xls to Sample1.xls (samples attached). Probably is an easy solution but haven't been able to find a solution on my own so I decided to ask for your expert opinion.
View 3 RepliesI have a sheet which has multiple columns, i want copy the data from column Q to Column H depending on the filter that i'm using on column D. Below is the code, what am i doing wrong here
View 9 Replies View RelatedI have data as follows.
In the first column name of the month is specified.And in the third column of the same row day is specified.And followed by some data in rest of the columns and rows.
I need to copy the data from the presant sheet to a new sheet based on some conditions.
first need to search for the row where the word "MONTH" is there.if it is found then in the same row third column need to be checked for the day like "MONDAY" or "TUESDAY".If it is monday then need to copy 6*8 array of data i.e. (6 rows and 8 columns) into a sheet "MONDAY".if it is TUESDAY then 5*8 array data into a new sheet TUESDAY.And the search should be continued till the end of sheet.
In column A, I have unit numbers. Column B is blank. I need help copying the unit number in Column A down in Column B until a new unit number appears in column A. For example, in A6 is unit LS2, A12 is unit LS24, A17 is unit LS34. I would like to be able to copy A6 into B6:B11, A12 into B12:B16, and A17 into B17:B22, etc.
I have the code to copy A6 in to B6 and down, but I don't know how to make it stop at A12. Attached is also an example.
I have a spreadsheet with data in a1:p100 and i'm trying to find a macro that anytime the cell contents change in either column B or Column F it copies the entire row to another tab in the spreadsheet.
View 9 Replies View RelatedI have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.
I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.
In the attached spreadsheet, I am trying to drag the formula from one column to the next but I need the same cells to be used in every column with the exception of one cell. For instance, the formula I want to use is in the cell highlighted yellow. In the next cell over (highlighted blue) I want the formula to use the same D and E cells with the only change being the first part of each (G). Both the blue and yellow cell represent what I want to do. For example, the next column (H) would have (H4+D4+E4) in the first part. Column I would have (I4+D4+E4), and so on.
The D and E columns will be in every equation for each column. The problem is that excel wants to use the next column over instead of keeping D and E in each. How do I drag these equations so that it just changes the first part and keeps the D and E columns the same?
I need a macro that will special paste from column G line 4 and down, to column C line 4 and down, and then clear column E line 4 and down.
View 9 Replies View RelatedHere it goes, my worksheet has multiple data (numbers) that are distributed like this :
Column L Column AColumn B
75,36-6,73 30,74
74,75-6,68 30,76
50,7 15,0519,55
49,3115,5219,94
69,44-8,28 36,3
68,41-8,31 37,03
47,4917,5 18,75
46,5617,7 19,39
71,07-6,38 35,99
70,05-6,42 36,84
44,2523,0418,24
42,8823,7 19,25
68,65-2,4 34,39
67,75-2,35 34,84
There are about 200 rows with values, I need to copy the numbers frow row 1 that are 75,36 -6,73 30,74 ,then in row 5 they are 69,44 -8,28 36,3...etc.
So the point is I have to copy every 4-th row with values in a nearby column. Of course I've tried the Ctrl + left click for every 4 rows then copy/paste it, but I was wandering is there a faster way??
What VBA code will copy column A in Sheet 1 to column B in Sheet 3? The range of column A to be copied begins in row 2 and varies in length. Column A ends right before the last nonblank cell.
I want the row in one worksheet to contain the data in the column of another worksheet. For example, Column C in the "Agency" worksheet contains the acronyms for 57 agencies. I want populate ROW 3 in the "Child" worksheet with those acronyms without having to enter a unique "='AGENCY'!C..." formula in each cell of Row 3. How can I do this?
View 3 Replies View RelatedAt the moment I have data in column A that I would like to move over to columns B, C, D, E, F, G and H. I need to do it for the whole sheet (60,000 rows), but the data isn't evenly spaced so I'm making a bit of a mess trying.
Here are a couple of examples of what I am trying to do.
Example 1: Data on the left
Example 2: How I want the spreadhsheet to look
Example 3: What happens when I copy and paste the row 3 columns b-H
http://i41.photobucket.com/albums/e2.../Example_1.jpg
http://i41.photobucket.com/albums/e2.../Example_2.jpg
http://i41.photobucket.com/albums/e2.../Example_3.jpg
I've been messing around with formulas like: if column A = the word CD, put the contents of the cell in this cell. I'm not that clever though! I know it can be done and it's annoying me not being able to do it.
I want to copy a name from one column to another with the new column having a space before the name starts. Is there a formula for this?
Example:
Original Column
John Doe
New Column
(Space)John Doe
I have modified this micro I found in a forum. All I changed was the Range on Sheet 1.
[Code].....
It works fine except when it cuts to sheet 2 it starts at on column B I need it to start with Column C. What do I need to add ?
Attached File : Survey.xlsm
I am trying to copy columns from one workbook to another workbook as follows:
Source workbook column Destination workbook column
c2:c14 e2:e14
d2:d14 c2:c14
e2:e14 d2:d14
f2:f14 f2:f14
g2:g14 g2:g14
The data columns in both the workbooks are of same length,I just want to copy the corresponding columns from source to destination as per above referenced cells.
How do I write a macro for this?
I am using a macro to copy a variable column of data from one file column A:4to another but what I want to do is copy the column apart from the last 2 cells. I am using:
View 2 Replies View RelatedI'd like the following code to do is to go through each worksheet in the workbook and copy the value of the formula in cell S2 down the S column to the last row based off of a count of rows in column B. It's not working quite right and was hoping someone would be willing to correct it.
View 4 Replies View RelatedI have finished a subroutine that allows me to press a button to run Macro1 that will open a dialogue box to select FileB (that contains data I want to import). Right now, it will import that data (always B4:B20) from Workbook1 and insert it into Workbook2.
I would like to adjust this macro so that each time it is run the imported data won't replace into one column but instead be inserted into the next empty column. i.e. Right now the first run places it into B4:B20 in Workbook 2, and I want the 2nd, 3rd, etc times it's run to place the data in column C, then D, etc.
This is what I have so far, but it only does it for the first piece of data. I want all 16 entries.
Sub Macro1()
'
' Macro1 Macro
'
[Code]....
I have a cell "A1" that changes values. Every time "A1" gets a new value I want to copy it the the cell below the last value in Column "B". Assume "B1" contains a column name.
I want to copy A1 to B2, then copy A2 to B3 when A1 gets a new value.
Then I want to copy A1 to B3 when A1 gets another new value and so on.
The requirement is Copying a col. B value when the criteria that there is value 'NO' in column C or D. The value of col.B should be pasted into another excel workbook called 'Deliver_Error Log'.
I have pasted the Sample checklist and the Deliver_Error Log.
Sample checklist.xlsx and Delivery_Error Log.xlsx
Condition 1: Example in the Sample checklist row. 8, 9, 10 & 12 have value No in either of the column C or D hence the value in col. B of row 8, 9, 10 & 12 must be pasted as 4 new available next row items into the Deliver_Error log into col. L (Error subcategory ) i.e Sample Checklist ,row 8,value col.B = next available row of Delivery Log with col.L (Error subcategory )as Col.B (Q-Checker CheckList) and it must also automatically fill the Delivery_Error Log col.B(Data type) as "Error" and copy the Object Name (value in C2 of sample checklist) into col.E (Object name in log).
Condition 2: If all the values in the checklist are Yes in Column C and D then Copy the Object Name (value in C2 of sample checklist) into col.E (Object name in log) and set Date type as Delivery
Condition 3: If value in checklist is blank or NA take no action.
There will be one Deliver_Error log but multiple checklist of different Object names.
Why my code is not working. When I choose a single column it works. Once I select more than one column it doesn't work. It something to do with my "column1:column2" reference.
View 2 Replies View RelatedThe workbook has two sheets, "user" and "analysis". I have this code in a button sub:
View 2 Replies View RelatedI want to copy a D1-C1 formula all the way down column E. If I don't have any data in column D1 and C1, however, I don't want anything to appear in Column E.
Is there a way to hide the formula once I have built it for the all of Column E? The reason I need this to happen is because I can't have any zeros popping up in Column E, because I am calculating a running average of Column E and "zero" values would throw off my average.
How do you make a formula continue all the way down a column, so i haven't got to drag or copy it down all the time?
Lets say I have a formula in row Z, which is =SUM(A1:T1) If I want that formual to be in every cell of row Z, I would have to copy it down. However, if I do that, the spreadsheet becomes huge. I just want that formula (in fact my formula is much more complicated) to always be in row Z, for evermore, as the spreadsheet in time will have more and more rows in it.
I have an Excel file where there are multiple people's information on one sheet, and I have to split them out into multiple sheets. So I have written a loop to successfully do that.
However, I need all the sheets to have the same column widths as the original sheet. So after I insert a new sheet and cut and paste the appropriate data, I call another Sub Procedure from my main Procedure to do this. That Sub Procedure looks like this:
Sub MySetColumnWidth()
' Copy the column width for the first 30 columns
Dim i As Integer
For i = 1 To 30
ColumnS(i).ColumnWidth = Sheets("Sheet1").ColumnS(i).Width
Next i
End Sub
However, I am not getting the results I expect. The column widths change, but are not the right sizes, and I have no idea why.
One odd thing I have notice, is that is mind code, whenever I type in:
Columns(i)
VBA automatically changes it to
ColumnS(i)
I have no idea why. I have no code or variables named "ColumnS". I am not sure if this is somehow playing into my problems...
BTW, I am using Excel 2007.
I have written a code for a cell in excel but it only works for the row it is on. I want to copy this code down the column (until reaching an empty row) will cells refernced in the code changing accordingly (just as if i had written the code in the cell and dragged it down a column). A sample of the code I have written is given below.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E4").Value = "L" Then
Select Case Range("D4").Value
Case 43
Range("H4") = "=2*G4"
Case 48
Range("H4") = 60
End Select
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
Excel 2007 / Windows 7.
I have a range of weekly data that I need to copy into another workbook, paste it below the data that already exists there and then delete the data from the original workbook. I would love to do this with vba but after hours of searching on how to do this my brain is frazzled.
I have 50 workbooks that I need to import weekly into one master sheet (Master.xlsm) but they need to be done individually after the weekly data has been checked. The master sheet will therefore have existing data and the new data needs to be appended at the bottom. Also column A will be blank in both workbooks so to find the last used row it will need to look in column B.
The number of rows in the weekly sheet will also vary rather than be a fixed range so I guess the last used row will also need to be found there too.
The attached file is a cut down version of my working file showing where the data starts on Row 14, I won't need to copy the headings.
Example file 21.03.xlsm
breeze.
Here is my situation:
X Y Z
Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep
A
B
C
The above format is how I want my spreadsheet to look like. On another sheet within the workbook, I have a chart with some control options that allow me to vary X, Y, Z (I can vary either of these individually). Anytime I vary any one of X,Y,or Z, then the values in A,B,and C all change. This is what I am looking for, a Macro that will allow me the opportunity to everytime I change or vary an option (X,Y,Z), I can hit "Save Scenario" and the new values will be appended to my "Report" sheet. The values of A-C and X and Y are kept on a sheet entitled "FY 07". X is maintained on a sheet called "Inputs". So in all, X,Y, Z and A,B,C are all copied for each scenario.
I am creating a training document for work; the sheet I'm creating is a summary sheet which works out how many people answered questions correctly, incorrectly (bringing in from another sheet), number of questions answered and percentage of correct answers.
Looks like:
Correct: =COUNTIF(sheet1!E2:E36,"correct")
Incorrect: =COUNTIF(sheet1!E2:E36,"incorrect")
Number of questions answered: =SUM(C4+C5) (correct+incorrect)
Percentage right: =SUM(C4/C6) (correct/number answered)
I need to drag these 4 formulas down into another 400+rows, however in the correct and incorrect formulas I need it to keep the 2:36 but change the e to f, g, h, etc.