Excel macro for copying range to another worksheet

On a monthly basis, I would like to copy the completed range (varies
from month to month) of a database (Sheet1) to a master list (Sheet3).
Once the data has been copied I intend to manually delete the entries
of Sheet1 and start anew for the new month =96 for eventual transfer to
Sheet3.

The idea is to copy each month=92s data at the bottom of the previous
months=92 (Sheet3).
I followed Excel=92s record macro command but the macro I ended up with
is not capable of placing the new data at the bottom of the existing
one; it simply keeps overwriting the previous entry.

Unfortunately, I don=92t know enough VBA to tweak the code that the
record macro command produced. It appears that the first part,
selecting the non-blank cells and copying into Sheet3 works OK, but I
also would like to copy the new data at the bottom of previous
entries, and the code is not doing it.

Below is the code that I=92m struggling with. Any help will be greatly
appreciated.

Dave


Sub DataTransfer()
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A22").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=3DxlValues, Operation:=3DxlNone,
SkipBlanks:=3D _
        False, Transpose:=3DFalse
End Sub
0
7/6/2008 5:11:07 AM
excel 39879 articles. 2 followers. Follow

4 Replies
471 Views

Similar Articles

[PageSpeed] 48

On Sat, 5 Jul 2008 22:11:07 -0700 (PDT), holt.david1@gmail.com wrote:


>Below is the code that I�m struggling with. Any help will be greatly
>appreciated.

Use F8 (debug stepping) to step through the code one line at a time to see how
it works. 
>
>Dave
>
>
>Sub DataTransfer()
>    Range("A2").Select
>    Range(Selection, Selection.End(xlDown)).Select
You could use the line below instead of the 2 above
        Range("A2", Range("A2").End(xlDown)).Select


The next line changes the selection and thus makes the previous 2 lines
redundant and fixes the range to end at A22, probable incorrectly in the general
case. I suspect you should delete this line. 
>    Range("A2:A22").Select


>    Range(Selection, Selection.End(xlToRight)).Select
>    Selection.Copy
>    Sheets("Sheet3").Select
>    Range("A1").Select

This line takes you to the last line of data
>    Selection.End(xlDown).Select

But this then moves to the top of the data
>    Selection.End(xlUp).Select
and this selects cell A2
>    Range("A2").Select

Delete the above 2 lines and use
      Selection.Offset(1, 0).Select

>    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
>SkipBlanks:= _

>        False, Transpose:=False
>End Sub

Thus the final code would be 

Sub DataTransfer()
    Range("A2", Range("A2").End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

-- 
Dave Mills
There are 10 type of people, those that understand binary and those that don't.
0
News11 (66)
7/6/2008 9:25:35 AM
Another one:

Option Explicit
Sub DataTransfer2()

    Dim RngToCopy As Range
    Dim DestCell As Range
    Dim LastRow As Long
    Dim LastCol As Long

    With Worksheets("Sheet1") 'or what you want
        'based on what's used in column A
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        'based on what's used in row 1 (headers are nice!)
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        Set RngToCopy = .Range("a2", .Cells(LastRow, LastCol))
    End With

    With Worksheets("Sheet3")
        'based on column A
        Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
    End With

    RngToCopy.Copy
    DestCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
    Application.CutCopyMode = False

End Sub


holt.david1@gmail.com wrote:
> 
> On a monthly basis, I would like to copy the completed range (varies
> from month to month) of a database (Sheet1) to a master list (Sheet3).
> Once the data has been copied I intend to manually delete the entries
> of Sheet1 and start anew for the new month � for eventual transfer to
> Sheet3.
> 
> The idea is to copy each month�s data at the bottom of the previous
> months� (Sheet3).
> I followed Excel�s record macro command but the macro I ended up with
> is not capable of placing the new data at the bottom of the existing
> one; it simply keeps overwriting the previous entry.
> 
> Unfortunately, I don�t know enough VBA to tweak the code that the
> record macro command produced. It appears that the first part,
> selecting the non-blank cells and copying into Sheet3 works OK, but I
> also would like to copy the new data at the bottom of previous
> entries, and the code is not doing it.
> 
> Below is the code that I�m struggling with. Any help will be greatly
> appreciated.
> 
> Dave
> 
> Sub DataTransfer()
>     Range("A2").Select
>     Range(Selection, Selection.End(xlDown)).Select
>     Range("A2:A22").Select
>     Range(Selection, Selection.End(xlToRight)).Select
>     Selection.Copy
>     Sheets("Sheet3").Select
>     Range("A1").Select
>     Selection.End(xlDown).Select
>     Selection.End(xlUp).Select
>     Range("A2").Select
>     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _
>         False, Transpose:=False
> End Sub

-- 

Dave Peterson
0
petersod (12005)
7/6/2008 12:02:18 PM
One way, withOUT selections, fired from the source sheet to copy values to 
sheet 4

Sub copyvaluestoothersht()
lr = Cells(Rows.Count, 1).End(xlUp).row
With Sheets("sheet4")
dlr = .Cells(Rows.Count, 1).End(xlUp).row + 1
        .Cells(2, "a").Resize(lr - 1).Value = _
         Cells(2, "a").Resize(lr - 1).Value
'to delete the source column
'columns(1).delete
End With
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
<holt.david1@gmail.com> wrote in message 
news:7fa28feb-e241-4b06-ae3c-6550e622b74d@m45g2000hsb.googlegroups.com...
On a monthly basis, I would like to copy the completed range (varies
from month to month) of a database (Sheet1) to a master list (Sheet3).
Once the data has been copied I intend to manually delete the entries
of Sheet1 and start anew for the new month � for eventual transfer to
Sheet3.

The idea is to copy each month�s data at the bottom of the previous
months� (Sheet3).
I followed Excel�s record macro command but the macro I ended up with
is not capable of placing the new data at the bottom of the existing
one; it simply keeps overwriting the previous entry.

Unfortunately, I don�t know enough VBA to tweak the code that the
record macro command produced. It appears that the first part,
selecting the non-blank cells and copying into Sheet3 works OK, but I
also would like to copy the new data at the bottom of previous
entries, and the code is not doing it.

Below is the code that I�m struggling with. Any help will be greatly
appreciated.

Dave


Sub DataTransfer()
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A22").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
        False, Transpose:=False
End Sub 

0
dguillett1 (2487)
7/6/2008 12:29:49 PM
On Jul 6, 5:02=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Another one:
>
> Option Explicit
> Sub DataTransfer2()
>
> =A0 =A0 Dim RngToCopy As Range
> =A0 =A0 Dim DestCell As Range
> =A0 =A0 Dim LastRow As Long
> =A0 =A0 Dim LastCol As Long
>
> =A0 =A0 With Worksheets("Sheet1") 'or what you want
> =A0 =A0 =A0 =A0 'based on what's used in column A
> =A0 =A0 =A0 =A0 LastRow =3D .Cells(.Rows.Count, "A").End(xlUp).Row
>
> =A0 =A0 =A0 =A0 'based on what's used in row 1 (headers are nice!)
> =A0 =A0 =A0 =A0 LastCol =3D .Cells(1, .Columns.Count).End(xlToLeft).Colum=
n
>
> =A0 =A0 =A0 =A0 Set RngToCopy =3D .Range("a2", .Cells(LastRow, LastCol))
> =A0 =A0 End With
>
> =A0 =A0 With Worksheets("Sheet3")
> =A0 =A0 =A0 =A0 'based on column A
> =A0 =A0 =A0 =A0 Set DestCell =3D .Cells(.Rows.Count, "A").End(xlUp).Offse=
t(1, 0)
> =A0 =A0 End With
>
> =A0 =A0 RngToCopy.Copy
> =A0 =A0 DestCell.PasteSpecial Paste:=3DxlValues, Operation:=3DxlNone, _
> =A0 =A0 =A0 =A0 SkipBlanks:=3DFalse, Transpose:=3DFalse
>
> =A0 =A0 Application.CutCopyMode =3D False
>
> End Sub
>
>
>
> holt.dav...@gmail.com wrote:
>
> > On a monthly basis, I would like to copy the completed range (varies
> > from month to month) of a database (Sheet1) to a master list (Sheet3).
> > Once the data has been copied I intend to manually delete the entries
> > of Sheet1 and start anew for the new month =96 for eventual transfer to
> > Sheet3.
>
> > The idea is to copy each month=92s data at the bottom of the previous
> > months=92 (Sheet3).
> > I followed Excel=92s record macro command but the macro I ended up with
> > is not capable of placing the new data at the bottom of the existing
> > one; it simply keeps overwriting the previous entry.
>
> > Unfortunately, I don=92t know enough VBA to tweak the code that the
> > record macro command produced. It appears that the first part,
> > selecting the non-blank cells and copying into Sheet3 works OK, but I
> > also would like to copy the new data at the bottom of previous
> > entries, and the code is not doing it.
>
> > Below is the code that I=92m struggling with. Any help will be greatly
> > appreciated.
>
> > Dave
>
> > Sub DataTransfer()
> > =A0 =A0 Range("A2").Select
> > =A0 =A0 Range(Selection, Selection.End(xlDown)).Select
> > =A0 =A0 Range("A2:A22").Select
> > =A0 =A0 Range(Selection, Selection.End(xlToRight)).Select
> > =A0 =A0 Selection.Copy
> > =A0 =A0 Sheets("Sheet3").Select
> > =A0 =A0 Range("A1").Select
> > =A0 =A0 Selection.End(xlDown).Select
> > =A0 =A0 Selection.End(xlUp).Select
> > =A0 =A0 Range("A2").Select
> > =A0 =A0 Selection.PasteSpecial Paste:=3DxlValues, Operation:=3DxlNone,
> > SkipBlanks:=3D _
> > =A0 =A0 =A0 =A0 False, Transpose:=3DFalse
> > End Sub
>
> --
>
> Dave Peterson

Appreciate all the help. It worked!
0
7/9/2008 6:07:46 AM
Reply:

Similar Artilces:

format changes when copying from one cell to another
I'm sure this is simple, but it is not clear to me... I am copying the contents of a group of cells that exist on one worksheet to another. In Sheet2, I tell cell A1 that it should equal Sheet1's A1 value. That works fine. But if the cell is blank on Sheet1, it comes up as "0" on Sheet2. I figured it was probably assuming numeric formatting, but when I right-click > format cell on both Sheet1 and Sheet2, both show up as "general", so why would Sheet2 take a blank value and populate it with "0" ?? I gather than you are putting a formula in one...

Copying Modified Invoices
We currently use SOP Short Invoice and I want to copy the report to the SOP Other Invoice in report writer. The invoice layout is totally different, and I am trying not to spend days recreating it. I could not locate any type of copy functionality from one modified invoice to another. Any ideas out there. We are on version 7.0 Zach 1.export the modified Short Invoice report as a package file. 2. open the Package file in notepad and change the Component name and Report SOP Other Invoice and save 3. import the package file Voila! HS "Zach Morgan" <ZachMorgan@discussions....

Another multiple criteria/column question
Ok, first post and pretty much a new user to Excel. I have two sheets that I am working with, trying to recall data from one to the other that meets criteria. In a nutshell: Sheet 1 contains a column of unique values (col A), cols C,D, and E are where I want to insert the formula to find data on Sheet 2. Sheet 2 contains 4 columns, A contains multiple occurrences of each value (from Sheet1, column A), each with its own timestamp in column D. On Sheet1, in column C, I want to find a value on Sheet2 in column A and return the timestamp in column D. I know I can use VLOOKUP for this. =VLO...

Copying to a disk
when I try to copy data to a disk, it often can't read the disk or it says the disk is full when there is only 1 small file on it. I also can't erase the file. I can't find a command in file to allow me to erase or delete the file or format the disk. Wha tdo I do?? Dear jqh: This is a Great Plains forum. Although someone here might be able to answer your question, you might have better luck on a MS Windows forum. Frank Hamelly MCP-GP, MCT East Coast Dynamics www.eastcoast-dynamics You throw the disk away and get a new one. You're asking for trouble copying data t...

Excluding hidden columns and rows when copying to another workbook
When I print part of a worksheet that has hidden columns and rows - the hidden columns and rows do not print. That's what I want. Now--I'd like to take that same data and copy it to another workbook excluding the formulas and hidden columns and rows so that the new file contains only the data as was printed. How can I do that? PJ Select your range including hidden rows and columns then Edit>Go To>Special>Visible cells only>OK Now do your copy/paste. Gord Dibben Excel MVP On Fri, 6 Feb 2004 10:16:07 -0800, "PJ" <anonymous@discussions.microsoft.com> wr...

Vertical Header in Excel
Is there a way to make a header in excel run down the side of the spreadsheet (vertical header)? You can freeze panes so that certain columns are always visible. Is that what you meant? If yes, then make sure the column(s) that should be visible is showing. Then select the column to the right and window|Freeze Panes (in xl2003 menus) For instance if I wanted column A to be always visible, I would make sure column A is visible to start. Then select column B and window|freeze Panes. On 05/18/2010 09:09, Erika wrote: > Is there a way to make a header in excel run do...

generate report thru pracle to excell
Recently i upgraded to ms office 2003 since then I'm not able to generate reports thru oracle unto excell (online ). But when I log in an administrator in the local system I'm able to generate the report. the same does not happen with domain users ...

Developing a robust database at one co. then using it at another c
Are there any legal implications of using the type of database listed above to perform similar functions for another company? The 2 company's are in 2 totally different industries and the new database will have to be modified to fit the requirements for the industry it is in. The database was developed at the old company. there is no such thing as a robust Jet database. Move to SQL Server if you want to build a solution that will work for the next decade. Jet is and always has been depecrated On Apr 7, 12:14=A0pm, BoaMan10 <BoaMa...@discussions.microsoft.com> w...

Excel 2003 file freezes laptop
I took a laptop away from the office and tried to open a large spreadsheet on the local C: drive. Each time I tried the laptop froze and had to be rebooted. When I connected back to the LAN at work, and opened the same file on the local C: drive I had no problems. What could cause this? If there is any corruption is there a way to decompile or something as you would in Access? Thanks, Mich ...

clearing pictures copied from websites
Hello, I posed this question a couple of years ago and someone helped by providing a macro which I unfortunately lost. I often create spreadsheets with prices I copy from pricewatch.com. I want to keep the product image but I want to get rid of the buy now button. I can't delete that row or column - the image remains. Thanks Try rightclicking on that button and hitting the delete key. Boe wrote: > > Hello, > > I posed this question a couple of years ago and someone helped by providing > a macro which I unfortunately lost. I often create spreadsheets with ...

Syntax for Re-registration of Excell
Windows 2000, Excel v8. Does anyone know the syntax for re-registration with Windows? We get a file already open error every time we launch a SS without opening the app first. Had a similiar problem with Word and fixed it with Winword /r. Thanks! Hi first you may check: http://www.contextures.com/xlfaqApp.html#AlreadyOpen -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > Windows 2000, Excel v8. Does anyone know the syntax for > re-registration with Windows? We get a file already open > error every time we launch a SS without opening t...

Pasting Graphs as Pictures in Excel
In an attempt to save file size, I would like to use paste special to past Excel graphs as pictures within Excel. I know you can paste special as pictures within PPT, but is it possible to do it within Excel???? Not sure if that can be done, what I do is cop the graph, open mspaint paste, select it again copy and paste back into excel, it does it as an image -- -John Please rate when your question is answered to help us and others know what is helpful. "andy.hogan@gmail.com" wrote: > In an attempt to save file size, I would like to use paste special to > past Excel gra...

Displaying a dataset or datatable in Excel
There's something I think ought to be easy, but I can't find a way to do it. Either there's something wrong with the way I'm searching, or there's a reason it can't be done easily. What I want is to create an excel addin that, when the user opens a spreadsheet , it goes out and fetches some data from a database, and displays it in a table. Pretty straightforward. Here's some code: void Application_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook wb) {//I've also attached this to other events. The "open" event isn't...

Recovery excel file
Anybody knows if there is any free tools to recover a advanced excel file (using macro, graphics, formulas, and several formats)???????? Tks, Marcelo Rayol ...

Data changes when Analyzed with Excel
I have a query with 3 tables that represent master item list, count qty and as qty. When I look at the results in Query, all is well and accurate yet when I select Analyze with Excel, values change. For example, if a record shows zero qty in the as of field and zero quantity in the count field, I get a value in one or both of the fields that origianlly were zero. Other than linking the tables and grouping by master item list (to show all parts regardless of qty's), there are no formulas or expressions in this query. One of the tables is linked to a FoxPro table via ODBC driver. ...

copy rows to another file
Dear Experts I have following code,i need to copy desirde rows to new file (r.xls) on sheet 1. This code is able to copy desired rows from active sheet to sheet2(same file) based on values in column G. Would you please guide me? regards Sub Marine() Dim arrParts() As String Dim MyRange As Range, CopyRange As Range Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = Range("G1:G" & LastRow) response = InputBox("Enter rows to copy in the format nnn,nnn,nn") arrParts = Split(response, ",") For Each ...

Powerpoint with Excel formatted information
My team has an excel file with Text and Icons in a status report format. We use it to copy and insert paste into Powerpoint. After copying into Powerpoint, I try to select it and edit but powerpoint gives an error that says: "There isn't enough memory available to read the Excel file" Other team members use this same excel template and powerpoint and they can edit the information after it is copied to powerpoint. And I can use their files and they work fine. Only one team member has this issue so somehow this person's excel file is using a massi...

finding the area under the curve in a graph in excel
Need help having excel calculate the total area under the curve of a graph in excel. Have a look at Bernard Liengme's website: http://www.stfx.ca/people/bliengme/ExcelTips/ HTH Jason Atlanta, GA >-----Original Message----- >Need help having excel calculate the total area under the curve of a graph in >excel. >. > ...

How to transfer Custom Views to another machine
Outlook 2003 How do you transfer Custom Views to another machine (no exchange server)? Thank you for your help. They are contained within your pst-file so if you transfer that you'll transfer the views too. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more ----- "John" <no.email@no.email.com> wrote in message news:usDhjJ7hHHA.4132@TK2MSFTNGP05.phx.gbl... > Outlook 2003 > > How do you transfer Custom Views to another machine (no exchange server)? >...

copy without space
I copy a cell to another app. and it contains a space. How should I cop from Excel without this space -- Message posted from http://www.ExcelForum.com ...

Excel calculates wrong ????
I have had no luck figuring this out... I have a list of 8 positive and negative numbers that 2 decimal place each. The numbers sum to zero confirmed by a calculator, however exce sums them to (2.55654899E12). I do not know why excel calculates the with a small value left that is denoted by scientific notation. I checked the value of each number to ensure that it did not includ any extra decimals, and I even re-typed them. I have had this happen with several other lists of numbers that are n brainers to calculate, and excel added extra scientific notation to th total. I have no idea why ...

Excel 97 dropdown function box
The dropdown function box on the Formula Bar show formulas but will no longer show "More Functions". How can I get this item back? ...

Output to excel ==> replace worksheet
Usually out to excel will replace a workbook.Can we make it to replace worksheet only.Thank's "shiro" <shiro@myname?.com> wrote in message news:umONlZiRIHA.4584@TK2MSFTNGP03.phx.gbl... > Usually out to excel will replace a workbook.Can we make it > to replace worksheet only.Thank's How about, just replacing the contents of a cell, or cells? Dim appXL As Object Dim wkb As Object Dim wks As Object Set appXL = CreateObject("Excel.Application") Set wkb = appXL.Workbooks.Open("C:\MyFolder\MyWorkBook.xls") Set wks = wkb.Worksheets(1) wks.Cell...

Excel 2000/Change Default Opening Location?
I'm using Excel 2000. When I open Excel, and the click on the Open button to open worksheet, it goes to a default location I'd like to change. How can I change that default location? I have searched under Tools > Options, but find no means there. Thanks. -- ---------- CWLee Former slayer of dragons; practice now limited to sacred cows. Believing we should hire for quality, not quotas, and promote for performance, not preferences. Tools|Options|General Tab change the entry in the Default File Location box CWLee wrote: > > I'm using Excel 2000. When I open...

excel v4
Does anyone know when Version 4 of Excel was introduced and/or retired http://j-walk.com/ss/excel/index.htm -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference ---------------------------------------------------------------------------- <anonymous@discussions.microsoft.com> wrote in message news:042201c39e64$4f4d9ab0$a301280a@phx.gbl... > Does anyone know when Versi...