MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I need some help with copying and pasting through VBA


Posted by Kevin on January 16, 2002 9:02 AM

Right now in VBA I have the following code:

Range("A1").Value = Range("B1".Value)

I use this code as a means of copying whatever is in cell B1 into A1 whenever the macro is run. However, I need it to also copy the format of B1, as well as any data validation criteria that is setup in B1, to A1. Right now all it pastes to A1 is the value. Can this be changed to suit my need?

Thank you,
Kevin


Posted by DK on January 16, 2002 9:09 AM

Hi Kevin,

There are other ways but the easiest is to use Copy and PasteSpecial e.g.

Sub CopyAll()
Range("A1").Copy
Range("B1").PasteSpecial xlPasteAll
End Sub

HTH,
D

Posted by Russell Hauf on January 16, 2002 9:12 AM

Try something like this (it worked for me):

Range("B4").Copy Destination:=Range("B6")
Application.CutCopyMode = False

Hope this helps,

Russell

Posted by kevin on January 16, 2002 9:25 AM

an additional question sort of related

Suppose I want to do something similar to this in another spreadsheet, only instead whenever I type data into a cell in column A, I want whatever is in B1:C1 to get copied down columns B and C of whatever row I happen to be in in column A. Could this code somehow be modified to do that, where my copy destination will always be different? I may be in over my head here, any help would be appreciated.

Thanks,
Kevin

Posted by Russell Hauf on January 16, 2002 9:30 AM

Re: an additional question sort of related

It is possible, but what do you have in columns B and C? It's possible that you could solve this with formulas instead of VBA.

rh

Posted by Kevin on January 16, 2002 9:54 AM

Re: an additional question sort of related

Columns B & C don't contain any data, but they are formatted a certain way, and they do have data validation criteria set up. This is what I want to copy. I don't want to just copy this down for a certain amount of rows, because I don't know how many rows this file will grow to, and I only want it to be as large as it absolutely has to be, so I don't know how I could do this except for within VBA.

Thanks,
Kevin

Posted by Russell Hauf on January 16, 2002 10:12 AM

Re: an additional question sort of related

Well, I have to first say that I would recommend that you just pull the cells down yourself. As I'm thinking my way through this, I can think of many ways the automatic way could fail. That being said, I'll just say that you could put some code in your Worksheet_Change event that checks for Column A and then copies the cells in the same row in Cols B & C down. The potential bad thing about this is that if you ever change some of your data in a cell that has already be defined, you may lose what data you had in columns B and C. But to copy multiple cells, you could try something like this:


Range(Cells(intRow, 2),Cells(intRow, 3)).Copy _
Destination:=Range(Cells(intRow+1, 2),Cells(intRow, 3))
Application.CutCopyMode = False

Hope this helps,

Russell

Posted by Russell Hauf on January 16, 2002 10:14 AM

One minor correction...

I forgot the "+1" in the second destination range argument. It should be as follows:

Range(Cells(intRow, 2),Cells(intRow, 3)).Copy _
Destination:=Range(Cells(intRow + 1, 2),Cells(intRow + 1, 3))
Application.CutCopyMode = False

Posted by Kevin on January 16, 2002 10:32 AM

i'll give this a try - thanks for all the help

Range(Cells(intRow, 2),Cells(intRow, 3)).Copy _