Copy and Paste Macro?

kskinne

Well-known Member

Joined

Feb 17, 2002

Messages

1,267

Office Version

1. 365

Platform

1. Windows

• Jul 22, 2003

• #2

specifically, which certain columns do you want to be copied down? do they contain formulas? this can easily be done, but need a little more information

thanks,
kevin

 

Upvote 0

Smitty

Legend

Joined

May 15, 2003

Messages

29,536

• Jul 22, 2003

• #3

This will take you to the next empty row starting from the active cell - It assumes that you have contighuous rows of data. (Credit to John Walkenbach):

Sub ActivateNextBlankDown()
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Hope that helps,

Smitty

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Jul 22, 2003

• #4

Hi OpenIntro:

Welcome to the Board!

If I have understood you correctly, you may find the following illustration of some interest ...

Book1
	A	B	C	D	E	F	G	H	I
4485	1
4486	1		a
4487	2		b
4488	3		c
4489	4		d
4490	5
4491	6
4492	7			copyRange
4493	8			PasteToCell
4494	9
4495	10
4496	11
4497	12
4498	13
4499	14
4500	15
4501	a
4502	b
4503	c
4504	d
Sheet2

The Aqua colored cells represent the copyRange, and the Brown colored cell represents to PasteToCell -- then the following code should do what you are intending to do ...

Code:

Sub yCopyRangeAndPasteToFirstEmptyRowinColumnA()
    Set PasteTocell = [a1].End(xlDown)(2)
    Range("copyRange").Copy PasteTocell
End Sub

I hope this helps. If I have misunderstood your question -- my apologies!

 

Upvote 0

O

OpenIntro

New Member

Joined

Jul 22, 2003

Messages

37

• Aug 4, 2003

• #5

Thank you all for your replies. I apologize for it taking so long for me to reply back!

Yogi, I think that your interpretation is the closest to what I am looking for.

I have 4 rows (and multiple columns) of data that I want pasted in the NEXT empty rows upon the click of a button.

I am not sure what I am supposed to do with the brown and aqua colored cells? I am guessing I can "hide" the information that want copied and pasted on ANOTHER sheet, but I have not figured out how to work this into the macro.

Any other suggestions?

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Aug 4, 2003

• #6

Hi OpenIntro:

The brown colored cell is part of the range that has been copied. The color coding is simply to see what is being copied where. I do not see any problem there -- do you? Please post back with some specifics if you need to discuss this further.

 

Upvote 0

O

OpenIntro

New Member

Joined

Jul 22, 2003

Messages

37

• Aug 4, 2003

• #7

In your example though, in C4492 there is a brown cell and C4493 an aqua cell. Am I supposed to put something in these cells? Because the only thing being copied and pasted is C4486-C4489.

I guess I don't understand in the macro that you created, what the "copyRange" and "PasteToCell" parts do and what I need to change?

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Aug 4, 2003

• #8

OpenIntro said:

In your example though, in C4492 there is a brown cell and C4493 an aqua cell. Am I supposed to put something in these cells? Because the only thing being copied and pasted is C4486-C4489.

I guess I don't understand in the macro that you created, what the "copyRange" and "PasteToCell" parts do and what I need to change?

Click to expand...

Define the range C4486:4489 as CopyRange; cells C4492 and C4493 were used just to show you the color legend -- nothing has to be done to those cells.

I hope this helps.

 

Upvote 0

O

OpenIntro

New Member

Joined

Jul 22, 2003

Messages

37

• Aug 5, 2003

• #9

Yogi Anand said:

Define the range C4486:4489 as CopyRange

Click to expand...

Not trying to be difficult here, but how and where do I "define" the range?

 

Upvote 0

kskinne

Well-known Member

Joined

Feb 17, 2002

Messages

1,267

Office Version

1. 365

Platform

1. Windows

• Aug 5, 2003

• #10

select the range C4486:C4489, then go to the insert menu, select names, define, type CopyRange as your range name, and click OK

hth
kevin

 

Upvote 0