VBA Copying Destination

[Littlemalky]

Is there any way to copy all cells and paste destination over all cells in another location so that it replaces whatever was there before and removes the old. For example, something like:

Workbooks("Example.xlsx").Worksheets("Example").Cells.Copy Destination:=Workbooks("Example1.xlsx").Worksheets("Example1").Cells

This never works for me because I believe it wants me to specify a range more specificly, but if i didn't want to clear the page of "Example1.xlsx" before I pasted something in there, is there an easy way to just paste over what was there replacing the contents if the amount of rows/columns differ. I know how to do it line by line, but I want to write it more efficiently.

 

[MrKowz]

Try, instead, doing:

Workbooks("Example.xlsx").Worksheets("Example").Cells.Copy Destination:=Workbooks("Example1.xlsx").Worksheets("Example1").Range("A1")

 

[Littlemalky]

Yeah, i understand the specifying the range part, but that won't replace everything that was there prior will it? Say I'm copying from something that has 10 rows, but I'm pasting to a sheet that already has 20 rows of data. If i specify the range like that, it's not going to delete the 10 row difference is it? Like pasting over "ALL" cells would...

 

[MrKowz]

It should, since you are selecting ALL of the cells to copy over.

However, you could always clear the contents of all the cells in your second workbook prior to pasting:

Workbooks("Example1.xlsx").Worksheets("Example1").Cells.ClearContents
Workbooks("Example.xlsx").Worksheets("Example").Cells.Copy Destination:=Workbooks("Example1.xlsx").Worksheets("Example1").Range("A1")

 

[Littlemalky]

Yeah, I was trying to avoid clearing contents for reasons that I have pivot tables connected to the data.

 

[Littlemalky]

I just tested the Cells.Copy thing and pasted it to a range and it worked! Thank you so much.