Copy and paste two named ranges together.

I am attempting to copy and combine two named ranges of equal size into a 
blank spreadsheet.  How does one copy the first named range and concurrently 
seperate each copied row with a blank row into the blank spreadsheet, and 
copy the second range and paste those copied records into the blank rows?   I 
am looking for a systematic way of doing this consolidation.  There can be 
hundreds of rows of data.  Also the named ranges can very in size month to 
month.

The end result is to combine two ranges for a journal entry upload into a 
financial accounting entry.  The two arrays represent the debit and credit 
side.  

At the moment I don't have extensive visual basic, nor macro writing skills.

Thank you.
0
ACDenver (6)
10/20/2005 6:33:06 PM
excel 39879 articles. 2 followers. Follow

3 Replies
844 Views

Similar Articles

[PageSpeed] 35

I like to pick out a column that always has data, then use that to find that
last used row.

Then drop down 1 (or 2) rows before the next paste.

dim rng1 as range
dim rng2 as range
dim destcell as range
dim newwks as range

with activeworkbook.worksheets("Sheet1")
  set rng1 = .range("range1")
  set rng2 = .range("range2")
end with

set newwks = workbooks.add(1).worksheets(1)
set destcell = newwks.range("a1")

rng1.copy _
 destination:=destcell

with newwks
  set destcell = .cells(.rows.count,"A").end(xlup).offset(2,0)
end with

rng2.copy _
 destination:=destcell

================

But you could just depend on the number of rows in each range.

dim rng1 as range
dim rng2 as range
dim destcell as range
dim newwks as range

with activeworkbook.worksheets("Sheet1")
  set rng1 = .range("range1")
  set rng2 = .range("range2")
end with

set newwks = workbooks.add(1).worksheets(1)
set destcell = newwks.range("a1")

rng1.copy _
 destination:=destcell

set destcell = destcell.offset(rng1.rows.count+2,0)

rng2.copy _
 destination:=destcell


ACDenver wrote:
> 
> I am attempting to copy and combine two named ranges of equal size into a
> blank spreadsheet.  How does one copy the first named range and concurrently
> seperate each copied row with a blank row into the blank spreadsheet, and
> copy the second range and paste those copied records into the blank rows?   I
> am looking for a systematic way of doing this consolidation.  There can be
> hundreds of rows of data.  Also the named ranges can very in size month to
> month.
> 
> The end result is to combine two ranges for a journal entry upload into a
> financial accounting entry.  The two arrays represent the debit and credit
> side.
> 
> At the moment I don't have extensive visual basic, nor macro writing skills.
> 
> Thank you.

-- 

Dave Peterson
0
petersod (12005)
10/20/2005 6:51:48 PM
Hi Dave,

I appreciate the prompt response.  But I need to re-clarify my commentary.  
I have "no" Visual Basic writing skills.  Can you state what you stated in 
the response in excel layspeak?  Or is my request only able to be 
administered in VB code?

Sorry for not being clear.

"Dave Peterson" wrote:

> I like to pick out a column that always has data, then use that to find that
> last used row.
> 
> Then drop down 1 (or 2) rows before the next paste.
> 
> dim rng1 as range
> dim rng2 as range
> dim destcell as range
> dim newwks as range
> 
> with activeworkbook.worksheets("Sheet1")
>   set rng1 = .range("range1")
>   set rng2 = .range("range2")
> end with
> 
> set newwks = workbooks.add(1).worksheets(1)
> set destcell = newwks.range("a1")
> 
> rng1.copy _
>  destination:=destcell
> 
> with newwks
>   set destcell = .cells(.rows.count,"A").end(xlup).offset(2,0)
> end with
> 
> rng2.copy _
>  destination:=destcell
> 
> ================
> 
> But you could just depend on the number of rows in each range.
> 
> dim rng1 as range
> dim rng2 as range
> dim destcell as range
> dim newwks as range
> 
> with activeworkbook.worksheets("Sheet1")
>   set rng1 = .range("range1")
>   set rng2 = .range("range2")
> end with
> 
> set newwks = workbooks.add(1).worksheets(1)
> set destcell = newwks.range("a1")
> 
> rng1.copy _
>  destination:=destcell
> 
> set destcell = destcell.offset(rng1.rows.count+2,0)
> 
> rng2.copy _
>  destination:=destcell
> 
> 
> ACDenver wrote:
> > 
> > I am attempting to copy and combine two named ranges of equal size into a
> > blank spreadsheet.  How does one copy the first named range and concurrently
> > seperate each copied row with a blank row into the blank spreadsheet, and
> > copy the second range and paste those copied records into the blank rows?   I
> > am looking for a systematic way of doing this consolidation.  There can be
> > hundreds of rows of data.  Also the named ranges can very in size month to
> > month.
> > 
> > The end result is to combine two ranges for a journal entry upload into a
> > financial accounting entry.  The two arrays represent the debit and credit
> > side.
> > 
> > At the moment I don't have extensive visual basic, nor macro writing skills.
> > 
> > Thank you.
> 
> -- 
> 
> Dave Peterson
> 
0
ACDenver (6)
10/20/2005 8:28:04 PM
One of the reasons to learn about macros is to automate repetitive tasks.  And
it sure sounds like this would qualify as repetitive.

You should take a look at David McRitchie's notes before you do too much
more--just to get a bit of a background:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Then you could create a new workbook and put this code into a General module of
that workbook's project.  
(David's notes should help you understand some of that sentence!)



Option Explicit
sub Copy2Ranges()

'declare some variables so the program knows how to handle our data
dim rng1 as range
dim rng2 as range
dim destcell as range
dim newwks as range

'You didn't say where the ranges were located.  I'm changing this
'to point at the active worksheet.
'and you didn't say what the names of the ranges were--so I guessed:
' range1 and range2
with activesheet
  set rng1 = .range("range1")
  set rng2 = .range("range2")
end with

'I wanted a worksheet in a new workbook--so the code created a new workbook
'with one worksheet 
set newwks = workbooks.add(1).worksheets(1)

'going to paste in A1 first
set destcell = newwks.range("a1")

'copy that first range!
rng1.copy _
 destination:=destcell

'come down 2 rows after the paste and get ready for
'the next paste
set destcell = destcell.offset(rng1.rows.count+2,0)

'do that paste
rng2.copy _
 destination:=destcell

'get rid of those dancing ants around the copied range
application.cutcopymode = false

End Sub

The bad news is you're gonna have a few more questions when you try this. 
When/if you post back, try to be a little more specific about what things are
(range names, workbook names, and that kind of thing).


ACDenver wrote:
> 
> Hi Dave,
> 
> I appreciate the prompt response.  But I need to re-clarify my commentary.
> I have "no" Visual Basic writing skills.  Can you state what you stated in
> the response in excel layspeak?  Or is my request only able to be
> administered in VB code?
> 
> Sorry for not being clear.
> 
> "Dave Peterson" wrote:
> 
> > I like to pick out a column that always has data, then use that to find that
> > last used row.
> >
> > Then drop down 1 (or 2) rows before the next paste.
> >
> > dim rng1 as range
> > dim rng2 as range
> > dim destcell as range
> > dim newwks as range
> >
> > with activeworkbook.worksheets("Sheet1")
> >   set rng1 = .range("range1")
> >   set rng2 = .range("range2")
> > end with
> >
> > set newwks = workbooks.add(1).worksheets(1)
> > set destcell = newwks.range("a1")
> >
> > rng1.copy _
> >  destination:=destcell
> >
> > with newwks
> >   set destcell = .cells(.rows.count,"A").end(xlup).offset(2,0)
> > end with
> >
> > rng2.copy _
> >  destination:=destcell
> >
> > ================
> >
> > But you could just depend on the number of rows in each range.
> >
> > dim rng1 as range
> > dim rng2 as range
> > dim destcell as range
> > dim newwks as range
> >
> > with activeworkbook.worksheets("Sheet1")
> >   set rng1 = .range("range1")
> >   set rng2 = .range("range2")
> > end with
> >
> > set newwks = workbooks.add(1).worksheets(1)
> > set destcell = newwks.range("a1")
> >
> > rng1.copy _
> >  destination:=destcell
> >
> > set destcell = destcell.offset(rng1.rows.count+2,0)
> >
> > rng2.copy _
> >  destination:=destcell
> >
> >
> > ACDenver wrote:
> > >
> > > I am attempting to copy and combine two named ranges of equal size into a
> > > blank spreadsheet.  How does one copy the first named range and concurrently
> > > seperate each copied row with a blank row into the blank spreadsheet, and
> > > copy the second range and paste those copied records into the blank rows?   I
> > > am looking for a systematic way of doing this consolidation.  There can be
> > > hundreds of rows of data.  Also the named ranges can very in size month to
> > > month.
> > >
> > > The end result is to combine two ranges for a journal entry upload into a
> > > financial accounting entry.  The two arrays represent the debit and credit
> > > side.
> > >
> > > At the moment I don't have extensive visual basic, nor macro writing skills.
> > >
> > > Thank you.
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12005)
10/20/2005 10:36:20 PM
Reply:

Similar Artilces:

Name Matching
Hi when an email is sent to a miss-spelled address, how do I route these emails to a postmaster or admin account? Thanks Matt What version of Exchange? -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchange.co.uk "Matt" <mattremoveme@edirect.co.uk> wrote in message news:006001c3b506$6c6670f0$a301280a@phx.gbl... > Hi > > when an email is sent to a miss-spelled address, how do I > route these emails to a postmaster or admin account? > > Thanks > Matt Exchange Server 2003 Running on Windows Server 2003 >----...

Validate almost one of two textbox
Hi, how can I validate a group of 2 textbox so the users have to populate at least one of them (or both)? Thanks in advance. Luis On Nov 27, 2:16=A0pm, Luigi <Lu...@discussions.microsoft.com> wrote: > Hi, > how can I validate a group of 2 textbox so the users have to populate at > least one of them (or both)? > > Thanks in advance. > > Luis Use Javascript Function function jsValidateTextBoxGroup(sTxtBox1, sTxtBox2) { var bSuccess =3D true; var sTxtBox1Value =3D document.getElementById(sTxtBox1).value; var sTxtBox2Value =3D document.g...

"Paste Special" with the right click mouse
I would like to have the option of "Paste Special" with the right click mouse in all microsoft office suite. Thanks, ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=de847738-df56-4...

range of chart
If I am trying to chart information related to individual's payroll in comparison to total pay roll, how do I select the correct range to have the names show along with the value ranges? Tracey - Do you want the names ass data labels, linked to the points? If so, use one of these free utilities to add text from cells to the chart: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Tracey wrote: > If I am trying to chart...

Cannot enable Blind copy option (BCC)
Have this option on my computer at work- use it all of the time. I have outlook 2002 and no matter what I do I cannot see the BCC button- it is not in the view section- it is not in the options section- it is not anywhere. It is almost annoying enough to make me switch to eudora. Why make it so i can't find or use it? Please help When in Outlook XP (2002) I open a new mail message... In the menu of that new message click VIEW There is an option to check "BCC Field" - check it. Enjoy, Terry "kentg@earthlink.net" <anonymous@discussions.microsoft.com>...

Table name length in microsoft query
Hi! Is it true that the maximum length of a tablename is still 8 characters. I'm useing Excel 97 and trying to get data from a Paradox 9 table via odbc-link and Microsoft Query. I have heard a few years ago that long names are OK and supported all over the microsoftian regime. Is there any trick to avoid this without shortening all the names. Greetings from Helsinki HK ...

proper name conversion
I have a database that has names of people in one field, in a last name, first name order. (example: Smith, John) How can i seperate the last and first names from one text field into two different fields? Or at least have only the last name. thanks for any help. Howard wrote: >I have a database that has names of people in one field, >in a last name, first name order. (example: Smith, John) >How can i seperate the last and first names from one text >field into two different fields? Or at least have only >the last name. thanks for any help. > > This is air ...

Is it possible to log the user's IP address or computer name?
We have Exchange 5.5 here and some users know other's email password. Is there anyway to log the machine IP address since under the property store you can only see the last logon with the user. Can't tell anything if multiple users sign on simultaneously? Lisa ...

Copying Data From SQL Into Excel
When I copy a range of data from SQL and paste it into Excel, the data doesn't appear to be available for formulas - in this case a VLOOKUP formula. However, once I click in the formula bar (as if to edit the data) then hit "return" the data is "magically" available for the VLOOKUP formula. It seems to me to be a format problem, but changing the format of the data doesn't help. Could this be related to similar issues when copying data from Access into Excel? Are there any workarounds? It sounds similar to the Access problem. Instead of pasting, you can ...

how do i search for a specific name in an excel file
i have a large file, (49,000+ names) is it possible to search for a specific name somehow without scrolling up and down the list repeatedly looking for it? Hit CTRL+F to open the find window, and in Look in: select Values. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261 View this thread: http://www.excelforum.com/showthread.php?threadid=496546 Did you try Edit > Find ? "cljenkins" wrote: > i have a large file, (49,000+ names) is it...

Linking Range
If I have a source sheet1 and this same range is linked to sheet 2, why if I insert a line on my source data no line is inserted on the link data. How can link two ranges so that if I insert a row on the one a row will also be inserted on the orter with links to the inserted rows? I'm getting dizzy with that. Don't know why you would think linking sheets together should handle inserting rows automatically. Group your sheets. Then when you insert a row on the one you see the other sheets in the group will receive the same treatment as if you had done the same keyboard instruct...

SUMIF with two conditions ? #2
I have 3 colums colum A G7705 colum B 300 colum C Units I need sum the units in colum C if colum A & B = "G77053000" Can somone Help =SUMPRODUCT(--(A1:A10="G7705"),--(B1:B10=300),C1:C10) Regards, Peo Sjoblom "Mestrella31" wrote: > I have 3 colums > > colum A G7705 > colum B 300 > colum C Units > > I need sum the units in colum C if colum A & B = "G77053000" > > Can somone Help Ok I did that & it worked, now when I try to get the data from another worksheet it gives me wrong data, do you know why? ...

copy and match data from one worksheet into another via script?
Hi all I have two worksheets 2 columns each The first sheet has ID and Name Column for example: Code ------------------- ID[/B] NAM 193948 Michael 684588 John 535279 Luke 098734 Matthe ------------------- my 2nd sheet Code ------------------- ID [B]NAM Michael John Luke Matthe ------------------- How do i use the first sheet to match up the names on the 2nd sheet and ultimately copy the ID numbers onto the 2nd sheet? This is just a simplified example. There are thousands of entries. Thank you if you have ...

Strange problem with some contact names / email senders
Hello! I have a strange Outlook problem which is really annyoing me at the moment! I added someone to my contacts but whenever i reply to their email they're name isn't automatically filled in as it does with other people's email. Also, if i Create a New email message, it never auto-completes their name. (ie if info@babylon-webmaster.fsnet.co.uk is in my contacts under 'Babylon', sends me an email and i reply to it "To: " reads "Babylon <info@babylon- webmaster.fsnet.co.uk>", but for this contact "To:" only ever displays their ...

copy and paste into excel
I am trying to set up a sheet into which i can copy and paste various items into a group of cells? and upon being pasted into the group the item will be automatically shrunk to fit the size of the cells it was pasted into, rather than expanding them to fits its size. Any ideas anyone?????? Hi, try copy data and paste them like: Edit/Paste Special/Values. It does not change the size of the columns. Marian Hi, What about 'format cells' 'Alignment' and check "shrink to fit"? That is under Text Control - is that any use??? Cheers, Mark copying+pasting into cell...

Find the number of years between two dates
I have aroster of players with thier date of births. I would like to automatically add their ages in another column Try this Formula, assuming that the dob is in cell A1 =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" regards Mukesh "duke" wrote: > I have aroster of players with thier date of births. I would like to > automatically add their ages in another column On Fri, 9 Sep 2005 19:47:02 -0700, "duke" <duke@disc...

Copying sorksheet formatting
How do I copy worksheet formatting, inc. custom headers, footers, column & row formatting across all the worksheets in a workbook? Hi one way: formating them all at the same time: - group the sheets (hold down the SHIFT key while selecting the sheets) - apply the format to a cell, column, row -- Regards Frank Kabel Frankfurt, Germany driverdriver wrote: > How do I copy worksheet formatting, inc. custom headers, > footers, column & row formatting across all the worksheets > in a workbook? You can also select the entire sheet with CTRL+A and do Edit / Copy, then group yo...

Copy and Paste question
I have a macro that copies and pastes a large amount of data. After it has run I get the question: " There is a large amount of information on the clipboard. Do you want to [keep it]?" Could someone please tell me how I can avoid this question being asked as I never wish to keep the data? Many thanks Insert this line after the pasting is done, it will clear the clipboard: Application.CutCopyMode = False hth knut egil "Richard" <rgarwell@jaguar.invalid> skrev i melding news:bpi1ee$cf71@eccws12.dearborn.ford.com... > I have a macro that copies and pastes a l...

How do I look at two worksheets in one Excel workbook at same time
I know how to arrange windows to look at multiple workbooks. But I want to have two worksheets displayed at the same time from the same workbook. Is there a way to do that? Window - New Window this will create a second view of active workbook. Then you can do Window - Arrange, active workbook only. -- Best Regards, Luke M "PeoriaJean" <PeoriaJean@discussions.microsoft.com> wrote in message news:503A1BB9-2130-4346-A0AF-A461388397B2@microsoft.com... >I know how to arrange windows to look at multiple workbooks. But I want to > have two worksheets di...

How to fill cell with two combobox values?
I am looking for VB code for command button that fills two combo box values and a toggle value (Y/N) behind each other in one cell. Bart Excel 2003 ...

series name change
How can I change the name of "series" ? I added to the chart with straight line by highlight two numeric columns. I can create lines but the legend show series 3,4,5,6 !! Thanks Daniel On Tue, 14 Aug 2007, in microsoft.public.excel.charting, Daniel <Daniel@discussions.microsoft.com> said: >How can I change the name of "series" ? > >I added to the chart with straight line by highlight two numeric columns. >I can create lines but the legend show series 3,4,5,6 !! Choose menu Chart.. Source Data.. Series, and there will be a box for series name. You can ...

Count with two criteria
I am working in Excel 2003, I am trying to count with two criteria and multiple sheets. I am using this formula: =SUMPRODUCT(('Sum 1Q10'!A2:A4="BKD")*('Sum 1Q10'!F2:F4="Open")) my result is 0. It should be 1. Is there another way to do this? I really need help. Lisak- There are a couple of approaches to do this; I'll give you the one I use out of habit. The sumproduct conditions evaluate to true or false. You have to force them back to a numeric format. Some folks do that with a leading double negative on each condition. I tend to ...

Contact categories & Exchange 2003: do they work together?
In my Small Business Server environment, I want Exchange to manage the Master category list for Outlook. I have been unable to locate an appropriate setting for this. Does anyone have any ideas? By the way, My contacts are all located in a public folder. "Paul van Egmond" <paul@oenone.nl> wrote: >In my Small Business Server environment, I want Exchange >to manage the Master category list for Outlook. I have >been unable to locate an appropriate setting for this. >Does anyone have any ideas? By the way, My contacts are >all located in a public folder. ...

Can I remove blanks from a range without using sort?
I have a range of cells A1:a10, say, which obtain data from another source. Some of those cells a3, a6:a8, say, under certain conditions, will be blank. How do I reorder this range such that the cells containing information are listed together, removing the blanks? I want to do this using a formula, rather than filter or sort, as the data, and hence the blank cells, will change, and I want to perform analysis on the cells containing data. this is from one of the newsgroup correspondents use this code statement Range("a1:a10").SpecialCells(xlCellTypeBlanks).EntireRow.Del...

copy setup and master to new compan
Hi to all – is any idea how to copy all setup and master tables from the existing company to a new company when I have already have an existing data on same server using Great Plains 8.0 , SQL-2000 and complete module of Great plains. thanks Here is a KB article with instructions: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;872709 -- Victoria Yudin GP MVP "AFT" <AFT@discussions.microsoft.com> wrote in message news:5138A080-DC38-462C-8101-CE7D55915D0E@microsoft.com... > Hi to all - is any idea how to copy all setup and master tables from...