How to copy specific columns until end of data

I'm trying to import data from 2 workbooks into a summary (combination)
workbook.  The trouble I'm having, is I want to import only certain
columns and have excel know to stop at the last row with data.

For example, from Workbook1 I want to import columns A, B, C, F,  and G
(from Sheet2).  I want it to copy up to the last row containing any
data--right now that's row 1046, but next month it could be row 1267.

In workbook2 I'm fine with it copying the CurrentRegion--all columns
are important.  BUT, it doesn't include the title/category heads in the
first row (Salesperson, ID#, Product, etc.)...anyone know how to
specify whether or not to include the title header?



Here's what I'm using so far to just copy the CurrentRegion of each
worksheet...

Sub ImportData()

Set Rng1 = Workbooks("Workbook1.xls"). _
    Sheets("Sheet2").Range("A1").CurrentRegion
Set Rng2 = Workbooks("Workbook2.xls"). _
    Sheets("Production Totals").Range("A1").CurrentRegion
Set Rng3 = Workbooks("Combine.xls"). _
    Sheets("Combination").Range("A1")

Rng1.Copy
Rng3.Activate
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
        :=False, Transpose:=False
Rng2.Copy
Rng3.Activate
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
        :=False, Transpose:=False

End Sub

0
Mattg6 (6)
10/24/2006 4:54:04 PM
excel 39879 articles. 2 followers. Follow

1 Replies
649 Views

Similar Articles

[PageSpeed] 19

Start from the bottom and work your way up to find the last used row, then 
define your range based on wherever you want to start from.  Take a look at 
this example syntax and have a try with that, but post back if you get stuck:-

    'Define the last row using End Up
    LastRw = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    LastRw = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    'Last cell plus one to bottom of sheet
    Set rng = ActiveSheet.Range(Cells(LastRw + 1, "A"), Cells(Rows.Count, 
"A"))

    'First cell to last cell with data
    Set rng = ActiveSheet.Range(Cells(1, "A"), Cells(LastRw, "A"))

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"Matt G." wrote:

> I'm trying to import data from 2 workbooks into a summary (combination)
> workbook.  The trouble I'm having, is I want to import only certain
> columns and have excel know to stop at the last row with data.
> 
> For example, from Workbook1 I want to import columns A, B, C, F,  and G
> (from Sheet2).  I want it to copy up to the last row containing any
> data--right now that's row 1046, but next month it could be row 1267.
> 
> In workbook2 I'm fine with it copying the CurrentRegion--all columns
> are important.  BUT, it doesn't include the title/category heads in the
> first row (Salesperson, ID#, Product, etc.)...anyone know how to
> specify whether or not to include the title header?
> 
> 
> 
> Here's what I'm using so far to just copy the CurrentRegion of each
> worksheet...
> 
> Sub ImportData()
> 
> Set Rng1 = Workbooks("Workbook1.xls"). _
>     Sheets("Sheet2").Range("A1").CurrentRegion
> Set Rng2 = Workbooks("Workbook2.xls"). _
>     Sheets("Production Totals").Range("A1").CurrentRegion
> Set Rng3 = Workbooks("Combine.xls"). _
>     Sheets("Combination").Range("A1")
> 
> Rng1.Copy
> Rng3.Activate
>    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
>         :=False, Transpose:=False
> Rng2.Copy
> Rng3.Activate
>    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
>         :=False, Transpose:=False
> 
> End Sub
> 
> 
0
ken.wright (2489)
10/26/2006 7:05:01 AM
Reply:

Similar Artilces:

collect data from mail merge
If I create a mail merge in Word with some blank space for data entry, can I collect the feedbacks from returned word file, and consolidate them in excel ? Can you give a little more information about what you are trying to do. Excel is a pretty powerful tool; almost certainly you can do what you want. Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "data collection" wrote: > If I create a mail merge in Word with some blank space for data entry, can I > collect the feedbacks from returned wo...

DVD Copy Pro for Mac
[b]DVD Copy Pro for Mac[/b] is a DVD copy software that can support D9 to D5 compression. With simple operation, it can save DVD in four different ways including directly burning to DVD disc, saving as DVD folder, copying into DVD media format that can be played by DVD Player directly, and converting as ISO file for easy movie management. Now this cool Mac DVD Copy also supports the latest released Snow Leopard. You can download [b][url=http://www.dvd-copy-mac.net/download/dvd-copy-pro-mac.zip]DVD Copy Pro for Mac[/url][/b] freely right now. [b]More information:[/b][quote]http://www.dvd-...

Import/copy calendar in Outlook 2003
I got a new computer and installed Office 2003, and wanted to move my calendar to the new PC. I created a new data file called Personal Folders from Old PC. I wanted to copy my calendar OVER my current one in Personal Folders, but don't see a way to do so. I can copy my old calendar into the Pesonal Folders, but can't delete the new, empty Calendar. So I have Calendar, and Calendar 1 under my Personal Folders. Any way to just import/copy my reminders/appointments into my new calendar? Thanks for any advice. Ck G'Day Cathy, 1. Use the Folder List 2. Click on the OLD Cal...

Splitting a secure database
Hi I have built a secure database, using the work group method, which has worked fine as a single database. However, I am now required to split it into a front end and a back end. I used the Database Splitter in the Tools menu to create the back end file. Now, I can open the back end without being asked for a password. It has not carried across the security stuff coded in the original database. How do I rectify this? Stapes On 24 Aug, 11:07, Stapes <steve.sta...@gmail.com> wrote: > Hi > > I have built a secure database, using the work group method, which has > worked fine...

How to classify closed-end bond funds correctly ?, Why is manual asset allocation of mutual fund always cleared?
I'm using Money 2005. I have some closed-end bond funds that are traded on the NYSE. For the purpose of correctly classifying them in my asset allocation, how can I get them to NOT be classified as mid-cap stocks? Also in the allocation, these should show up as "debt", not 100% "equity". I'm aware of the trick of creating a new investment with the right class, converting all of the existing transactions to the new investment, and then deleting the old investment. However, even as a "mutual fund" I cannot specify the correct asset allocation because th...

Copy info from one workbook to another?
I have a problem I have a workbook with the function i want like a discribe here. The problem is i want to split the workbook but i don`t know how. Is it possible to have a main workbook with information from other workbooks at each worksheet? I have 20 workbooks with information. I would like to have a main workbook with on sheet1 every row from the 20workbooks with category1, on sheet2 every row from the 20workbooks with category2, etc, etc. The information in the 20workbooks in always only on sheet. The Colum in which the data is has no fix end. I go to fill in the rows trough a...

Cant se some columns! Why?
I have a spreadsheet that displays COL A and them col BL-BM-BN etc How do I get to see the cols B to BK I am working with EXCEL 2007 Thanks Either columns B through BK are hidden (most likely), or the Window is frozen and you've scrolled way over to the right, bringing column BL next to A. Assuming they're just hidden, Click on the 2 that identifies row 2 so that the entire row is selected, then On the [HOME] tab, in the CELLS group, pull down the "Format" list and choose Hide and Unhide and click the UNHIDE COLUMNS option. If its a Frozen window thin...

Secondary Copy
Hi, My customer using Great Plains modified report(Primary Copy). Now he wants new report something similar to the modified GP report with very few changes. I tried to copy the modified report and planned to use us a secondary copy. But the report has temp tables, so I am unable to make secondary copy. Can some one help me in how to make a new report something similar to the modified report, where to launch it. Whether it is possible throught VBA & Modifier? Regards Selvakumar.S A report with a temporary table can only be a primary copy. This is because only a primary copy can b...

local copy of mail
How do you store a copy of all your mail on you local computer when using Exchange? You setup an OST/Offline Folders file. Check with your mail admin if they support that configuration. "Brandon Baker" <brandon@discussions.microsoft.com> wrote in message news:epNKuJ19FHA.4004@TK2MSFTNGP14.phx.gbl... > How do you store a copy of all your mail on you local computer when using > Exchange? > ...

Calculate max value in specific range
Hi all, I've got a table with specific date values (start & end dates) like this: 3/23/00 Start 3/26/00 End etc. note: the length of days will vary from time tot time. -- I also have a table with all date values and corresponding numbers, like this: Column: A B Row: 1 3/23/00 1101.16 2 3/24/00 1106.16 3 3/25/00 1143.07 (=MAX) 4 3/26/00 1137.54 -- How can I calculate the max value from all those corresponding numbers between a Start- & End date? (1143.07 in this case) I've already tried the Vlookup and Max function but that only refers to 1 value (as...

Columns won't Hide!
I'm trying to hide columns in a ss and I get the message, "Cannot Shift Objects off Sheet.". I'm able to hide some columns but Excel seems to reach a limit. Help! You may have something in columns that you don't know about. "Tom" <thomas.hamilton@med.va.gov> wrote in message news:051e01c38d97$7b458e80$a001280a@phx.gbl... > I'm trying to hide columns in a ss and I get the > message, "Cannot Shift Objects off Sheet.". I'm able to > hide some columns but Excel seems to reach a limit. > > Help! >-----Original Mess...

Outlook 2000 SP3
How can I copy my mail and calendar to CD so that I can take my received and sent mail history to a new computer with Outlook installed? Thanks. >-----Original Message----- >How can I copy my mail and calendar to CD so that I can >take my received and sent mail history to a new computer >with Outlook installed? Thanks. >. > All your data saves in C:\Documents and Settings\Username\Local Settings\Application Data\Microsoft\Outlook\archive.pst and C:\Documents and Settings\Evgeni\Local Settings\Application Data\Microsoft\Outlook\outlook.pst You can either install instan...

Text to column and paste special transpose
Hi All, I have 1 table like this item_number qty ref 00010-001 3 U34,U36,U43 00013-001 2 U16,U21 12505-001 3 U32,U33,U42 12512-001 3 U5,U6,U27 and 1 want it to become like this item_number qty ref 00010-001 3 U34 00010-001 3 U36 00010-001 3 U43 00013-001 2 U16 00013-001 2 U21 12505-001 3 U32 12505-001 3 U33 12505-001 3 U42 12512-001 3 U5 12512-001 3 U6 12512-001 3 U27 Anybody can help me to program it in macro.. usually i do it manually and have to redo again do to careless. After using Text to Columns on Column C.......comma delimited.........Run the Reorganize macro. Sub ReOrganize...

Error message when hiding columns
Has anyone seen this error message before when trying to hide columns? "Cannot shift objects off sheet." -- valenj ------------------------------------------------------------------------ valenj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24087 View this thread: http://www.excelforum.com/showthread.php?threadid=376912 maybe this'll help: XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769 valenj wrote: > > Has anyone seen this error message before whe...

Copy picture ossice wizard
I have project 2007 standard edition, but the button is not working at all? Any suggestions Hi, The wizard is IMHO useless. Go to Roport, Copy Picture and do without the Wizard what millions have done before the Wizard existed. HTH -- Jan De Messemaeker Microsoft Project Most Valuable Professional +32 495 300 620 For availability check: http://users.online.be/prom-ade/Calendar.pdf "Enrique" <Enrique@discussions.microsoft.com> wrote in message news:72B5AA6C-67AE-468D-B6AE-FF16F52E57BD@microsoft.com... >I have project 2007 standard edition, but the bu...

Log data in a shared workbook
Hi all Is it possible to have a spreadsheet which logs data from anothe without over writing each other's data, even though several colleague are inputting and saving in this shared sheet at different times. Eg. Sheet 1 has various data Sheet 2 - A1,2,3, etc = first save Sheet 3 - B1,2,3, etc = second save Etc. ...for the day Cheers Joe -- Message posted from http://www.ExcelForum.com Joey Would you consider writing to a CSV file? You could use VBA's text file functions to write directly to the file, but the file would still open in Excel. One advantage would be that...

Copy and paste versus copy and insert copied cells
What is the difference between these commands? Try them, it is pretty easy to see the difference -- Regards, Peo Sjoblom "Alana" <Alana@discussions.microsoft.com> wrote in message news:879FED29-5541-41B0-BB2C-7108D42ED593@microsoft.com... > What is the difference between these commands? ...

subtotal copy and paste
Hi I have used the subtotal option in Excel and this is fine. I have also collasped the list so only the subtotal for each category is visible. However I want to copy and paste this list (just the subtotal) into another sheet When I do this it expands and shows all the results. Is it possible to just copy the visible bit. I have looked in paste special but no luck Cheers Hager On Jan 21, 6:38=A0am, Hager <Ha...@discussions.microsoft.com> wrote: > Hi > > I have used the subtotal option in Excel and this is fine. I have also > collasped the list so only the...

3D Column Chart #2
How do I create a 3D column chart and have three axis? ...

Copy above fails one way, copy below fails another way
I have a table of blood pressure readings that looks something like this: A B C D E 11 12 Averages: 134 83 74 13 R# Date Sys Dia Pul 14 4 8/17/08 138 87 72 15 3 8/15/08 130 84 76 16 2 8/12/08 137 83 70 17 1 8/11/08 129 79 76 Cell A14 contains =A15+1 Cell C12 contains =AVERAGE(C14:C17) Cell D12 contains =AVERAGE(D14:D17) Cell E12 contains =AVERAGE(E14:E17) Every time I take a new reading, I want to add a row to the top of the table, between row 13 (the header) and row 14 (the previous top row). I want to push a...

column headings in numbers vs letters
For some reason all my excel files now display columns as numbers rather than letters. How can I change back to letters? Why are all my old files also now reading in numbered columns? This is a setting of Excel, not of your files. Tools>Options>General, uncheck R1C1 Reference style -- Kind Regards, Niek Otten Microsoft MVP - Excel "garyflood" <garyflood@discussions.microsoft.com> wrote in message news:6870D172-C11D-4FF9-A7D8-48BE89149DF8@microsoft.com... > For some reason all my excel files now display columns as numbers rather > than > letters. How...

Business Portal "Copy to Excel" not working in Query pages
Hi, I am using Business Portal 4.0. When browsing through one of the queries pages in Business Portal, i clicked "Copy to Excel" icon. I got an error saying "The Office Web Components (OWC) must be installed to copy results to Excel." at the bottom. I am using Office 2007 on my machine. I came to know that Microsoft Office Web Components will no longer be shipping in Microsoft Office. I tried installing Office 2003 Add-in: Office Web Components from http://www.microsoft.com/downloads/details.aspx?familyid=7287252C-402E-4F72-97A5-E0FD290D4B76&displaylang=en a...

line column Pivot Charts
Access 2003 does not support combined Line/Column Charts, but Excel does. Has the capability been added to Access 2007? Access does support a series displayed as a bars and another series as line. -- Duane Hookom MS Access MVP Help me support UCP http://www.access.hookom.net/UCP/Default.htm "John" <John@discussions.microsoft.com> wrote in message news:A0DD25F6-D99A-44C3-812B-416776BED8AF@microsoft.com... > Access 2003 does not support combined Line/Column Charts, but Excel does. > Has > the capability been added to Access 2007? ...

Adding a column...very bizarre behavior
I have an MPP file given to me by one of my users. When she tries to add a column to the Gantt Chart view, we get a little screen flicker, then several new columns appear on the view. I don't know what to tell her. It doesn't do this on any other view, and it only does this in this particular MPP. If I open another project file, it doesn't behave this way. Is it possible that this file has just been corrupted somehow? This is a multi-part message in MIME format. ------=_NextPart_000_002F_01CAE16B.B28F3380 Content-Type: text/plain; charset="Windows-1252...

Copying graphs
Hi Is it possible to range value a graph ie I would like to copy the graph to another workbook, show all the information but without the links? Thanks You could copy the chart as a picture, then paste it wherever you want. Select the chart, hold Shift while clicking on the Edit menu, Choose Copy Picture, and select the On Screen and As Picture options, then go to the other workbook/sheet and Paste. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Dixie" <Dixie@discussions.micr...