Moving data between worksheets ...

Hi,

Wonder if someone can help me please?

I have a sheet in a workbook called "Sheet1" (please see
www.iedmont.blogspot.com/) and what I am trying to do is move all rows
that contain a date into a separate sheet within the workbook called
"JanArchive".

"JanArchive" already contains archived entries and I would like to
combine the data from "Sheet1" with the data from "JanArchive".

Can anyone offer any suggestions how to do this please?

Many thanks for your time.

Ian Edmont.

0
iedmont (11)
1/19/2006 8:21:48 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
2079 Views

Similar Articles

[PageSpeed] 17

Select cells in sheet which you wish to move.  From Menus pick Edit then
the cut option (you can also use your right mouse button for the
shortcut menus which will bring up these options)

Go to sheet where you want the information to appear.

Select (or right click) in first availble cell and choose paste.

If you are a bit worried that you might loose your information from
sheet 1 while doing this, then you could always choose the copy option
under edit instead, then go back and delete either the information or
the sheet if no longer needed.


-- 
jujuwillis
------------------------------------------------------------------------
jujuwillis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2123
View this thread: http://www.excelforum.com/showthread.php?threadid=502809

0
1/19/2006 8:49:38 AM
Try this:

Option Explicit

Const csz_dst_sheet As String = "JanArchive"
Const csz_src_sheet As String = "Sheet1"

Sub movedata()
Dim wsd As Worksheet 'dst worksheet
Dim wss As Worksheet 'src worksheet
Dim rd As Long ' dst row
Dim rs As Long ' src row

Set wsd = ActiveWorkbook.Worksheets(csz_dst_sheet)
Set wss = ActiveWorkbook.Worksheets(csz_src_sheet)

'find last row on dst
rd = 2
While wsd.Cells(rd, 1) <> ""
    rd = rd + 1
Wend

rs = 2
While wss.Cells(rs, 1) <> ""
    If wss.Cells(rs, 2) <> "" Then
        wss.Rows(rs).Copy Destination:=wsd.Rows(rd)
        rd = rd + 1
    End If
    rs = rs + 1
Wend
Set wss = Nothing
Set wsd = Nothing
End Sub
'------------
-- 
HTHs Martin


"Ian Edmont" wrote:

> Hi,
> 
> Wonder if someone can help me please?
> 
> I have a sheet in a workbook called "Sheet1" (please see
> www.iedmont.blogspot.com/) and what I am trying to do is move all rows
> that contain a date into a separate sheet within the workbook called
> "JanArchive".
> 
> "JanArchive" already contains archived entries and I would like to
> combine the data from "Sheet1" with the data from "JanArchive".
> 
> Can anyone offer any suggestions how to do this please?
> 
> Many thanks for your time.
> 
> Ian Edmont.
> 
> 
0
1/19/2006 8:56:03 AM
Thanks for that Martin, it worked fine however it leaves the rows on
Sheet1 in place.

Is there a way to delete the rows from Sheet1 after they have been
transferred to JanArchive?

Many thanks.

Ian Edmont.


Martin Fishlock wrote:

> Try this:
>
> Option Explicit
>
> Const csz_dst_sheet As String = "JanArchive"
> Const csz_src_sheet As String = "Sheet1"
>
> Sub movedata()
> Dim wsd As Worksheet 'dst worksheet
> Dim wss As Worksheet 'src worksheet
> Dim rd As Long ' dst row
> Dim rs As Long ' src row
>
> Set wsd = ActiveWorkbook.Worksheets(csz_dst_sheet)
> Set wss = ActiveWorkbook.Worksheets(csz_src_sheet)
>
> 'find last row on dst
> rd = 2
> While wsd.Cells(rd, 1) <> ""
>     rd = rd + 1
> Wend
>
> rs = 2
> While wss.Cells(rs, 1) <> ""
>     If wss.Cells(rs, 2) <> "" Then
>         wss.Rows(rs).Copy Destination:=wsd.Rows(rd)
>         rd = rd + 1
>     End If
>     rs = rs + 1
> Wend
> Set wss = Nothing
> Set wsd = Nothing
> End Sub
> '------------
> --
> HTHs Martin
>
>
> "Ian Edmont" wrote:
>
> > Hi,
> >
> > Wonder if someone can help me please?
> >
> > I have a sheet in a workbook called "Sheet1" (please see
> > www.iedmont.blogspot.com/) and what I am trying to do is move all rows
> > that contain a date into a separate sheet within the workbook called
> > "JanArchive".
> >
> > "JanArchive" already contains archived entries and I would like to
> > combine the data from "Sheet1" with the data from "JanArchive".
> >
> > Can anyone offer any suggestions how to do this please?
> > 
> > Many thanks for your time.
> > 
> > Ian Edmont.
> > 
> >

0
iedmont (11)
1/19/2006 9:06:08 AM
Ian on the second loop modify it as thus:

rs = 2
While wss.Cells(rs, 1) <> ""
       If wss.Cells(rs, 2) <> "" Then
            wss.Rows(rs).Copy Destination:=wsd.Rows(rd)
            wss.rows(rs).Delete
             rd = rd + 1
        else
            rs = rs + 1
       End If
Wend

-- 
HTHs Martin


"Ian Edmont" wrote:

> Thanks for that Martin, it worked fine however it leaves the rows on
> Sheet1 in place.
> 
> Is there a way to delete the rows from Sheet1 after they have been
> transferred to JanArchive?
> 
> Many thanks.
> 
> Ian Edmont.
> 
> 
> Martin Fishlock wrote:
> 
> > Try this:
> >
> > Option Explicit
> >
> > Const csz_dst_sheet As String = "JanArchive"
> > Const csz_src_sheet As String = "Sheet1"
> >
> > Sub movedata()
> > Dim wsd As Worksheet 'dst worksheet
> > Dim wss As Worksheet 'src worksheet
> > Dim rd As Long ' dst row
> > Dim rs As Long ' src row
> >
> > Set wsd = ActiveWorkbook.Worksheets(csz_dst_sheet)
> > Set wss = ActiveWorkbook.Worksheets(csz_src_sheet)
> >
> > 'find last row on dst
> > rd = 2
> > While wsd.Cells(rd, 1) <> ""
> >     rd = rd + 1
> > Wend
> >
> > rs = 2
> > While wss.Cells(rs, 1) <> ""
> >     If wss.Cells(rs, 2) <> "" Then
> >         wss.Rows(rs).Copy Destination:=wsd.Rows(rd)
> >         rd = rd + 1
> >     End If
> >     rs = rs + 1
> > Wend
> > Set wss = Nothing
> > Set wsd = Nothing
> > End Sub
> > '------------
> > --
> > HTHs Martin
> >
> >
> > "Ian Edmont" wrote:
> >
> > > Hi,
> > >
> > > Wonder if someone can help me please?
> > >
> > > I have a sheet in a workbook called "Sheet1" (please see
> > > www.iedmont.blogspot.com/) and what I am trying to do is move all rows
> > > that contain a date into a separate sheet within the workbook called
> > > "JanArchive".
> > >
> > > "JanArchive" already contains archived entries and I would like to
> > > combine the data from "Sheet1" with the data from "JanArchive".
> > >
> > > Can anyone offer any suggestions how to do this please?
> > > 
> > > Many thanks for your time.
> > > 
> > > Ian Edmont.
> > > 
> > >
> 
> 
0
1/19/2006 9:23:03 AM
Thanks very much Martin. Exactly what I needed.

Ian Edmont.


Martin Fishlock wrote:

> Ian on the second loop modify it as thus:
>
> rs = 2
> While wss.Cells(rs, 1) <> ""
>        If wss.Cells(rs, 2) <> "" Then
>             wss.Rows(rs).Copy Destination:=wsd.Rows(rd)
>             wss.rows(rs).Delete
>              rd = rd + 1
>         else
>             rs = rs + 1
>        End If
> Wend
>
> --
> HTHs Martin
>
>
> "Ian Edmont" wrote:
>
> > Thanks for that Martin, it worked fine however it leaves the rows on
> > Sheet1 in place.
> >
> > Is there a way to delete the rows from Sheet1 after they have been
> > transferred to JanArchive?
> >
> > Many thanks.
> >
> > Ian Edmont.
> >
> >
> > Martin Fishlock wrote:
> >
> > > Try this:
> > >
> > > Option Explicit
> > >
> > > Const csz_dst_sheet As String = "JanArchive"
> > > Const csz_src_sheet As String = "Sheet1"
> > >
> > > Sub movedata()
> > > Dim wsd As Worksheet 'dst worksheet
> > > Dim wss As Worksheet 'src worksheet
> > > Dim rd As Long ' dst row
> > > Dim rs As Long ' src row
> > >
> > > Set wsd = ActiveWorkbook.Worksheets(csz_dst_sheet)
> > > Set wss = ActiveWorkbook.Worksheets(csz_src_sheet)
> > >
> > > 'find last row on dst
> > > rd = 2
> > > While wsd.Cells(rd, 1) <> ""
> > >     rd = rd + 1
> > > Wend
> > >
> > > rs = 2
> > > While wss.Cells(rs, 1) <> ""
> > >     If wss.Cells(rs, 2) <> "" Then
> > >         wss.Rows(rs).Copy Destination:=wsd.Rows(rd)
> > >         rd = rd + 1
> > >     End If
> > >     rs = rs + 1
> > > Wend
> > > Set wss = Nothing
> > > Set wsd = Nothing
> > > End Sub
> > > '------------
> > > --
> > > HTHs Martin
> > >
> > >
> > > "Ian Edmont" wrote:
> > >
> > > > Hi,
> > > >
> > > > Wonder if someone can help me please?
> > > >
> > > > I have a sheet in a workbook called "Sheet1" (please see
> > > > www.iedmont.blogspot.com/) and what I am trying to do is move all rows
> > > > that contain a date into a separate sheet within the workbook called
> > > > "JanArchive".
> > > >
> > > > "JanArchive" already contains archived entries and I would like to
> > > > combine the data from "Sheet1" with the data from "JanArchive".
> > > >
> > > > Can anyone offer any suggestions how to do this please?
> > > >
> > > > Many thanks for your time.
> > > > 
> > > > Ian Edmont.
> > > > 
> > > >
> > 
> >

0
iedmont (11)
1/19/2006 9:32:34 AM
Reply:

Similar Artilces:

SQL in Excel data
Hi all, Is there a possibility/way to run an SQL query in an excel data sheet? I have quite some data like the sample below, now i would like to have the sum of spending for each person. Like it is possible in Access. A1 B1 Field1 Field2 Chuck 12,89 Mike 23,09 Jean 9,34 Chuck 30,00 Mike 3,80 Chuck 22,00 Mike 7,23 Jean 10,55 Jean 10,75 Jean 31,45 Chuck 19,99 Result Field1 SumOfField2 Chuck 84,88 Jean 62,09 Mike 34,12 Advice would be appriciated. Cheers, Ludovic Hi You could use a formula like this ...

DPM forces a Consistency Check on data
Hi all, I have a DPM 2007 SP1 server in one office ? and are trying to backup data in another office, connected by T1 (1.54Mbps) link. It works, but every time there is an issue ? DPM forces a Consistency Check on XGB( >100) of data, which takes 5-7 days to complete. can any tell abt this. thanks in advance gopal Submitted via EggHeadCafe - Software Developer Portal of Choice Useful ASP.NET Exception Engine http://www.eggheadcafe.com/tutorials/aspnet/52012346-36db-4a47-ac61-8b9c9b4ce9a0/useful-aspnet-exception.aspx Hi Gopal, Can you please eloborate on "Ev...

How to get XML data out of an XML file
I am trying to retrieve the Parameters first or second (0, 1 ,2) node from the following XML file: <?xml version="1.0" encoding="utf-8" ?> <Robot xmlns="http://tempuri.org/RobotDefaults.xsd"> <Parameters> <Name>Decker</Name> </Parameters> <Parameters> <Name>A</Name> </Parameters> <Parameters> <Name>B</Name> </Parameters> </Robot> There will be more data than just a name for each Parameters node. Here is my code: Dim node As XmlNode = xmlDocument.SelectSingleNode("/P...

Moving Dynamics
Can someone direct me to updated infomation on How to move GP 10 to a new server box with SQL 2008 KB article 878449 still applies and has updated information for SQL Server 2008. -- Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "tstrop" wrote: > Can someone direct me to updated infomation on How to move GP 10 to a new > server box with SQL 2008 ...

Cannot move emails between folders
I am the Administrator on my home machine. If I try to move an email from the Inbox to another folder I am told 'I don't have permission, check the folder properties for permission' There is nothing in the folder properties about permissions. This has only recently started happening. XP Home and Outlook XP with all updates. ...

Moving to the next day in Calendar
I cannot find how to quickly select the next or previous day when in Calendar Day view (except by clicking the next day date) ..... sorry if this is a stupid question! Thanks, Rob The left and right arrow keys should work...you may have to select the date, though, to make sure it has "focus". -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:1069486675.651039@ananke.eclipse.net.uk, Rob wrote: > I cannot find how to quickly select the next or previ...

moving outlook to a new computer
A sudden and unexpected component fault(graphics chip) on the motherboard of my laptop necessitated the purchase of a new laptop. I had not backed up any data from the old machine, but have now restored all my documents and files via an external hard drive adaptor. Unfortunately I'm struggling to transfer my Outlook data to the new laptop from the old drive. I have followed the Import/Export wizard in Outlook to import the .pst file after saving it from the old hard drive, but just get the following message "This Personal Folder file (.pst) is already in use in the curre...

Erase data, preserve formula's
I have a an excel file with 12 worksheets for the financial year and an additional worksheet for yearly totals. I need to get a blank copy of this and was wondering if anyone knew a way to delete all the user inputted data while keeping the formatting and formula's intact. Any help is much appreciated. -- urbanfox ------------------------------------------------------------------------ urbanfox's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22826 View this thread: http://www.excelforum.com/showthread.php?threadid=519004 Hi Hit F5 and select Special a...

can servers be moved between administrative groups?
Below is an extract from the 70-284 MS training kit. I am totaly confused i have been told in this newsgroup by an MVP that you can not move a server once installed to a different administrative group and have also read elsewhere that you cannot but Microsoft below say you can in both modes. Subsequent Exchange servers are added to routing groups during the installation process,and if necessary, you can move servers between routing groups using Exchange System Manager. The only caveat is that in mixed mode, you cannot move servers between routing groups that belong to different administrative...

Move/Copy A Row Based on Formulas to a New Worksheet
I want to move several rows of sub-totals (averages within sub-groups) to a summary worksheet, but I get the Ref error. How can I copy sub-group averages to another worksheet? Thank you. high light and copy. select where you want it. edit>paste special>values. this will turn you formulas into hard numbers. you are getting the #Ref error because on the other sheet where you pasted the formulas, the formula no longer had the same references that they had on the other sheet. for example: =sum(a1:a10) in cell a11 you copy and paste on another sheet at cell a1. excell tries to compensat...

Two mailboxes for one user account after mailbox moved to a new server
After moving a user's mailbox from a Exchange 2003 to a new Exchange 2003 server, Outlook has listed two mailboxes with same name in the "All Mail Folders"area. The Outlook profile only has the user mailbox and no other mailboxes added to it. The user can send and recieve email just fine. The mailboxes seem to be clones of each other. New mail show up in the mailboxes at the sametime. How can I get Outlook to only show one mailbox? Any help would be appreciated, thanks, ...

Embedding worksheet into Word
Has anyone had the experience where when they embed a worksheet into Microsoft Word, a portion of the worksheet gets cut off? Is there an easy fix to this? ...

Keep custom format in new worksheet
Hi all, I created a new custom format in "Format Cells", after using this custom format I save the file and quit, when I open the old file the custom format still in this file, but when I try to create a new file the custom format is lost. Is it possible to keep the custom format forever in the Excel so that I don't need to create it again when open new workbook? Many Thanks. Buddy See Ron's reply in .misc -- HTH RP (remove nothere from the email address if mailing direct) "Buddy" <tcheung27@sinaman.com> wrote in message news:uAWhxPHKFHA.3348@TK2MSF...

SUMIF function not calculating from closed worksheet
We recently upgraded from Microsoft Office 2000 to 2007. Prior to the upgrade each department would access a spreadsheet that would load data from a common lookup spreadsheet The loading process was performed as follows: The lookup file and each department file would be opened to update the link for that month and then saved and closed. When the department head would open the department file, a question would appear asking if they wanted to update the link, if they answered no the spreadsheet would open and display the calculated data. If they answered yes, the file would o...

Macros for worksheet copy & paste?
I have 6 different excel files. How can I combine all 6 of them in one single excel file under individual worksheets (E.g. Worksheet1 for File1, worksheet2 for File2 ....etc)? I do not wish to copy & paste it manually. Can I use a macro to take care of it? If so, where can I find further info on how to go about it? THANKS!! Assuming (1) this is a one-time need, and (2) your workbooks have only one sheet each, this would probably be faster done by hand: 1) With all the files open choose one of the files to be the receiving file. 2) Select the sheet in the subsequent books, one at...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

How refresh imported data automatically?
In Excel, I've imported data into a worksheet. I know I can click Data.. Refresh Data to requery the source, but I want to be able to do it in a more automated way. Is there some sort of macro I can write when opened or something? Thanks, Ron Ron, Select the cell the data starts in, select Data/Import External Data/Data Range Properties Check the box "Refresh on file open" and anything else that needs checking off... Other than that, you could record a new Macro that selects your cell, refreshes data, and assign the macro to a keystroke, or even an icon. Beege ...

excel, worksheet, set print area, position selection on printed pa
Office 07, Excel. Print area is set, print preview displays selection, always on far left of page. I want to choose the position on the page, eg centered or right aligned etc. Help please. Hi, To get your printout to center on the page chose Page Layout, click the Page Setup quick launch button (bottom right corner of the Page Setup group). Choose the Margins tab, click Horizontal. There is no command to right align a printout, but you can change the left margin to force the printout to the left. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Eve Al...

Adding additional rows for data entry
I have a spreadsheet with five columns that I enter data to. I then have a blank row at the bottom of these columns. Below the blank row I have several formulas pertaining to each row. How do I add more data to the columns and have the formulas adjust for these new rows without highlighting rows and using the insert rows command to make room (empty rows) where I can then add the additional data to the columns. Is there a formula that would always leave one empty row even when new data is entered in the columns? Thanks Put the formulas at the top of the columns. You can even use a Freeze ...

Question About Missing Data
So, this is probably really easy, but I just want to ask and see if I may be missing something here. Some data on vendor numbers changed. Let’s say IBM used to have a vendor number 12345 and now it’s vendor number is 56789. I can identify IBM as IBM, but I really want to use the number, not the name. Should I set up a table that ties the numbers together, so that Access knows 12345 = 56789? Or, should I do some kind of Update Query and change all incidences of 12345 to be 56789? Or, is there some other, method, like a ‘best practices for missing data’? Thanks! Ryan--- ...

Outlook Data Files #4
I've done some reading but I'm still confused about the use of Office Outlook Personal Folders File (.pst) versus Outlook 97-2002 Personal Folders File (.pst). I understand the basic "Office Outlook Personal Folders File (.pst) to create a new Outlook 2003 .pst file. Click Outlook 97-2002 Personal Folders File (.pst) to create a new Outlook .pst file that is compatible with earlier versions of Outlook." When we have upgraded to Outlook 2003 or set up a new PC with Outlook 2003, when adding PST's you can chosse either of the two file type options and browse to chos...

Pivot table and organizing data
This one is really making me scratch my head. Here is the story. I have a list of information which I am pulling in via a query from SQL. Data is good and it correctly comes into Excel (03 or 07). I have five columns with data: Date, Time, AccountID , Status. First two are self-explanatory; third is a 3-letter ID, forth is a status (pass/fail). Ok, now that you have an idea, here is what I need: 1. List the account IDs as a column 2. List the dates as rows 3. Place the alert into the location that corresponds to the appropriate data and account I know this 'sounds' like a strai...

Copy data from one record to a new record on a sub form
I have a form showing patient details with a sub form displaying all associated referral details for the patient. One patient can have more than one referral and I would like to be able to copy data from some of the fields on the current referral when adding a new referral for the same patient. Is this possible? Thank you for your help I am fairly new to Access code and I am having some problem following your suggestion could you please simplify. Also some of the fields on the referral records are selected via a combo box will these fields still copy? "Klatuu" wrote: > ...

Prevent Hidden Column data from being copied/pasted?
A student came up with a question that I haven't been able to figure out yet in a recent Excel class. They are hiding a column and protecting the worksheet in the correct manner. They want to allow some users to access and enter information in some cells. They do not want the users to be able to copy and paste the information from the hidden column. The question is how can this be prevented? For example, Column B is hidden. When they copy a range such as A1:C10 and paste it to another worksheet, they are getting the "hidden" data in Column B in B1:B10. Any suggestions wou...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...