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
2012 Views

Similar Articles

[PageSpeed] 43

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:

Custom Data Validation
Hi Could someone be kind enough to tell me what is wrong with the following formula =COUNTIF($F11:$AN11,$AR11:$BF11,AN11)=1 Thaks You have specified too many arguments, what are you trying to achieve. -- If this helps, please click "Yes" <><><><><><><><><><><> "Gotroots" wrote: > Hi > > Could someone be kind enough to tell me what is wrong with the following > formula > > =COUNTIF($F11:$AN11,$AR11:$BF11,AN11)=1 > > Thaks The syntax for COUNTIF is =...

2 DATA RANGES IN CHARTS
I am trying to create a chart of stacked data The data I have is Class of cost Planned and Actual by month/year I want a chart that will show me actuals by class of cost and then side by side actual and planned I am not sure if i can create a chart that will range two data series. If you arrange your data properly, a lot of things in Excel are vastly simplified. To chart multiple series, put the category labels (or X values) into a column, put the Y values for one series into the column to the right, and put Y values for any additional series in the next columns after that. If the da...

Charting 2 Sets of Data
I have 2 sets of data. One set is in Billions (AUM) and I would like to have that be a bar chart. I would also like a line through the data that show Revenue, Expenses, and Net Income but this set is in the Millions so I need to have it scale appropriately. Any thoughts? I'm clearly not a Chart Guru thus asking for help, thx. Ok I figured it out my bad for even asking . . . "Darin" wrote: > I have 2 sets of data. One set is in Billions (AUM) and I would like to have > that be a bar chart. I would also like a line through the data that show > Revenue, Ex...

exporting data to Excel through Export Solutions
Hello: I created an Excel Export Solution macro that works perfectly. Subsequently, I created two other Excel Export Solutions. But, when I export the data to Excel from SmartList through a macro the data in Excel displays blank! Why is this? This is on GP 10.0 with service pack 1 and Office 2003. I have never had this happen before. I have configured SmartList with macro-based Excel Export Solutions many times before, and this is the first time that I have seen blank data. And, it does not matter how complex or simple the macro is. And, it does not matter how many times I recre...

Move the *.mny file
I have been using money since Money 2001. When I installed Money 2006, I discovered part of Money 2001 was still their and that is where my *.mny file is stored. How do I move my *.mny file to get it into the Money 2006 folder, and still have money be able to find it. I almost lost it when I first deleted Money 2001, and had to retreve it from the Recyle bin. Thanks, -- Jared3 Move it to the new folder, and then double-click it to open the file. Money 2006 will remember the location next time you start up the program from the Start Menu -- Glyn Simpson, Microsoft MVP - Money http:...

data across two different spreadsheets
Hi, I have an interesting problem here. There are 2 spreadhseets (.xls files). The first spreadsheet has a columns which has either a Yes, No or NA in it. What the second spreadsheet does is it counts the number of Yes and No in that column of first sprsheet and calculates the % of Yes in it, and displays it in a cell. For example: B1 to B15, B18-B20. Out of these 18 concerned cells in first spreadsheet, 9 are y rest are no and there are no NAs say. Then say cell B1 in second spreadshhet displays 50%. So, the real deal is to collect data from another spreadsheet. Thanks for any help. -Sash....

[URGENT] Linking Dates to a Data Worksheet
I have a control sheet with hire dates and values for these hires. I have another worksheet with week ending dates on it. I need to find a way to link these values into the weeks ending, based on the start date. I know how to use the sum if formula but I dont know how to do it with dates. Since 6/1 and 6/6 will go in the same cell. I need to have my system automated so this is really important. Thanks. Am I correct that you want to sum all the values for the new hires, by week? I'll assume the work week is Mon-Fri, and your weeks start on either Sunday or Monday (it won't matter whi...

How can i use the dash character, "-", to sort data?
If i wanted to sort the info below into two different columns, how would I do it?SOMRVLLE - SOMERVILLE SOUTH - SOUTHERN REG. ST. JOHN - ST. JOHN VIANNEY ST. ROSE - ST. ROSE HIGH ST. ROSE - ST. ROSE HIGH STAF - SOUTHERN REG. Hi use 'Data - Text to columns' to split the content into two columns -- Regards Frank Kabel Frankfurt, Germany "Val" <Val@discussions.microsoft.com> schrieb im Newsbeitrag news:1F8EE451-7B54-49D1-B8C7-B21575180B81@microsoft.com... > If i wanted to sort the info below into two different columns, how would I do > it?SOMRVLLE - SOMERVILLE ...

Move Selection after Enter
Is it possible to have the Move Selection after Enter setting in Options - Edit be set per xls file. I like the selection to move Down after Enter but I have one file that I would like the selection to move to the Right after Enter. Thanks, Marty You can use a macro to set it for this particular workbook, first create the code open the workbook where you want to have this happen, then do tools>macro>record new macro do tools>options>edit and change from the default down to right, click OK while still recording repeat and turn move down on again, stop the macro press alt + F1...

After moving profile OUTLOOK.PST not accessible
I have just move a users profile to another profile using the Copy to option in system properties. However outlook is still looking for the OUTLOOK.PST file in the old location, in which the user no longer has right to. How do I change that so that it will look for the file in her profile. Thanks, Matt On Wed, 3 Sep 2003 10:19:07 -0500, "Matt Burks" <none@yourbusiness.com> wrote: >I have just move a users profile to another profile using the Copy to option >in system properties. However outlook is still looking for the OUTLOOK.PST >file in the old location, in whi...

Move Columnar data to Rows
I've received a rather large spreadsheet from a client that's laid out like this: Acct Jan 2004 Feb 2004 Mar 2004 Apr 2004 CAR 108 104 409 109 BOAT 56 100 300 105 I need to change it to be laid out like this: Acct Date Amt CAR Jan 2004 108 CAR Feb 2004 104 CAR Mar 2004 409 CAR Apr 2004 109 BOAT Jan 2004 56 etc...etc...etc. Isn't there some sort of pivot table function I can use to do that? Thanks in advance for your assistance. ~cr To reorganize the data, you can use the "unpivot' techni...

Using Enter Key to move from field to field
I've created two forms in the same database. In the first form I must use Tab to move through the fields -- the second form allows the use of the Enter Key. How can I get the first form to accept the Enter Key? Right now if I push Enter by mistake in the first form it erases the text (but if I do "undo" the text is restored.). I can't find where to set this. FYI - I have a totally different database where the form also allows the Enter Key to be used to move from field to field. Thanks for any help. -- Posted via a free Usenet account from http://www.teranews.com...

Worksheet Change event #3
I have a range on one of my worksheets that I use the Worksheet change event for. The range is a column of about 200 rows where the user can enter percentages. Right now I have code in there that makes sure they don't enter a percentage less than a default one that has been already set. If they do enter one that is less, then a message is displayed and the value of that cell is set back to the default. The user is allowed to increase the percentage. However, if the user increases it and then goes back to the cell to try to make it lower than the default, then it sets it back to the d...

Data / Sort not working ????
Hello all, I'm basically a newbie to Excel and only create very basic spreadsheets with basic function. (I'm using Excel 2002) I've creating a simple spreadsheet to keep track of finances. I'm having problems with the "Data/Sort" function. Not sure the best way to explain my satiation so I've pasted an example: Customer Credits Debts Balance Paul $200 $150 $50 Billy $200 $151 $50 John $200 $152 $48 Shawn $200 $152 $48 George $200 $155 $45 Aaron $200 $155 $45 Adam $200 $156 $44 Dave $200 $162 $38 J...

how to share a row throughout the worksheet?
What do yo mean? "asd" wrote: > ...

Data Entry #2
Hi Can anyone help me? I have an excel workbook at work and all the data needs to be filled in along the row. The problem is, some people leave parts of it blank!! Is there any way of creating a form, like in Access to ensure all cells are filled in correctly? Perhaps using a HTML document would work as a type of online form, but i am unable to use MS Access in this project. Unfortunately, i am unable to use Access as the form is a nationwide document over the network and so needs to be kept as an Excel document. Is it possible to do this, or is there some way to prompt the user to fill i...

transferring data/text to another worksheet
I have two columns/rows with the following data on Sheet1: Column: A B Row: 1 178 buy 2 buy 3 185 buy 4 140 sell 5 sell 6 sell 7 130 buy I want to tranfer to sheet 2, all the cells in column A that have number value and if column A has a number, then transfer the text i column B. This is what I want sheet 2 to look like: ...

Data Duplication
Can anybody tell me how I can avoid importing data into MS CRM that is already held in MS CRM as there are no tools to automatically detect duplicate records and purge them? CRM as you have found does no form of data dupiclation checking so you need to code solutions to review the data or better yet train your users to search for data before creating it. Such as making them look for an account before creating a new one etc "Lee" <lee.hargrave@quiss.co.uk> wrote in message news:82d1a37d.0403010925.54b59d64@posting.google.com... > Can anybody tell me how I can avoid importi...

importing access data
I want to put certain pieces of access data in a specific location on an excel file. Should I just import the data as a new sheet and then write the appropriate excel formulae/coding to distribute these where needed or is there a cleverer way of doing this ? If I were going to do this more than once, my approach would be to write a macro that will use MSQuery to import the ACCESS data to a helper sheet and then use further macro commands to copy the data from there to where you want it and then delete the helper sheet. If only once, then as you describe in your second sentence. Vaya...

How do i copy columns of data in notepad into microsoft excel?
I have a several large columns of data stored in notepad. The columns are all next to each other. I need to put column 1 in column A in excel and column 2 into column B in excel etc. Copy and paste does not work. I can’t just highlight one column I end up highlighting all 6 columns! If a just copy a paste all the data, the entire data just goes into column A of excel, and I can not perform any excel functions on the data. HELP! This is extremely annoying, I am beginning to think the only way is to copy each bit of data by hand into excel, but there over 1000 items!!! HELP!!!!!!!!!!!!!!...

How can I create a data dial using visio 2002?
I have visio 2002 and never used it before until my work suggested that I should create my Data Dashbords in it. My dashboard includes several Dials that I produce on a monthly basis and basically used to create them myself in excel by hiding half of a donut Chart! If dials can be done in excel, is there a way of updating the data so that the dial automatically updates? If not, is there any other office programme I could use? This is easily done with Visio 2007 Professional using the Data Link and Data Graphics features. In prior versions of Visio you would need to construct your own...

The selected area grows when I click a cell or press keys to move.
The F8 function key seems ineffective in turning of the extended-selection function. I'm using Excel 2000 SP3 I should by grateful if someone would advise me how to terminate this feature. You could try downloading and installing a new version of the mouse driver, if one is available from the manufacturer's web site. Or, try a new mouse and driver on the machine. David McRitchie also has some suggestions on his web site: http://www.mvps.org/dmcritchie/excel/ghosting.txt yendorexcel wrote: > The F8 function key seems ineffective in turning of the extended-selection > f...

moving Microsoft Exchange System Objects Container
I have a problem with mail enabling public folders. I am getting error c1038a21 and after searching I found an article below that gives the cause of this error is The Microsoft Exchange System Objects folder is not located in the root of the domain. After looking at my AD I discovered that the Microsoft Exchange System Objects folder is actually under Lost and Found and not the root of the domain. How would I move this folder back to the root of the domain? Is it as simple as moving the folder or is there anything that I need to do with exchange before I move this folder. I have h...

Convert data in Excel file to vsd format
H We are dynamically building an Excel sheet aith data to be shown as an Organization chart How can we convert this file into vsd format from Visual Basic Also, how can we show the Excel file contents on the WEB using asp Really Urgent. Regard Ajith well, I'm looking at Office2003 and Visio2003. Visio has the OrgChart Wizard for import of excel data, and Excel has the export of html. I believe this is functional back to the 2000 version as well. What version of the tools are you using? Al "Ajitha Anil" <anonymous@discussions.microsoft.com> wrote in ...

Minimised excel worksheets in the taskbar
We are running Excel 2000 (part of office 2000) on a windows 2000 terminal server. When the user was running excel they used to be able to minimise a worksheet (not excel) down, the worksheet title bar would be placed at the bottom of the excel window and the worksheet name would be appear on the taskbar, this was handy when the user were moving between several worksheets as the just clicked on the worksheet in the taskbar to change worksheets. But now for some users when they minimise a worksheet the worksheet title only appear at the bottom of the excel window and nothing on the taskbar. ...