Copying Columns From One Tab To Another Via Macro
Dec 17, 2013
I have 2 tabs (tab 1 is "Data", tab 2 is "Compare")
Data tab is filled with results from tests I do in a lab, each test has its own row, each 'test subject' has its own column.
In the second tab I want to have the ability to pick from a drop down list one of the other columns from the first tab.
So I have 2 columns (D and F) in tab 2 with drop down lists that I have populated with the names of the test subjects (using data validation grabbing the top row in tab 1).
I have formulas set up in Column E of tab 2 for the actual comparison.. What I'd like to do is when I select a test subject (for example SubjectA), from the drop down list in $D$1.. I want to populate $D$3:$D$155 with rows 3-155 of the matching column in tab 1 (so if for example SubjectA is in column X, it would copy from tab 1 X3:X155 to tab 2 D3:D155.
So so far I have the validation part done with the drop down list.. and I have the following VB code for tab 2.
View 4 Replies
ADVERTISEMENT
Apr 7, 2014
I have a basic question for a macro, I've looked at other threads but they all seem to have some kind of twist to it. All I want is a macro that copies columns A&B from a source workbook to my destination workbook in a specific worksheet for column A&B too. I'm assuming that the destination worksheet will automatically update whenever the source workbook is updated? My source workbook is called Job List 7 and the worksheet is called Master Job List, my destination is Ted's Timesheet and the sheet is called Job List.
View 10 Replies
View Related
Sep 22, 2012
Why does my macro mess up on second line C23 and not places Play Equipment or the amount in the right column it stays on row C22
[URL]....
Code:
Private Sub CommandButton1_Click()
Dim SheetName As String
SheetName = "Estimate1"
SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)
[Code]....
View 2 Replies
View Related
Mar 31, 2014
I have a sheet made into a form with some check boxes included. If I select all the relevant columns, create a new sheet, and paste into cell A1 - all of the information carries over just fine. You can still select cells and check the boxes in the new sheet.
However, when I record this action as a macro, using the macro produces a picture of the selected columns instead of actually copy/pasting the information. I can't click on the cells or check boxes. I can only move the picture around within the new sheet.
View 7 Replies
View Related
Sep 29, 2009
Hello. I download several spreadsheets monthly that have a large number of columns (A to EZ). In every excel spreadsheet I need to copy only 18 different columns of data from the sheet which are the same columns each month, and put them into a new spreadsheet. It is very time consuming to highlight and copy a few columns at a time throughout the whole sheet.
I am not very experienced in excel, but is there a simple way to copy all of these columns at once out of the spreadsheet and paste them at once into a new one? I would think there must be some way to enter the column letters I need to pull them out.
View 9 Replies
View Related
Jan 7, 2013
I'm currently making a workbook of annual sales lists for my small business. There are separate sheets for each year (2007, 2008, etc.) where column A consists of a list of customers for that year and the next 12 columns include sales for each respective month.
The issue I'm now facing is in trying to create a new "master" sheet of sales (month-to-month) for every year (2007-2012). The problem I'm having is transposing the sales data for each month from the previous sheets. Why I'm having trouble is this new sheet will be for every customer we've ever had whereas the past annual sheets were merely for customers in those specific years.
Thus I have annual sales sheets of 800 some customers while the final sales sheet will be nearly double that. What I've been attempting is to match the customer name from the master sheet with a specific year and if matched, transpose the monthly data associated on that yearly sheet. The formula I came up with works to a certain extent and then seems to fail because the customer difference gets lost in translation. I'm doing something wrong so that it's not looking for a match out of the entire 1600.
Here's my formula I'm trying: =IF(ISNA(VLOOKUP($A2, '2007'!$A$2:$A$883, 1, FALSE )), "", '2007'!B$2:'2007'!B$883)
View 2 Replies
View Related
Mar 5, 2014
I'm trying to copy some columns from one sheet to another using column numbers. Why this doesn't work?
Code:
Dim leftColumn As Integer
Dim rightColumn As Integer
leftColumn = Sheets("Hidden-Summary").Range("B3")
rightColumn = Sheets("Hidden-Summary").Range("B4")
Sheets("Hidden-Summary").Range(Columns(leftColumn), Columns(rightColumn)).Copy _
Destination:=Sheets("Summary").Range(Columns(leftColumn), Columns(rightColumn))
View 2 Replies
View Related
Jul 17, 2014
I have on sheet1 a number (72 at the moment) of Form CheckBoxes.
In simple terms: I would like a macro to look at each CheckBox and remember its state (Checked or Unchecked)
Then, go through and Check All checkboxes
Call MyMacro
Once MyMacro is complete (Filtering & Printing)
Revert the checkboxes to their original state.
The purpose of the checkboxes:
When Checked column on sheet2 is UnHidden
When UnChecked column on sheet2 is Hidden
Or, UnHide All columns on sheet2, run MyMacro, then "re-hide" the columns that were previously hidden.
View 11 Replies
View Related
Sep 16, 2012
I have 4 columns E,F,G,H which each column has the variant purchase cost, I need to sort between E,F,G,H and highlight which is the lowest cost on sheet 1, I then need sheet 2 to display any item that was the lowest figure from column E, then the same with sheet 3 for column F and so on to H.
View 1 Replies
View Related
Jan 15, 2013
Without moving or copying columns I need to print Column A & Column I on the same piece of paper and not print Column B-H.
Every time I try this though it is printing column A on one sheet and Column I on a second sheet. Yes I've checked margins, etc.
This is actually possible, right?
(I didn't want to move or copy columns as the spreadsheet is for someone else's use, plus my formulas buggered when I tried to so I didn't want to add that complication for the other person of double checking the formulas every time she wanted to print this two column version of the sheet.)
View 3 Replies
View Related
Jun 25, 2013
I am half way through writing a bigger macro, but am stuck at this stage.
I need to copy from a block of data as shown:
However, this column varies in length each time, and I only want to copy down to where the entries stop.
I also only want to copy W1:X6, and columns Y, AA, AB, AD (but only down to the end of the column)
I've tried using the End(xlDown)).Copy function, but obviously this doesn't work with the block up the top and the gap between the block and the columns
View 4 Replies
View Related
Aug 26, 2013
I am trying to create a macro that will create a new workbook, copy certain columns to the new workbook and add new headersEaxample is:
In workbook 1 copy columns A To Workbook 2 Coumn D
BTo Workbook 2 Coumn F
CTo Workbook 2 Coumn O
Also add headings to the Columns in workbook 2
D=Name
F=Amount
O=Date
And last populate some columns with text all the way down tothe last row that contains text in workbook 2. A = Y
B= 5
C= Pass
View 3 Replies
View Related
Nov 16, 2013
I want to copy certain columns from Sheet6 to Sheet1. For instance, Column G in Sheet6 would be copied to Column A in Sheet1. I have over 20 columns that need to be copied. There are over 5,000 rows in Sheet6. Thiomething that I have to do daily.
View 2 Replies
View Related
Jan 24, 2014
I have three columns - as listed below. I need a script - That compares "result 1" and result 2. If there are data in the "result 1" column then input that into the "City" column. Else input "result 2" data (if any exist), else leave blank.
"Result 1" always has priority over over "Result 2".
City
result 1
result 2
San Fran
San Fran
New York
Tampa
View 4 Replies
View Related
Aug 20, 2008
I have a file that has various numbers of columns, some of which i need, some of which i do not. Under these columns, there are various rows, sometimes 50, sometimes 400. I need to write something that copies only the specific columns I need and pastes it into a new worksheet.
For example, in the data file there is are columns property type, loan balance current, and amortization type. I need them to be copied with the information in the rows below them to a new worksheet, to eliminate the unnecessary information in the data file.
View 9 Replies
View Related
Dec 17, 2012
I need to take a list of about 1,500 rows of data (single column) and convert it into columns of 9 entries. I know that I can select nine rows and then paste using transpose... But any way of "automating" the process?
So
1
2
3
4
5
6
7
8
9
ETC
ETC
becomes 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 etc....
View 5 Replies
View Related
Nov 27, 2013
Im using this formula in a spreedsheet =(B$3*0,5)+(B$3+(B$4*0,5))+(B$3+B$4+(B$5*0,5))+(B$3+B$4+B$5+(B$6*0,5))+(SUM(B$7:B$28)*4)
What i want is to be able to drag this down for multiple rows but i want the formula above to only change columns i.e.
Next row gets C$3 and so on.
View 3 Replies
View Related
Jul 10, 2014
So, I have a document 25 pages long. It has 26 sections (1-26) and each section has up to 10 (.1 - .10) sub sections which contain 16 (a-p) sub-sub-sections. There are 6 columns of varying widths. Some cells, in a row, merge into adjacent cells.
I would like to break this document into 26 documents - one for each section - so that I may recombine and regroup them in the future incorporating a few modifications. Whenever I copy and paste to a new Workbook, I loose the formatting of column widths. Resetting these alone takes lots of time.
I have come up with is to duplicate the entire document and delete everything above and below the section I would like to save. Then repeat for next section. There's got to be a better way?
View 10 Replies
View Related
Oct 14, 2008
I have a spreadsheet with two tabs (Customer Survey Data) and (Customer Rollup). The data starts on line 5 (headers in rows 1 thru 4) and is found in columns B thru J. In column B I have dates with no blanks. In column C I have customer names - no blanks. In column J is are the customer comments - not all customers made a comment so there are blanks.
I created a macro that autofilters the data to show only those entries within a specific date range (dictated by dates found in L1 and L2 - begin and end dates respectively) by column B (no problem with this). The macro also auto removes all rows where there are no comments (column J = no blanks). This leaves me with only those surveys recieved within a specied date range that have comments.
Here is my problem: the Customer Rollup sheet is the finalized report. I need to copy only the customer names and their comments into the finalized report. The following is the macro I put in which only half works and I have no idea why. It copies the names fine...but then only copies the first comment.
Oh yea...I don't want to copy the WHOLE column...only from the first visible row down to the last. Btw...I put a lot of notes for myself...I capitalized the notes where I think the problem is...Any suggestions on how to fix it or why it just doesnt seem to work like it should?
View 5 Replies
View Related
May 22, 2012
I am trying to copy two columns to a different sheet in vba code. I will ultimately need to do this multiple times but I think I have the iteration part ok, it's just the copy part that seems to be wrong.
Code:
Worksheets("Lookup").Select
Range(n & lngRevStart & ":" & n + 1 & lngEndRow).Select
Selection.Copy
Worksheets(strDeptSheet).Select
Range(Columns(o), Rows(m)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
The original code I used for this works great on one column but I knew the letter column each time I used it. Now I am trying to get two columns at a time and will be iterating through several based on user selections. I figure that will be faster to do both columns as the two columns are side by side.
I am getting an error on the PasteSpectial line "Run-time error '1004' PasteSpecail method of Range class failed.
I'm not sure where to go with this but I am assuming I have a problem with the Range selection lines on either the source or destination or both. The support is just for Macro errors.
View 1 Replies
View Related
Jul 17, 2014
I have an employee schedule that is a whole year. I'm trying to extract only the next two weeks worth of columns. I'll schedule it to run once a day. Some information... The calender has the dates listed in Row 2 on Sheet called Daily Schedule The 2 week calendar would be on sheet 7 called Two Week Calendar There are only weekdays on sheet 1 Daily Schedule so if there is nothing matching today's date just stop and do nothing I would like it to save the whole file when complete
I have written some of the simplest code but I don't know how to select by today's date then copy that column plus 9 to the right.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:Schedule.xlsx")
[Code]....
View 2 Replies
View Related
Nov 13, 2006
If I have formulas in A1, A5, A19, and A36, highlight them with goto, how do I move them into B1, B5, B19, and B36? Copying and pasting puts them in B1, B2, B3, and B4.
View 9 Replies
View Related
Mar 16, 2009
to copying a formula from one column to the next adjacent column and also incrementing the 2 numeric values inside the formula. Please see attached sample.
One value increments with each new column whereas the other increments with each new row. Please see attached sample.
And in the last row there is the average formula which must copy the new column and average the new column.
I tried to create a Macro in attached but it did not work with formulas.
View 8 Replies
View Related
Jun 20, 2006
i want to copy data from one excel workbook to other (or ever between diff sheet wud do ) on basis of search on a complete column by picking data one by one from other column & on finding the exact match copy 4 columns form one workbook(or sheet) to other. this has to be done 4 a bulk of data (1000+) so manually is next to impossible.
View 2 Replies
View Related
Feb 7, 2007
I export data from another programme into excel and I wish to write a macro to copy the contents of columns G, H, I and J and paste it into columns A, B, C, D but starting at row 4. I then carry out various formatting tasks. I have written a macro that works for the copy and paste and the formatting but it only works when the data is the same length. The length of the data exported varies each time and so I am looking for some code that stops at a blank cell and copies the range above. I believe this uses xlup or xldown.
View 8 Replies
View Related
Dec 4, 2013
I have two columns D and E that contain dates and values. Column D has the dates and Column E contains the corresponding values.
I need a macro to take all of the dates in column D (it is pulled from a different macro so the length of the two columns varies every time but starts at D2) and paste the first value in W1, the second value in AE1 , and so on (every eight cells).
I need to do the exact same thing with Column E except start at Y1, then AG1, etc (still every eight cells).
I will try to post an example later.
View 2 Replies
View Related
Jun 21, 2014
I am trying to copy several columns into another sheet within the same workbook.
I want to copy Columns 1,2, 15, 19, 23, 40.
I don't know how to work with arrays.
View 4 Replies
View Related
Oct 8, 2009
I need a formula. Probably Vlookup. I'm having a LOT of trouble trying to figure out how to "phrase" the formula so let me explain what it is I'm trying to do. If a cell in Sheet 1 Column AS and a cell in Sheet 2 Column B match exactly, I want the cell in Sheet 2 Column C that is in the same row as the matched cell in Sheet 2 Column B to be copied into Sheet 1 Column BB in the same row as the matched cell in Sheet 1 Column AS.
For example, if cells in S1 Column AS and S2 Column B both have the name "LOS ANGELES" then "KLAX" will be copied from cell in S2 Column C (in same row as "LOS ANGELES") to S1 Column BB (in same row as "LOS ANGELES").
View 3 Replies
View Related
Dec 19, 2012
I am working on an excel spreadsheet that has multiple "sheets" to it. On the first sheet I have multiple columns worth of info and on the second sheet I am trying to copy some of the info from the first sheet.
For example:
Sheet 1, Column A2 has the number 1 in it.
On sheet 2 I have entered the command "=SHEET1A2" and it copies the info as I had hoped.
However, here is where my issue comes into place.
When I sort Column A on sheet 1 numerically, my answer on sheet 2 changes to whatever the new number is in box A2 on sheet 1.
Is there a way I can copy the info so that it changes as I sort the column numerically or alphabetically?
View 1 Replies
View Related
Feb 11, 2013
I have the following code which works but only fills down one row and I want it to fill down to the last row but I cannot work it out.
Dim Col As Range
For Each Col In Range("L:Q").Columns
With Cells(Rows.Count, Col.Column).End(xlUp)
.Offset(1).Formula = .Formula
.Resize(2).FillDown
End With
Next
How to get it to copy down to the last cell in all the columns.
HKDDSHH
31-Jan-13
HUS
8
8
8
0
-896.469
637.037
-636.555
-895.987
January
2013
Comp
-112.059
Thursday
Midweek
[Code] ........
View 5 Replies
View Related
