I need some help with copying and pasting through VBA

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

Try something like this (it worked for me):

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

Hope this helps,

Russell

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

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

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

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

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

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

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