+ Reply to Thread
Results 1 to 4 of 4

Copy and paste two named ranges together.

  1. #1
    ACDenver
    Guest

    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.

  2. #2
    Dave Peterson
    Guest

    Re: Copy and paste two named ranges together.

    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

  3. #3
    ACDenver
    Guest

    Re: Copy and paste two named ranges together.

    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
    >


  4. #4
    Dave Peterson
    Guest

    Re: Copy and paste two named ranges together.

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1