Newbie Question-Pasting Multi-Row Data into Accumulating List With Macro

I have a two page worksheet. On the first page, data is entered into a 7 
row by 6 column table.  Several computations are done on all the data 
and a weekly report can be printed.  I want to accumulate that weekly 
data in a list on the second page using a macro.  I have recorded a 
macro that will copy all the current week's entries from the first page 
and "paste special" the data values onto the second page with a single 
click on a button located on the first page.  What I can not figure out 
is how to paste the subsequent week's data below the previous set of 
data.  Because some of the computations are done for all the data, I can 
not copy/paste on a daily basis nor do I want any formulas on the second 
page.

I need to modify the recorded macro to somehow find the next blank cell 
in the "a" column and then paste all 7 rows at one time.  I'm a newbie 
on macros and VBA and this has my eyes crossed!

Thanks in advance for any advice.


0
tallen01 (2)
11/27/2006 1:50:13 PM
excel 39879 articles. 2 followers. Follow

3 Replies
603 Views

Similar Articles

[PageSpeed] 40

    Set start_range = Worksheets("Sheet2").Range("A1").End(xlDown)
    Worksheets("Sheet1").Range("A1:F7").Copy start_range

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"tallen" <tallen01@rochester.rr.com> wrote in message
news:p2Cah.26189$zB4.15230@twister.nyroc.rr.com...
> I have a two page worksheet. On the first page, data is entered into a 7
> row by 6 column table.  Several computations are done on all the data
> and a weekly report can be printed.  I want to accumulate that weekly
> data in a list on the second page using a macro.  I have recorded a
> macro that will copy all the current week's entries from the first page
> and "paste special" the data values onto the second page with a single
> click on a button located on the first page.  What I can not figure out
> is how to paste the subsequent week's data below the previous set of
> data.  Because some of the computations are done for all the data, I can
> not copy/paste on a daily basis nor do I want any formulas on the second
> page.
>
> I need to modify the recorded macro to somehow find the next blank cell
> in the "a" column and then paste all 7 rows at one time.  I'm a newbie
> on macros and VBA and this has my eyes crossed!
>
> Thanks in advance for any advice.
>
>


0
bob.NGs (282)
11/27/2006 1:55:58 PM
I bet Bob wanted to come down one more row:

    Set start_range = Worksheets("Sheet2").Range("A1").End(xlDown).offset(1,0)
    Worksheets("Sheet1").Range("A1:F7").Copy start_range

He started at the top and came down to find the next available cell.

Another way is to start at the bottom and come up:

dim DestCell as range
with worksheets("Sheet2")
  set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

whateverrangetocopy.copy _
  destination:=destcell



Bob Phillips wrote:
> 
>     Set start_range = Worksheets("Sheet2").Range("A1").End(xlDown)
>     Worksheets("Sheet1").Range("A1:F7").Copy start_range
> 
> --
> 
> HTH
> 
> Bob Phillips
> 
> (replace xxxx in the email address with gmail if mailing direct)
> 
> "tallen" <tallen01@rochester.rr.com> wrote in message
> news:p2Cah.26189$zB4.15230@twister.nyroc.rr.com...
> > I have a two page worksheet. On the first page, data is entered into a 7
> > row by 6 column table.  Several computations are done on all the data
> > and a weekly report can be printed.  I want to accumulate that weekly
> > data in a list on the second page using a macro.  I have recorded a
> > macro that will copy all the current week's entries from the first page
> > and "paste special" the data values onto the second page with a single
> > click on a button located on the first page.  What I can not figure out
> > is how to paste the subsequent week's data below the previous set of
> > data.  Because some of the computations are done for all the data, I can
> > not copy/paste on a daily basis nor do I want any formulas on the second
> > page.
> >
> > I need to modify the recorded macro to somehow find the next blank cell
> > in the "a" column and then paste all 7 rows at one time.  I'm a newbie
> > on macros and VBA and this has my eyes crossed!
> >
> > Thanks in advance for any advice.
> >
> >

-- 

Dave Peterson
0
petersod (12005)
11/27/2006 2:31:22 PM
tallen wrote:

> I have a two page worksheet...
>
>
Thanks guys.  I'll work on this today and see where it leads me.

T

0
tallen01 (2)
11/28/2006 12:51:35 PM
Reply:

Similar Artilces:

Awesome list of Excel/Word/Powerpoint shortcuts!
Hi, everybody! I recently discovered a great keyboard shortcut list for the office applications. It seems to have practically every shortcut imaginable and it fits on one page too! I printed it out and put on the wall next to my monitor -- just great!. Thought you would appreciate it... Here's the link to the PDF file. Hit print and you're ready to go. http://www.prihodko.com/msoffice/Office_2003_Shortcuts.pdf Lindy Hi Lindy, For Excel, I like Chip Pearson's Excel Keyboard Shortcuts page at: http://www.cpearson.com/excel/KeyboardShortcuts.htm There is also a d...

Hey Microsoft! Exchange 2003 Question
Can an Exchange administrator modify the Level1 and Level2 attachment lists? I don't want my Outlook users to have to save a certain type of file to the disk first. If so how? Thanks This may help: http://www.slipstick.com/outlook/esecup/admin.htm -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Jeff" <anonymous@discussions.microsoft.com> wrote in message news:0be901c3db86$5e56e700$a501280a@phx.gbl... > Can an Exchange administrator modify the Level1 and > Level2 attachment lists? > I don't want my Outlook users to ha...

Off topic: VB6 question
sorry this is way off topic. using _vb6_: i'm trying to add/replace nodes to an xml document via CreateTextNode: xml = "<item>floogle</item>" set newNode = oDom.createTextNode(xml) call dDom.replaceChild( newNode, bloogleNode) It works ok. But I can no longer find the new node in the document via an xpath query. When I save the doc to disk I can see the new node but its been escaped, presumably because it was a text node: <root> <item>wayheyhepahole</item>&lt;item&gt;floogle&lt;/item&gt; </root> I guess my question is: H...

Using insert to paste a row--how done in Excel 2007
Hi, In my old version of Excel, I could copy a row or chunk of rows, move to a new spot and use the "insert row" icon to insert the rows and paste it automatically. Now in Office 2007 it just inserts a row instead of what I have copied. I want it the old way! How do I do it? -- Thanks, PTweety R-click, Insert Copied Cells. pickytweety wrote: > Hi, > In my old version of Excel, I could copy a row or chunk of rows, move to a > new spot and use the "insert row" icon to insert the rows and paste it > automatically. Now in Office 2007 it just inserts a r...

CRM 4.0 Report Deployment
Hi When I deploy a custom report (through Web UI) and run it, no data is displayed. The report header, footer, etc, display, but there is no data. To simplify the problem I created a very simple report with no parameters, which selects all from filteredsystemuser, and the problem was the same. This is in a test VPC, the whole environment is contained in the VPC. I am developing/publishing/viewing as the CRM administrator. The steps I took were: - Created a new report in Business Intelligence Development Studio using Wizard (installed in VPC) - Created a new DataSet called dsTest with a new D...

Button/Macro Not Working, Help!
I have a macro that pulls up Report Manager and prints out pre-define views. The macro works fine only after I use Report Manager the firs time. If I exit my workbook and Excel, then return to it, th button/macro does not function. Nothing happens until I manully us Report Manager again. Is there a bug with this or am I missin something -- Message posted from http://www.ExcelForum.com Post your code. Gord Dibben Excel MVP On Thu, 20 May 2004 08:38:55 -0500, baston <<baston.16k4at@excelforum-nospam.com>> wrote: >I have a macro that pulls up Report Manager and prints out pre...

Why does workbook think deleted macro still exits?
I have deleted the macros in a workbook but each time I open it there is still a messge stating there are macros. How do I stop the message from appearing? Thanks Karen ...

Can I make a database in Word with Excel Data
I know this may be a "DUH" question, but... I am making a list of area dentists that I have copied and pasted of of the Dex website into Excel. I am wondering if I can take this inf and make a database in Word for printing address labels. Does anyon know if this can be done, and HOW?:confused -- Myn77 ----------------------------------------------------------------------- Myn777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2958 View this thread: http://www.excelforum.com/showthread.php?threadid=49283 For help on Word mail merge using Excel as the...

How do I alpha my mailing list in Microsoft excel and delete names
To alphabetize -- use sort Sorting, Some notes on sorting in Excel http://www.mvps.org/dmcritchie/excel/sorting.htm To delete rows pick a range of rows by selecting one or more contiguous row number(s) then Edit, Delete rows You might also be interested in Filtering to expose only names in Ohio You can use Filtering to permanently delete rows or just hide them. you can read more about filtering at http://www.contextures.com/tiptech.html If you want to print labels for your mailing list, you do the printing in MS Word with Excel acting as your data bas...

Personal Macro Workbook 12-15-09
Hi, The personal macro workbook doesnt start up automatically in excel 2007. How should i get it to start automatically when excel starts up? Its driving me mad to find out where the personal macro workbook is stored in all honesty. Hi, If you did a 'standard' installation you should find personal.xlsb by looking in C:\program files\microsoft office\office 12\xlstart If it's not there then you don't have one. To create it record a simple macro saved to the personal macro workbook and then it should load every time you start the application. Mike "...

hide Admin from address list
Hi Exchange server 2003 (SBS2003) how do I hide / remove the administrators mail box address from all lists to stop people sending emails to it when they select all domain users? thanks Adrian wrote: > Hi > Exchange server 2003 (SBS2003) how do I hide / remove the > administrators mail box address from all lists to stop people sending > emails to it when they select all domain users? > > thanks Well, you can control who can send email to that mailbox in the delivery restrictions section (ADUC properties of the admin acct, exchange general tab). Would that not suffice?...

Product list in CRM v.1.2
Hi all, i have another question concerning the product list, can i attach an excle sheet that contains my products instead of loging each product one by one as i have over 25,000 products to log? -- Regards, Marwa I'm afraid not. This is a major pain point for a lot of people and I do not kno of any 3rd party add-ons that have addressed it either. -- Matt Parks MVP - Microsoft CRM "marwa" <marwa@discussions.microsoft.com> wrote in message news:C062CF1E-EEF8-43B4-AC31-F299BF707252@microsoft.com... Hi all, i have another question concerning the product list, can ...

data validation
Is it possible to create a similar function like data validation, from which I can select multiple options? (Background: I need a list of many names, from which I case by case can select). Not really. Maybe you could use a listbox. I added a listbox from the Forms toolbar to a worksheet. I called it "List Box 1". I rightclicked on that listbox and chose Format Control. On the Control tab, I chose Selection Type of Multi. I also pointed at the range that held the values that go in that listbox. Then I added a button from the Forms toolbar (I click the button when I'm d...

Newbie: can't get a calculated value on the form?
I have a table with numbers and a form that shows the numbers. I have a query that takes one of the numbers and mulitplies it. I put a text box on the form from the query result field, but I get a "#Name" error instead of the result. When I run the query, I get the correct result. Help, please? Ed "Ed from AZ" <prof_ofwhat@yahoo.com> wrote in message news:d1e7d27c-11d9-4696-8d19-4c5fdd9dbb89@d70g2000hsb.googlegroups.com... >I have a table with numbers and a form that shows the numbers. I have > a query that takes one of the numbers and mulitplies it. I p...

Updateing data when closing forms
I am using MS Access 2000 I have a table that has several required fields. I developed a form to add records to the table. In addition to the table fields, the form has a form-close button constructed with the button wizard. When I enter a record that does not have the required information and close the form with the “Close Window” (X) button on the tool bar, the appropriate error message appears telling me that there is missing data. (Just what I want) When I use the close button on the form, the form closes with no message and without adding the record. How do I give the button on the f...

Graph Data Values
I am trying to create a pick and mix graph that shows forecast spen against actual spend by a selected business area. I somehow want to create a graph on the fly based on the selections fo example: If I choose company one and want to see the contracted data I want th graph to pick up the forcasted contracted Labour, Passthru and Othe and show the combined contracted actuals; See attached; and if i then choose company 2 then graph will pick up the releven ranges? HELP!! Attachment filename: help.zip Download attachment: http://www.excelforum.com/attachmen...

generate list of non-entries within dates
I use Excel (2007) to record activity and support for about 100 current online learners and I have to provide an activity summary sheet every two weeks. The list of learners is in one sheet and are marked "current", agreed break" or "completed" in an adjacent column. The total list is about 1000 and grows by about 10 each week. The activity log is another sheet of the same workbook. Assuming that all activity is logged in this way I need to generate a list (sheet) of those current learners who have not submitted any recent work and are therefore "inacti...

How do I import data from a SECURED website into Excel?
I can import data from a regular web page directly into Excel spreadsheet, but when I tried importing them from a secured site I encountered problems. Does anyone have any suggestions? ...

Counting Question #3
I have three columns: dates, values, and names 10/17 $300 Jim 10/17 $300 Jim 10/17 $200 Tom 10/17 $100 Jim When I enter Jim and $300 in to two separate cells, in a third cell I want to count the number of "days" it applies to (all the way down the spreadsheet.) So in other words, there are two instances in the same day of Jim and $300, but since it all happened on one day, the answer would be one. Hope I explained it well. =sumproduct(--(b1:b10=x1),--(c1:c10=x2)) where x1 holds the amount, x2 holds the name and B1:B10 holds the list of amounts and c1:c10 holds the list of...

Transfer Macro in Excel
I am trying to transfer informtion in one sheet to another sheet in the same workbook. I have a macro to process this information from the first sheet to the second. I keep getting an error in this Process macro at the line. Application.Goto REFERENCE:=Range("END") The first sheet is called ProblemSheet and the second sheet is called Processed. The line above is taken from the process macro on the ProblemSheet page. Do I have to name a certain cell as "END" on either sheet? Any help with this would be really appreciated. --- Message posted from http://www.ExcelForum.com...

Lookup Wizard changing Data Type
I am trying to make Lookups from Table Field to Table Field and generally it seems OK. However the LookupWizard is changing the Data Type from Text to Number (I guess it's looking at the ID?) Data picked from Combo Box 'looks' OK in Table view A Query view is asking for a number but displays the Text field value when a valid ID number is added. Viewing Results in FrontPage Database Wizard, and using hand coded .asp querie, it's showing the ID field value. I can see why... Any ideas? Merci. Yes, the wizard isn't the problem it's the use of Lookup Fields in ...

retrieving data from Great Plains via eConnect
Hi, I've been working with eConnect for the last two weeks and have had success with importing a variety of transactions into Great Plains. However, I'd like to query Great Plains on demand and have data returned to me. For example, given a certain field value (like customer name = "Smith"), I'd like to have all customer records returned that match that specified value. Is there any way to do this easily with eConnect? I'm not so interested in the Transaction Requester Service because that seems to be focused more on automatically returning records that have...

Global Address List #16
This is a brand new Windows 2003 SBS Exchange 2003 install. I have created a separate OU and added a few users to that OU. From Outlook 2003, the GAL only shows the administrator and 1 of the default SBS groups. However, when 'All Users' is selected from the drop-down menu, the other users appear. I have checked the preview for both the GAL and All Address lists filter in System Manager and all of the appropriate users appear. Shouldn't they all appear on the GAL by default? "Jorge Azcuy" <anonymous@discussions.microsoft.com> wrote: >This is a brand ne...

Backup service authentication question
Hello, I have a fairly small Exchange 2003 installation. My HQ site has a single Exchange 2k3 BE server. The HQ site is connected to 3 other remote sites via a mesh network. Each of those sites has a local Exchange 2k3 BE server. I use BackupExec 11d for my backups. I recently noticed that if my Exchange server in the HQ site is unavailable, the Exchange backups in the remote sites fail in connecting to the resource. It appears like an authentication issue. When the main site comes back on line, backups can run again without issue. Each site has at least one 2k3 domain controlle...

Keeping a range constant when inserting rows
Hello, I'm trying to keep a range of cells constant within a function when I insert a row (e.g. average(a1:a6) becomes average(a1:a7) but I want it to keep the a1:a6 range). Even if I use absolute cell references ($a$1:$a$6), it doesn't help. I would greatly appreciate any ideas. Thanks, Jeff Jeff, In your formula, use: =AVERAGE(INDIRECT("A1:A6")) Absolute cell references (dollar signs) do one thing only: They keep any copies you make of the cell references from changing relatively as they're copied. They still change when the cells to which they refer are m...