Copy named range, but skip blanks

My goal here is to copy "NewRecord" and paste values only in the first blank 
cell in column A on the Payment History sheet, while skipping blanks rows.

"NewRecord" has 22 rows with formulas in columns A, B & C.  Sometimes all 22 
rows will have data entered, sometimes only the 1st row. 

The marco works, but does not skip the blank rows in NewRecord - is it 
because there are formulas present?  How do I fix this? 

Also, how do I assure my macro is pasting on the first blank row of column 

Application.Goto Reference:="NewRecord"
    Sheets("Payment History").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
        :=True, Transpose:=False
End Sub

12/14/2009 5:52:01 PM
excel.programming 6508 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 42

LastRow = Sheets("Payment History").Range("A" &
NewRow = LastRow + 1

NumberofRows = Range("NewRecord").Rows.Count

For RowCount = 1 To NumberofRows
If Range("NewRecord").Offset(RowCount - 1, 0) _
.Resize(1, 1).Value <> "" Then

Sheets("Payment History").Range("A" & NewRow).PasteSpecial _
NewRow = NewRow + 1
End If

Next RowCount

joel's Profile: 229
View this thread:

[url=&quot;;]Microsoft Office Help[/url]

12/14/2009 6:34:50 PM

Similar Artilces:

Maianting copies of all emails
We wanted to maintain a copy of any incomming and outgoing emais hence we did it through our surfcontrol server. Now any email coming and leaving our system also gets stored on our specified location. The problem is that we cannot get a copy of any internal communications because internal communications do not go through surfcontrol and it gos directly through exchange. Is there a way in exchange where we can store a copy of all the internal emails as well? On Tue, 6 Jun 2006 06:11:02 -0700, Shaqs <> wrote: >We wanted to maintain a copy of any in...

What names should be on a SSL Cert for hub/cas 2010 using cas arra
Hi, I was just currious what names should be on a SAN Certificate for a couple cas hub servers that host a CASarray with NLB. Are just the external names sufficient or are all the internal ones neccesary as well? Such as (external casarray VIP and NLB IP) server1.domain.local server2.domain.local server1 server2 Thanks! I believe that the below names are enough for the SAN, you don't need internal certificate names if you will not assign it for SMTP service on the HUB servers. &...

Cut, Copy, Paste using Insert Key in Outlook 2007
Since I am left-handed and use the mouse with my left hand, I have grown accustom to using Ctrl-Ins for Copy, Shift-Ins for paste, and Shift-Delete for Cut. While in an opened message in Outlook 2007, I can use these key combinations (due to an editor option in mail format tab of the options dialog), but in the preview pane, I am not able to do it. Is there some way to enable these key combinations in the preview pane of Outlook 2007? cd <> wrote: > Since I am left-handed and use the mouse with my left hand, I have > grown accustom to using Ctrl-Ins for Copy, Sh...

How to transfer and copy Pictures from iPhone to computer?
[url=][b]How to transfer and copy Pictures from iPhone to computer with iPhone Tool Kits?[/b][/url]Do you want to copy Photos/Pictures from iPhone to computer for printting? [url=][b]iPhone Tool Kits[/b][/url] is an easy to use utility designed to help you back up all your files on your iPhone, so that you can recover any lost or missing songs, videos, books, photos and so on. It can easily [b]ba...

Worksheet has corrupt name after download of csv
I am offering visitors of a website the possibilty to download a csv formatted file of data. Visitors may normally choose to save the download or to open it directly with Excel. I have noticed that if they choose to open it, the Worksheet will have a name containing brackets, which is considered invalid by Excel and this prevents users to: 1) rename the woksheet 2) use the data in the sheet for reference form other sheets, for pivot tables, e.t.c. If the file is suqsequently saved, the worksheet is renamed and all works fine. Below is a sample that reproduces the behaviour: 1. Start VS.NET a...

Autosuggest doesn't retain names after restarting Outlook
Outlook2003 I recently had to reinstall Office. Outlook no longer retains names for autosuggest if I close out. For example, the first time I send an email to "Bill Smith" I must type the entire name. Second and subsequent times, Outlook autosuggests the name Bill Smith after I type "B." However, when I log off and on again, I must once again type the full name the first time. Thanks for any assistance! Erica <> wrote: > I recently had to reinstall Office. Outlook no longer retains names > for autosuggest if I close out. For examp...

We have an excel spreadsheet with names and addresses in columns On 3 different worksheets. Is there a way to have excel search the doc for same entire data. ex: Jane Doe Joe Davis Jane Doe _ Exel would see that and stop since there is a Jane Doe already entered? Please help I really don't want to ctrl -f 380 times to make sure.. Thanks to anyone that replies So you have some kind of cell that's used for data entry and you want to check if that name appears in any of those other 3 lists? If I used A1 on Sheet1 as my data entry cell, I could put this in B1: =if((countif(sheet2!a...

Outlook doesn't recognize name
outlook stopped sending. I get the message "Outlook does not recognize one or more names" Searched microsoft with no luck. anybody else seen this? ...

Fixed Range
Hi all, Looking for a formula to help on my problem: I have a worksheet with the totals in row 2. The data beneath that will be added to everyday with the most current day being place in row 3. I then need to the total in row 2 to just take the first 100 rows of data (B3:B102). If i use "absolute refs" and then insert a row, it will update the forumla as well. I can write a macro to do this, but was more curious about a formula based approach. Any help you can offer would be greatly appreciated. Cheers Dave =SUM(INDIRECT("b3:b102")) -- Don Guillett Microsoft MVP Exc...

Saved File Names
How can you stop excel from putting an extension on a wookbook file name Why would you want to? -- HTH RP (remove nothere from the email address if mailing direct) "puzzled" <> wrote in message > How can you stop excel from putting an extension on a wookbook file name ...

Removing Blank Rows #6
Using Excel 2002: This is probably one of the top ten most asked questions, and saw some related posts. But like to know, if there is a simple non-formula way of clearing blank rows in a list? Mark the cells, then just have a action to "Clear Blank Rows". Thanks, M Mi Mark Select column A >press F5>Special>select Banks>OK Whilst the selected cells are highlight>right click>Delete>Entire Row -- Regards Roger Govier "MarkC" <> wrote in message news:OCGBCOwsHHA.4968@TK2MSFTNGP06.phx.gbl... > Using Excel 2002: > ...

Thought this was all going, getting sucked down the plug hole. Where are all my favourite gurus going to now? Alt windows 98 ? Let me know. -- pebble in Boulder pebble wrote: > Thought this was all going, getting sucked down the plug hole. > Where are all my favourite gurus going to now? Alt windows 98 ? > Let me know. I strongly recommend you start reading, if not become a member of this web-forum: Otherwise, yes, the newsgroup alt.windows98 is a logical choice. Also be aware that June 1 was not...

An Array To Sort Blank Cell
I'd like to create an array to place values in column A into Column B and remove blank cell from column A. Example:data in column "A" has blank cell at A2, I'd like to have column "B" show the values and elimate the blank cell. A............B 4............4 blank.......5 5............6 6 Your assistance is appreciated. Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: View this thread:

Copy from worksheet 1 to worksheet 2, 3 , 4 etc..
Okay. I learned a thing or two but still not working for me. Sheet 1 looks like this Monday Tuesday Wednesday Etc... John Smith off 4am 4am Katie Jones 4am 4am 4am Dawn Moran 4am 4am off etc.... I would like sheet 2 (renamed "Monday") to list the names with "4am" under the column Monday. And not list the names with "off" under the Monday column. So "Monday" sheet should look someting like Schedule Katie Jones Dawn...

Pivot Table
I have a pivot table in Excel (2000) which is based on Access data. The problem is, it shows (Blank) where I would like it to show 0. I looked around in the preferences for the pivot table and found nothing to my advantage. I also browsed the forum, but only found ways to conditionally format, and also a suggestion to alter the source data. Anyone know of another way to do this? Altering the source data might be quite difficult (and would seem like a total workaround)! THANKS!! --- Message posted from Right click in the field and select Field from the dropd...

Copying sheets
Dear All, I have 3 worksheets for one week, these are 1) values, 2) summaries of values and 3) graphs of the same. I have copied this within the workbook so that I now have sheets for weeks 2, 3 and 4 and it has worked well except for the graphs. The values for the graphs still relate to the values from week 1. Why is this? Is there a way round it without redoing the data sources for each of the graphs? Thanks, Danny Hi have a look at -- Regards Frank Kabel Frankfurt, Germany "Aardvark" <djurmann(at)> sc...

multiple display names
I own two businesses. I have one e-mail address. Is it possible to change the display name on my outgoing e-mails without going into my account and changing names constantly? How about creating two profiles with two different names. The profiles will have the same pop3/imap/smtp details and login details but reply to etc will differ. You do, however, need to make sure that only one account downloads the messages. This might sound tricky but possible. "regina" <> wrote in message news:05B9847D-73D2-49E4-8083-8FD7AD321...

Email distributes copies to all many times
Hello, I have a new SBS 2003 server with Exchange installed. 5 people in my business got sent the same email from someout outside the company. Normally I would have thought this would just send one copy to each person in the To: list. It actually sent 5 copies to each of the people! And seems to do this everytime you send an email to more than one person. 3 people they get 3 copies!!! Any ideas why? or how to fix this? Thanks Are you using the POP connector? Jack D wrote: > Hello, I have a new SBS 2003 server with Exchange > installed. 5 people in my business got sent the sam...

Shape names question
Hi there, I am currently trying to pimp a presentation with a couple of VBA scripts. I am for instance importing png graphics and try to access those through my scripts. After playing with the macro recorder I was able to find out the name of those shapes in order to set properties or whatever. Slide108.Shapes("Picture 42").Visible = msoFalse. Now my question: Is there for mainly readability reasons a way to change those names? I could'nt find yet where to change this, but I believe this must be somewhere possible. Thanks for any hint, I am using XP SP3 and Offic...

copying a formula down a column
I am trying to copy a formula down a column where c1=a1,c2=a4,c3=a7 an so on but when Itry to drag the formula down the column it doesn't cop properly. Does anyone know a simple answer to this. Thanks in advance ----------------------------------------------- ~~ Message posted from ~~View and post usenet messages directly from Put in C1: =INDIRECT("A"&3*ROW()-2) Copy down col C -- hth Max ----------------------------------------- Please reply in newsgroup Use xdemechanik <at>yahoo<dot>com for email ----------...

Copy and Paste without copying Formulas
I have created a workbook with several sheets. The first sheet consists of a master list of 8000 numbers. The second sheet is where I paste a smaller list of numbers. The workbook performs a search and returns numbers that match on the third sheet. The third sheet also has formulas. I want to be able to copy and paste the resulting info on the third sheet to another workbook without copying any of the formulas......I just want the info. Any suggestions?? Ken Copy>Paste Special>Values>OK>Esc. Gord Dibben Excel MVP On Thu, 18 Nov 2004 14:21:13 -0800, "Ken"...

Copy unique data to a different WS
This is my situation any suggestion or help will be greatly appreciated. WorkSheet 1 Date Prod ID A bit more detail would help. Is this a one time operation, or something that needs to be live and linked in some way. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�------------------------------�---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------�------------------------------�---------------- "Sdbenn90"...

Outlook Express sends 2 or 4 or 6 copies of the same e-mail
My neighbor asked me to solve a problem with his computer - seems like Outlook Express (Ver 6.0) flashes an error message when he sends an e-mail. (Typical Microsoft error message in that it doesn't say what the error is) Then, the application sends 2 or 4 or 6 copies of the same e-mail. Any ideas? He also uses a laptop, and Outlook Express works as designed when sending e-mail from the laptop (through the desk-top router). I did remove (unsuccessfully) the Outlook components, rebooted and saw that Outlook was still there doing its' nasty thing of sending 6-8 e-mails. ...

MSN messenger won't change my display name..
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel hey <br> when i sign into mac messenger, my display name is my real name i told windows live. i change it. but then when i sign out and sign in again my display name is my real name again and my personal message is gone. any ideas? Nup! This is the Word group. We don't know anything about Messenger here. Post your question in the Messenger group. Cheers On 27/11/09 9:39 AM, in article 59badac7.-1@webcrossing.JaKIaxP2ac0, "" <just_mickey@officefo...

Copy data in multiple worksheets
How do I put the total of a row(column) of numbers from one worksheet to another worksheet? Mahalo. Karen kfletchb wrote: > How do I put the total of a row(column) of numbers from one worksheet to > another worksheet? > Mahalo. > Karen Use something like this: =SUM(Sheet1!B1:B25) Change the value "Sheet1" to the name of your worksheet Change the value "B1:B25" to the cells that you are adding DON'T FORGET THE EXCLAIMATION POINT AFTER THE WORKSHEET NAME great. thanx mark "Mark" wrote: > > kfletchb wrote: > > How do I put the ...