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

Similar Articles

[PageSpeed] 8

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:

how to paste data from top to bottom to bottom to top
some of the historical data (monthly data, 1980-jan, Feb, march...1981-Jan, feb.march....) are on this format. But i need them to be pasted as the otherway around (1981-Dec, Nov.....feb,jan,1980-dec,Nov...feb,jan). If I had to do this, I'd try to convert each of those non-dates into real dates. One way is to use a formula: with 1980-Jan in A1, I could put this in B1 =DATEVALUE(RIGHT(A1,3)&" 1, "&LEFT(A1,4)) Format that cell as a date and see: Jan 1, 1980 Then you could do pretty much anything you want with that column of real dates. But the formula you need really...

data from a modeless dialog box to its parent
I need to post a text data from a modeless dialog box to its parent which is itself a dialog based application. Since it should be a large text data a simple Windows message won't be good. What is the right way to do that? Thanks Gil If you are sure the data will stay current you can post the address of the data (as a pointer) in a windows message to the parent. If you want to be really sure use SendMessage() which will wait until the parent gets the message before returning. You could call a function or copy the data to a variable in the parent dialog, but that could be dang...

Calendar from Excel Data
I currently keep a fairly complex calendar as an Excel spreadsheet. I would like to find a way to automatically generate a normal-looking month-by- month calendar from this. I really don't need the month-by-month calendar to show much of the complexity, just the events and times and possibly locations in the correct dates on the calendar. I did a little looking on the web and saw many calendar systems, but it wasn't clear to me which might be able to import my data from Excel. --David ...

Showing Specific Data from Master Sheet to Another Sheet
I am making a bill of materials at work and have a master list I want to access from other sheets. The master list has all parts named and categorized, and I want to be able to access sections (Hydraulics, Chassis, Electrical, etc.) of the master from other sheets. I have tried pivottables but I am having issues showing the data in it original form: Part Name Quantity Material Expense Category etc. (accross the row) I wish to be able to make calculations only off the selected data on each sheet but I am having immense problems getting there. If you can help. Parker Jo...

highlight range, apply calculation to data in cells and paste special to same range
I know how to select a range of cells and copy: Range("O2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy but now 1. applying the calculation, which should be value of cell * 1000 2. special past values only to same selected sells ? You enter 1000 in a separate blank cell and copy it then highlight the range to be multiplied and past special-> multiply. that will multiply all the cells by 1000 in the pasted range. Regards, OssieMac "S Himmelrich" wrote: > I know how to select a range of cells and copy: &g...

Exporting Data
I am looking for a way to export my contacts and the associated notes out of CRM in a format I can import into Goldmine as I need to archive old contacts. Any ideas would be appreciated. Simple way: Create a contacts view that has all the columns that you want to archive, then do an advanced find on the contacts you want exported, click the Export to Excel button at the top of the view and save them all into Excel. Then save your Excel spreadsheet as a CSV in order to import it into Goldmine. Not so simple way: Use a product like Scribe (www.scribesoft.com). -- Matt Wittemann http://...

Moving Emails to a Public Folder
Hi, I have Exchange 2003 automatically forwarding anything sent to several email addresses to one public folder. The problem is that these items appear in the public folder with the "Posted To:" field, but not the "To:" field, so we don't know which email address it was sent to (without looking in the headers). When you reply, the original email in the body still only references the public folder "Posted To:" field, and not the email address it was originally sent to. Is there a way to change this behavior so the "To:" field is displayed? If not,...

pulling data 04-27-10
I'm not sure I am explaining this right I want the data from C21 on sheet 1 in a cell on sheet 2 if the A & B column data matches SHEET 1 row 21 A B C D E 4/21/2010 34287 74 3 4 SHEET 2 row 20 A B C D E 4/21/2010 34287 74 try =sumproduct((s1!a2:a22=a2)*(s1!b2:b22=b2)*s1!c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Norm" <Norm@discussions.microsoft.com> wrote in message news:A40C8E08-721F-47BB-A73C-458AC...

Help: message "Requesting data from Exchange Server"
Has anyone figure out why we receive the pop-up message "Requesting data from Exchange Server" when we try to open an email in outlook? We are using Exchange 2000. Is there a parameter we can change to fix this issue? "Kenny" <anonymous@discussions.microsoft.com> wrote: >Has anyone figure out why we receive the pop-up >message "Requesting data from Exchange Server" when we >try to open an email in outlook? We are using Exchange >2000. > >Is there a parameter we can change to fix this issue? Depends to a greater or lesser extent ...

Data source for PivotTable-Form in ACCESS 2000
Hi, this drives me crazy, 4 years ago I defined in an ACCESS 2000 application a "PivotTable-Form". The resulting EXCEL table inclusive the "Data refresh" works perfect. I now want to update/change the query for the "Data Source" but cannot find which query is behind the "PivotTable-Form" or behind the resulting EXCEL spreadsheet. When editing the properties for the "PivotTable-Form" or the EXCEL spreadsheet the "Data Source" is always blank. Question: Where does Access 2000 or EXCEL 2000 hide the respective data source (Query)...

MS CRM 4.0 Rollup 2 error Data ProductInfo.ExistingLanguage is not available
Recently upgraded to CRM 4.0. Details are as follows - CRM is on premise - 60 Outlook clients - 32 bit server w/ SRS and Exchange router on CRM server - 64 bit DB CRM 4.0 upgrade goes well. Server application of Rollup 2 works fine. Most of my 60 Outlook clients are getting a popup upon running the rollup 2 .EXE: "Data ProductInfo.ExistingLanguage is not available" These are all US English installs (language id 1033). This is not a multi-language install. All users were made "local admins" for the purpose of the client upgrade to 4.0 as well as the rollup install. About...

Pull Data From Multiple Tables ????
Hi I will have 4 tables name "TblCostomers","TblVendors","TblAccounts", TblExpenses" Now i have a for name "FrmDrVouchers" that has a table "TblDrVouchers" in source. This form has two TxtBox Control name "TxtAccountNo" and "TxtAccountName" If User enters a Account No., It pulls the Account Name from Any One of these Table. I can do this if I have only one table. But tell me how can i do it while I have 4 tables for One Field of a table Thank you.. -- Message posted via AccessMonster.com http://...

Quote for Customer can't find data source #3
I need assistance finding the data source for the quote mail merge in CRM 4.0. I get a error "mail_merge_64494[1].doc is a mail merge document. Word cannot open the data source, ." Here is the process I go through before I get the error: - I open a quote - click on menu bar button "Print Quote for Customer" - select organization mail merge, Quote for customer, and OK - when asked if I want to open or save, I select open - I pick yes, when it asks if it can put data on my document I then get message above. How do I find the datasource? Also is there a way to configure...

update worksheet in one file with worksheet in another file
I want to have one worksheet in one of my excel files (file 1) to reflect the contents of a worksheet in another file (file 2) every time it is opened (file 1), that is, everytime it is opened this one tab should reflect the last changes made on the tab in another file, including formating. I suspect this is possible but I don't have a clue how to do it. I have a few questions: Can this be done without VBA? (I'm not even a beginner with VBA!) I did search the web some and found the following that looks interesting: http://techrepublic.com.com/5208-6230-0.html?forumID=101&threadI...

fitting a polynomial to data
Hi, I have three (equal sized) sets of data, T, x and y, and I am trying find the f(T)=x and f(T)=y polynomial approximations, but I can't seem to find the option in Excel that will let me do this. I've been assured by some people that Excel has this option hidden away in it somewhere, but if it's somewhere then the help function is not finding it when seaching on "polynomial fit" or even just "polynomial"... Does anyone know how to do this? Kind regards, - Mike Kamermans Hi Mike, I use polynomials to achieve a curve fit on an x,y scatter chart with t...

automatic copy cells into other worksheet??
i am trying to find out how i can type text in an excel cell and have it automatically inserted in another worksheet. is this possible?? if so how?? cheers. Hiiiiiiii dear hope u are ok go the sheet2 cell A1 and type =Sheet1!A1 and Drag it and now go to sheet1 and type something in Cell A1 and A2 and A3 and then check them in sheet2 Colum u will find what u have written hope it may work for uuuuu good luck "Matt Hall" wrote: > i am trying to find out how i can type text in an excel cell and have it > automatically inserted in another worksheet. > is this possible?...

Excel Look up and Return Data
Hello all, I have a "data table" in one Excel file (call it DATA, the table is sorted on a field called 'CODE') and a second Excel file (call it FORMULA). I need to lookup information from the DATA table and return it to the FORMULA file, this will be done by matching a Raw Material Code number from the FORMULA file to the DATA table. When the code is matched, I would like to return 5 consecutive cells (in 5 consecutive columns) of data to the FORMULA file. Currently, I am using 5 vLookup functions to do this in the 5 consecutive cells. I am doing this to try to ...

Dummy series and data table
Hi, I have a chart that presents 2005, 2006, 2007 summary data as a column chart and then 2007 by month as a line. To show the yearly data I have a yearly category, after which I have individual months where the yearly data is zero - sort of like a dummy series - because I only have one value for them. The 2007 detailed data has zero in the yearly column but all the individual values in the monthly columns. It worked fine until I was asked to add a data table to the chart. Now, since it has 2007 twice - once as the summarized for the year and the other as all these individual months - some us...

Minimizing Rows of with similar data
I am using Excel 2007. I have 3 columns A, B, and C such as below: TU10-10 TU10 Tungsten Satin Wedding Band TU10-10.5 TU10 Tungsten Satin Wedding Band TU10-11 TU10 Tungsten Satin Wedding Band TU10-11.5 TU10 Tungsten Satin Wedding Band TU10-12 TU10 Tungsten Satin Wedding Band TU10-12.5 TU10 Tungsten Satin Wedding Band TU10-13 TU10 Tungsten Satin Wedding Band TU10-6 TU10 Tungsten Satin Wedding Band TU10-6.5 TU10 Tungsten Satin Wedding Band TU10-7 TU10 Tungsten Satin Wedding Band TU10-7.5 TU10 Tungsten Satin Wedding Band TU10-8 TU10 Tungsten Satin Wedding Band TU10-8.5 TU10 Tung...

Automatic Data Entry Script OnChange Event for Date Field.
Hi, Sub: Automatic Data Entry Script OnChange Event for Date Field. has anyone assigned 'Today date'( new Date()) to a custom date filed on the form - OnChange of a Picklist field, I have the following code. DatefiledName.value = new Date(); This assigns, currect date to the field, but then when I save it does not like it. When I manually assign a date it displays in the format mm/dd/yyyy, and saves correctly, so I changed the script to: d= new Date(); s = d.(getMonth() +1); s += '/' + d.getDate(); s += '/' + d.getFullYear(); DatefiledName.value = s; This also acc...

Data missing from enterprise custom task fields after migrating
We can see the enterprise task fields, but none of the data is displayed in files migrated from Project Server 2003 to 2007. Any ideas? -- michael Michael: What process did you follow for migration? -- Gary L. Chefetz, MVP, MCT, MCTIP Business: http://www.msprojectexperts.com FAQs: http://www.projectserverexperts.com BLOG: http://www.projectserverhelp.com "michaelj" <notreallyme.com> wrote in message news:4BD612D9-D1AD-4FF8-B103-699AB1B54D06@microsoft.com... > We can see the enterprise task fields, but none of the data is displayed > in > fil...

Data Import/ Data Map Questions
Hello, I am developing a solution that will allow various new and updated entities to be loaded from several source systems into a Microsoft Dynamics 4.0 installation on an ongoing basis. There are some up- front components to the solution that detect new/changed source system data that are custom but I wanted to be able to use the built-in Data Import facilities within 4.0 to take the data from Excel files and load it into the CRM. I've looked at the sample code in the SDK and have been able to write code that will do this, including use of Picklist and Lookup (ie parent account) type ...

Data Entry Problem
I am designing a database to manage staff absences. I have a number of forms (one for each manager) feeding one table for company reports etc. I have a 'member of staff' field that is a comby on each form to restrict entry for managers to their team members only. My problem is that i set the data entry property to 'Yes'. This clears the form on open but prevents me from finding records (obviously because the form is empty). If i set the data entry to 'No', all records from other managers are showing on all managers forms also. Due to data protection i need to ov...

How do you invert legend data?
My boss wants the legend inverted, instead of the 8% at the top, he'd like the 12% at the top and the 8% at the bottom. I switched the cells around thinking that it would switch the legend data as well, no go. Anyone have an idea? Hi, The legend order is dependent on 3 things. The series order the chart type of a series the axis upon which it is plotted If they series are all on the same axis and of the same chart type then have a look at the series order tab on the format dialog. Double click a series to display this dialog. Cheers Andy rainbowraven wrote: > My boss wants ...

Previous year data into chart
I currently have a whole table of data imported into Excel from Access. The only problem is, i cannot seem to import the data for the previous year using a query. With the data i would like to create a chart for the past 12 month, i.e the last 12 cells (as the data is taken monthly). Can i select data for the last 12 months that will remain the last 12 months even when the worksheet is refreshed every month? How my data looks: Date % Acknowledged % Investigated 09/2005 85 99 10/2005 ...