Copy contents of cells if cell contains information

I have a column of cells, say A1:A30 in a few different spreadsheets. Some
of these spreadsheets contain information within these cells (not formulae,
but just typed info) and some don't contain information.

Now, in a new spreadsheet, I would like to copy the contents of these cells
(columns) if there is information in them.

So for example, in spreadsheet 1, i have information, speadsheet 2 i don't,
spreadsheet 3 I do, i would like my information to be copied into a new cell
as follows:-

information 1
information 2

Any advice?

Thanks


0
joebloggs1 (11)
5/13/2004 6:09:58 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
669 Views

Similar Articles

[PageSpeed] 43

Hi,

Test this on sample (or backed-up) workbooks.
just paste in as your "Sheet1" macro.

Modified slightly from "BrianB"'s post:
 "BrianB >" <<BrianB.15dmdj@excelforum-nospam.com> Sent:  
4/27/2004 7:52:33 AM 

jeff


'==============================================
'- Generic code for transferring data from
'- one or more workbooks in a folder to a master sheet
'-
'- workbooks must be the only ones in the folder
'- run this code from the master book
'-
'----------------------------------------------
Dim ToBook As String
Dim ToSheet As Worksheet
Dim NumColumns As Integer
Dim ToRow As Long
Dim FromBook As String
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim LastRow As Long
'-
'----------------
Sub NEW_MASTER()
'----------------
Application.Calculation = xlCalculationManual
ChDrive ActiveWorkbook.Path
ChDir ActiveWorkbook.Path
ToBook = ActiveWorkbook.Name
Set ToSheet = ActiveWorkbook.Worksheets(1)
NumColumns = ToSheet.Range("A1").End(xlToRight).Column
ToRow = ToSheet.Range("A65536").End(xlUp).Row
'- clear master
If ToRow <> 1 Then
    ToSheet.Range(Cells(2, 1), Cells(ToRow, 
NumColumns)).ClearContents
End If
ToRow = 2
'- main loop
FromBook = Dir("*.xls")
While FromBook <> ""
If FromBook <> ToBook Then
    Application.StatusBar = FromBook
    Transfer_data
End If
FromBook = Dir
Wend
'-- close
MsgBox ("Done.")
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
End Sub
'-------------------------------------------------

Sub Transfer_data()
Workbooks.Open Filename:=FromBook
For Each FromSheet In Workbooks(FromBook).Worksheets
LastRow = FromSheet.Range("A65536").End(xlUp).Row
'- copy paste
a1$ = FromSheet.Cells(2, 1).Address
b1$ = FromSheet.Cells(LastRow, NumColumns).Address
FromSheet.Range(a1$ & ":" & b1$).Copy _
    Destination:=ToSheet.Range("A" & ToRow)
'- set next ToRow
ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1
Next
Workbooks(FromBook).Close savechanges:=False
End Sub
'==== EOP ======================================

>-----Original Message-----
>I have a column of cells, say A1:A30 in a few different 
spreadsheets. Some
>of these spreadsheets contain information within these 
cells (not formulae,
>but just typed info) and some don't contain information.
>
>Now, in a new spreadsheet, I would like to copy the 
contents of these cells
>(columns) if there is information in them.
>
>So for example, in spreadsheet 1, i have information, 
speadsheet 2 i don't,
>spreadsheet 3 I do, i would like my information to be 
copied into a new cell
>as follows:-
>
>information 1
>information 2
>
>Any advice?
>
>Thanks
>
>
>.
>
0
anonymous (74722)
5/13/2004 7:24:04 PM
Reply:

Similar Artilces:

Sort
I am trying to sort my sales table, it doen't seem to work for me. A comment comes up: this operation requires the merged cells to be idendically sized. Can you help? Thanks Easiest way is to *unmerge* the cells, and then use "Center Across Selection" instead. <Format> <Cells> <Alignment> tab, And expand the "Horizontal" window, and choose, "Center Across Selection" -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ==============================...

deleting cells from a calculation...
Hi If I had 32 cells, which represented control values, and I wanted to execute some QC on them if individuals were too far from the mean value, how could I calculate the average value in one cell and yet exclude cells where the values were too different? I hope I have explained this well enough! If not I will try again following feed back. Best wishes to you Andrew J Scotland Ajohns, Take a look at the function TRIMMEAN. (It migth be a function from the Analysis Toolpak) -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * <...

Moving an active cell
I need a snippet of code that will (when you use ctrl-d) look at the active cell, move the cursor down 9 cells (same column) and make that cell the active cell, no matter where you start on the spreadsheet. Any ideas? Thanks to everyone. -- Howard ActiveCell.Offset(8).Select Hope this helps. Otto "Howard" <Howard@discussions.microsoft.com> wrote in message news:E4AEBCBE-AB3A-456C-81D0-2478A113B5FE@microsoft.com... > I need a snippet of code that will (when you use ctrl-d) look at the > active > cell, move the cursor down 9 cells (same column) and ...

Too many formatted cells
When i try to open a saved excel workbook it will not let me open it.It just says too many formatted cells. Maybe... XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=213904 A few people have said that OpenOffice.Org has been able to open the file. Then they clean it up and save it there. Then excel can open that cleaned up version. http://www.openoffice.org, a 60-65 meg download or a CD XROCKYISBACKX wrote: > > When i try to open a saved excel workbook it will not let me open it.It just > says too many formatted cells. -- Da...

Stopping cell reference updates
Simple question: I have one table in one worksheet with 100 rows of data, 4 columns. Worksheet 2 has a table with 2 columns with 1000 rows, all referencing two of the columns in the first table. So essentially I have one table that's a subset of the larger table. However, when I insert rows in the first table, the second table's references change too, which is undesired. If cell B3 in table 2 points to cell B3 in table 1, and I insert a row above row 3, then cell B3 in both tables become B4.. I'd rather have B3 in the second table still point to B3, and B4 in the second table ...

Conditional Formating for Formula Cells
Hi to all fantastic Excel champions, How do I get conditional formatting to change the format of any cell that contains a formula? Kind regards -- Andrew 'The Ox' Heath Brisbane, QLD Australia Hi Andrew, You can use a user defined function (see my formula.htm page): But it must be in the same workbook not your personal.xls to work in Conditional Formatting, though there is no harm in having it in both places. Function HasFormula(cell) HasFormula = cell.HasFormula End Function =hasformula(a1) use the above in your conditional formatting http://www.mvps.org/dmcritchie/exc...

Changing cell colors
I am trying to change a cell color to either red, yellow or green if the data in the cell is greater than lets say 90 it would turn red. Less than 90 but greater than 80 yellow and less than 80 turn to red. Without using conditional formatting. Unless there is a way to conditional format the cell and have the conditional formatting move with the sorting of high to low. I can do the conditional formatting just fine but when I sort the conditional formatting stays with the cell it doesn't move with the sorting. Hi, That process is very simple. Microsoft Office 2007 has mad...

Changing colors of cells to establish value.
Hello, Can I program my cells to establish a color of the cell and/or the color of the font according to the value of the cell. ie: If I have a formula that has a product being sold at 5000.00 with a cost of 4000.00, in another 5000.00 sale with a 4500.00 cost, and in another a 5000.00 sale with a 6000.00 cost. Could I show these with the 1000.00 profit showing in one color automatically, if the cost changed to the second, could I show the 500.00 profit as another. If it changed to the 1000.00 loss, could it change to another color? thanks, Mark. You want CONDITIONAL FORMATING....

addition, subtraction in a single cell formula
I need help updating a single cell formula. G4 has a formula of *=12500-A7 I would like to update this so that A7, A8, A9 are added together then subtracted from 12500. Any help would be greatly appreciated. mePenny =12500-SUM(A7:A9) OR =12500 - A7 - A8 - A9 OR 12500 - (A7 + A8 + A9) best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "mePenny" <mePenny@discussions.microsoft.com> wrote in message news:47A0D90D-164B-4821-9315-887080F6B019@microsoft.com... > I need help updating a single cell formula. > > G...

Cell formatting from Access
When I export an Access report to Excel, one of the fields, which is a time field, e.g., 15:10.9 in Access becomes "0.63205" in Excel. Some kind of decimal formatted cell. How can I export to Excel without this format change? Thanks, Rusty Rusty, After the import is done, simply format those cells as time, with the custom mm:ss.s format - though you will have to first divide all the values by 60. The 15 minutes and 10 seconds from Access is becoming 15 hours and 10 minutes, since Excel requires the entry to be 0:15:10.9 to treat the value as minutes and seconds. HTH, Ber...

Copy data into next avaiable cell #2
Thanks Katie I will give it a go and see what happens. Chi -- Chise ----------------------------------------------------------------------- Chisel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=935 View this thread: http://www.excelforum.com/showthread.php?threadid=26840 ...

when i write in cell 2-2 or 1-2 whatever it is change to date whi.
Whenever i enter 2-1 or 3-1 etc into a cell, Excel automatically changes it to a date, which i don't want to change to date i want it as 2-2 or 2-1wahtever .when i simply want to display 2-1 etc. However if i change the formatting in that cell back to general, it interprets that as a 5 digit number, e.g. 38408. How do i turn this auto-edit feature off? Format the cell as text before you enter the data. Or precede the data with a single quote; it will not show but it will cause the data to be interpreted as text. -- Kind Regards, Niek Otten Microsoft MVP - Excel "haniht&qu...

Past Link then copy
I am doing a log sheet which calculates usage of gas and steam production on a daily basis. The readings are taken daily and the usage/production is calculated. I want to automatically transfer the previous day's readings to the next day's sheet so that I don't have to retype the readings twice. I have done a "paste link" to the next worksheet and would like to find an easy way to copy the sheets so that the "paste link' will refer to the last days sheet rather than refer all the way back to the original sheet. I there a way to copy sheets which will increme...

Copy range based on input
Based on the value of one input cell, I want to copy a certain other range of cells, pasting them into another area on the worksheet. When I change the value in the input cell, I want to copy a different range of cells into the same target range. I have several different values of the input cell that I would like to use for defining the copy range. Ideally, I would pick the value of the input cell from a list and use that to determine the copy range. I've been doing manual copy/paste and am tiring of it. Please help. If desired, send your file to my address below. I wil...

Cell Format Not Available
We have an old spreadsheet that was originally created by Excel 2000. It is a complex workbook with many formulas. It does not have locked cells, nor is it protected. The problem, is that a bunch of cells will not allow for formatting. We can't figure out why. If one right clicks while on the cell, the short-cut menu appears, but if you click on "Format Cells" nothing happens (it doesn't take you to the next menu). The same happens if you go through the toolbar and try to click FORMAT then CELLS. I've searched a couple of databases and I can't find any reference ...

Why does Custom Format not control Cell Display?
XL 2003 Have a cell which shows in the formula bar: 06/10/2005 10:10:32 AM In the worksheet (same cell but unselected) the display is: 38513.4239814815 No matter what I do with cell formating i.e. general to text to custom (BTW which is mm/dd/yyyy h:mm) the cell displays 38513.4239814815 UNLESS I select the cell - then it displays 06/10/2005 10:10:32 AM? When I de-select the cell it reverts back to 38513.4239814815 I thought that I knew better! What is the very obvious thing that I am missing? Dennis Maybe you're looking at formulas... Tools|options|view tab Uncheck Form...

Serious sorting problems with cells containing references
Hi, I'm running into some serious bugs when I try to sort some cells on a sheet. If all the cells contain simple text or numbers, all is fine. But if any of the cells contains a reference to another cell (outside of the sort range), the sort is messed up badly. On some occasions the reference in question has been deleted and the cell's previous contents restored. The range in question is three columns wide and about 12 rows long. It has a header row. Everything is very simple. Thanks, p. -- MS Office '03 Basic on Win XP Paul, Select your cells with the references, and run the ...

recalculation not recalculating some cells containing user functio
I have the following situation: 1) workbook contains User Function macros in some cells. 2) #VALUE appears in some of the cells 3) if i select the cell and use "F2" to edit then the value is correctly calculated 4) pressing "F9" will not cause the cells to recalculate nor will 5) running a macro containing the following Application.CalculateFullRebuild what must i do to force the cells to recalculate without having to physically perform #3 above on each cell. thanks, -- jwm application.volatile at the start of the UDF will make the fu...

Excel VBA: How to select only rows containing data?
Hi all, I'm writing a macro to automate formatting of a commonly used report. The number of rows containing data will vary on any given day. I'd like to select only the rows that contain data, set the heights for those rows to 63.75, and leave the other rows untouched. Does anyone have a suggestion on how to select only rows containing data? I'm running Excel 2000 on Windows 2000 Best regards, Russell Somers try Sub setrowht() For Each c In Selection If c <> "" Then c.RowHeight = 63.75 Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "Russel...

Excel 2003 -- copy/paste error
I'm using XP and Excel 2003...everything is current. I'm copying a cell's formula using ctrl-c, then pasting to many cells using ctrl-v after selecting the target cell range. Upon hitting ctrl-v to paste, I get the following error message: "Selection too large." I get 1 option: "OK". Nothing else...no help, no nothing. Very cryptic error message. Has anyone else come across this problem? If so, how did you solve the problem? Thanks! I don't think I've seen this error--I still use xl2002. You may want to give more details to see if anyo...

copy selected accounts to CRM-Test
I have CRM Test and CRM Production running on two different servers. I wish to copy some accounts, which are unique from the others by relationship type. I want to copy these to the CRM test. 1.) Is there a way I could do it in teh Web client itself. 2.) If not, where do they exist in the SQL database. I located the Account Base in the CRM Production, but do not see a column named' Relationship Type'. 3.) How do I copy the selected data in one Database to the other database. Thanks. ...

looking up a cell
I have the row() function returning one number and the column() functioning returning another number, but how do I look up the value in the cell this refers to? I know without modifying the numbers it would be the same cell, but I add something to the row() number. Regards Uffe What are you trying to do? -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31708 View this thread: http://www.excelforum.com/showthread.php?threadid=522118 One way: J3: =OFFS...

Creating two linked copies of one workbook
Hello. I would like to create two spreadsheets that are exactly the same in two different places. Essentially, I would like to use a spreadsheet on my local drive that updates a spreadsheet on a network drive that is accessible to other people. This way when I save to my local spreadsheet it automatically updates to the network document. Can this be done and how? Thanks. Hi Jim Try this You can use SaveCopyAs Copy this in the Thisworkbook module to save the file also in C:\ (change that) Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ThisWorkbook.Sa...

Can we update links automatically somehow after making a copy of a sheet with links?
Is there a way to do this that is not manual? Knowing the power of XL2K, it seemed wrong to me, somehow, that each time my supervisor makes a copy of a sheet, she has to fix the links manually. Any help would be greatly appreciated. I have a colleague who is working in this massive workbook at present, and she's daunted at all the new sheets to add that will require all this manual editing. Thank you! :oD You don't indicate exactly what you have to change, sheetnames, workbook names. Formulas or links that get changed would help. If you are copying more than one sheet that ...

how do i combine a date cell and a time cell in excel?
how do i combine the date(A1) and time(B1) to show up in one cell and have the correct format (C1). A1 B1 C1 5/25/05 13:00 5/25/05 13:00 Just add 'em up; in C1: =a1+b1, then format it appropriately (Edit > Format, on the numbers tab select Date, and scroll down til you see some formats that include both date and time). "Season" wrote: > how do i combine the date(A1) and time(B1) to show up in one cell and have > the correct format (C1). > > A1 B1 C1 > 5/25/05 13:00 5/25/05 13:00 > >...