copy data to another sheet

[Patch]

Hi -

My VBA knowledge is very limited. I pretty much depend on
the macro recorder to do my dirty work and I know that
it's very inefficient. Here's what I want to do -

I have a template sheet that gets filled in with data,
both numbers and text. I want to copy certain bits of this
data and paste it into a different workbook which I use as
a summary.

The data I want to copy/paste is scattered throughout the
template and is for the most part in non-contigious blocks.
I can handle the copy operation but the problem comes when
I need to define the destination. I want the data to be
pasted in the next MT row of the summary sheet in a
different workbook. If it wasn't for the last row bit I
could just use a formula cell to cell link like =Template!
A10.

Say for example I want to copy from the template sheet
cells - A10,A11,E22,F22,G22,H22,K60,L60,M60 and have that
pasted into the summary workbook on the next MT row, which
might be row 12, in the range A12:I12.

I also would like a msg box that pops up and confirms that
the copy/paste was successful.

I can handle creating a button and assigning the macro to
it.

Any help will be greatly appreciated.

Patch

 

[Tom Ogilvy]

Using cute notation like MT to say empty just makes it less likely your post
will be answered. You interest is best served by clearly, simply and
completely stating your problem.

Here is one possible interpretation:

Sub Tester9()
Dim rng As Range, rng1 As Range
Set rng1 = Worksheets("Summary") _
.Cells(Rows.Count, 1).End(xlUp)
Set rng = Range("A10,A11,E22,F22,G22,H22,K60,L60,M60")

i = 0
For Each cell In rng
cell.Copy Destination:=rng1.Offset(0, i)
i = i + 1
Next
MsgBox "Pasted at " & cell.Address
End Sub

 

[Patch]

If you thought it was so "cute" then why did you bother to
reply?

Thanks anyhow!

 

[Tom Ogilvy]

There was something offensive in trying to help you improve the quality of
your posts and make answering your questions easier?

 

[Patch]

Where I come from MT is THE common notation for empty. We
don't consider it "cute".

Just trying to help you with the quailty of your replies!

 

[Tom Ogilvy]

Your not there now, are you?

Your here looking for help. Empty is the most common notation I am aware of
for empty. Remember, this is a worldwide forum. Many of the frequent
responders here don't speak English as a first language. But I guess you
will have some cute response to that as well.

 

[Patch]

Helping is one thing, being a condescending jerk is
another.

But I guess they're not mutually exclusive.

 

[Vasant Nanavati]

OK ... I can see you're going to be real popular around here.

I personally had no idea what MT meant, so I don't know where you come from
where you claim it is common usage. Tom pointed out to you that you would
lose possible replies from people such as myself who didn't understand your
post. And he did give you an answer.

So if you're going to be ultra-sensitive to criticism, perhaps this is the
wrong place for you to hang out. Even the veterans and MVPs get criticized
here; no reason why you should be excepted.

Hope this helps!

 

[Patch]

You're point is valid, however, had he phrased it without
describing it as "cute", it it would have been accepted as
a suggestion rather than a "put down".

BTW, the code has an error in it. I fixed it!

I really do appreciate the time and effort! (believe it or
not!)

 

[Vasant Nanavati]

Fair enough.

We're actually pretty easygoing around here, even if not always as tactful
as you might want. If you frequent some of the other newsgroups, you'll see
how pleasant we are by comparison!

 

[Tom Ogilvy]

Actually there were two problems with the code

Sub Tester9()
Dim rng As Range, rng1 As Range
Dim cell As Range, i As Long
Set rng1 = Worksheets("Summary") _
.Cells(Rows.Count, 1).End(xlUp)(2)
Set rng = Range("A10,A11,E22,F22,G22,H22,K60,L60,M60")

i = 0
For Each cell In rng
cell.Copy Destination:=rng1.Offset(0, i)
i = i + 1
Next
MsgBox "Pasted at " & rng1.Address
End Sub


is the corrected version. my appologies for not thoroughly testing it.
Seemed to work OK for me.

 

[Amedee Van Gasse]

Hi -

My VBA knowledge is very limited. I pretty much depend on
the macro recorder to do my dirty work and I know that
it's very inefficient. Here's what I want to do -

I have a template sheet that gets filled in with data,
both numbers and text. I want to copy certain bits of this
data and paste it into a different workbook which I use as
a summary.

The data I want to copy/paste is scattered throughout the
template and is for the most part in non-contigious blocks.
I can handle the copy operation but the problem comes when
I need to define the destination. I want the data to be
pasted in the next MT row of the summary sheet in a
different workbook. If it wasn't for the last row bit I
could just use a formula cell to cell link like =Template!
A10.

Say for example I want to copy from the template sheet
cells - A10,A11,E22,F22,G22,H22,K60,L60,M60 and have that
pasted into the summary workbook on the next MT row, which
might be row 12, in the range A12:I12.

I also would like a msg box that pops up and confirms that
the copy/paste was successful.

I can handle creating a button and assigning the macro to
it.

Any help will be greatly appreciated.

Patch

I want to help you, but what is an "MT" row? Is that a row with the
string "MT" in the first column or in another column?
It might help if you paste a small piece of example data here.

 

[Amedee Van Gasse]

Using cute notation like MT to say empty just makes it less likely
your post will be answered. You interest is best served by clearly,
simply and completely stating your problem.

Oops... hadn't read this yet. So "MT" is the official abbreviation for
"empty" in English? I did not know that. In Dutch "MT" sounds like
"em-tay" and not "em-tee" so I didn't see the connection. Also we Dutch
speakers have the nasty habit of reading every letter that is written.
So if you ever hear someone say the word "empty" with the "p" not
silent, then you know he's either from Belgium or from the Netherlands.

 

[Patch]

Thanks for the help!

-----Original Message-----
Actually there were two problems with the code

Sub Tester9()
Dim rng As Range, rng1 As Range
Dim cell As Range, i As Long
Set rng1 = Worksheets("Summary") _
.Cells(Rows.Count, 1).End(xlUp)(2)
Set rng = Range("A10,A11,E22,F22,G22,H22,K60,L60,M60")

i = 0
For Each cell In rng
cell.Copy Destination:=rng1.Offset(0, i)
i = i + 1
Next
MsgBox "Pasted at " & rng1.Address
End Sub


is the corrected version. my appologies for not thoroughly testing it.
Seemed to work OK for me.

--
Regards,
Tom Ogilvy




.