Copy and paste two named ranges together.

[Guest]

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]

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

 

[Guest]

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]

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).