Error 1004 - Copy area and paste area not same size and shape

F

flaaron

New Member
Joined
Jul 5, 2011
Messages
5
I have a spreadsheet with over 7400 columns of data. I'm trying to copy out a select few columns and their corresponding data for some performance analysis.

I cobbled together the following routine, and it copies the first column just fine. However, all other columns throw the 1004 error.

Any suggestions would be most welcome (including how to do this without having to list out every column header name) :-)

If source is needed, I can provide a sample.
~Aaron

Code: 
Sub CopyColumns()
'Make sheet for relevant stats to be copied to
Sheets.Add.Name = "Results"

'Select the 'sheet1' worksheet
Sheets("sheet1").Select

'match desired counters to columns and copy those columns to the 'results' worksheet
esxtime = WorksheetFunction.Match("(PDH-CSV 4.0) (EDT)(0)", Rows("1:1"), 0)
counter1 = WorksheetFunction.Match("\\hcsesxicore1\Physical Cpu Load\Cpu Load (1 Minute Avg)", Rows("1:1"), 0)
counter2 = WorksheetFunction.Match("\\hcsesxicore1\Physical Cpu(_Total)\% Processor Time", Rows("1:1"), 0)
counter3 = WorksheetFunction.Match("\\hcsesxicore1\Physical Cpu(_Total)\% Util Time", Rows("1:1"), 0)
counter4 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Commands/sec", Rows("1:1"), 0)
counter5 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Reads/sec", Rows("1:1"), 0)
counter6 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Writes/sec", Rows("1:1"), 0)
counter7 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Average Driver MilliSec/Command", Rows("1:1"), 0)
counter8 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Average Kernel MilliSec/Command", Rows("1:1"), 0)
counter9 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Average Guest MilliSec/Command", Rows("1:1"), 0)
counter10 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Average Driver MilliSec/Read", Rows("1:1"), 0)
counter11 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Average Kernel MilliSec/Read", Rows("1:1"), 0)
counter12 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Average Guest MilliSec/Read", Rows("1:1"), 0)
counter13 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Average Driver MilliSec/Write", Rows("1:1"), 0)
counter14 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Average Kernel MilliSec/Write", Rows("1:1"), 0)
counter15 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba2)\Average Guest MilliSec/Write", Rows("1:1"), 0)
counter16 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Commands/sec", Rows("1:1"), 0)
counter17 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Reads/sec", Rows("1:1"), 0)
counter18 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Writes/sec", Rows("1:1"), 0)
counter19 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Average Driver MilliSec/Command", Rows("1:1"), 0)
counter20 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Average Kernel MilliSec/Command", Rows("1:1"), 0)
counter21 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Average Guest MilliSec/Command", Rows("1:1"), 0)
counter22 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Average Driver MilliSec/Read", Rows("1:1"), 0)
counter23 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Average Kernel MilliSec/Read", Rows("1:1"), 0)
counter24 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Average Guest MilliSec/Read", Rows("1:1"), 0)
counter25 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Average Driver MilliSec/Write", Rows("1:1"), 0)
counter26 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Average Kernel MilliSec/Write", Rows("1:1"), 0)
counter27 = WorksheetFunction.Match("\\hcsesxicore1\Physical Disk Adapter(vmhba3)\Average Guest MilliSec/Write", Rows("1:1"), 0)
counter28 = WorksheetFunction.Match("\\hcsesxicore1\Virtual Disk(vce-bvm-30_cucm-cust1)\Commands/sec", Rows("1:1"), 0)
counter29 = WorksheetFunction.Match("\\hcsesxicore1\Virtual Disk(vce-bvm-30_cucm-cust1)\Reads/sec", Rows("1:1"), 0)
counter30 = WorksheetFunction.Match("\\hcsesxicore1\Virtual Disk(vce-bvm-30_cucm-cust1)\Writes/sec", Rows("1:1"), 0)
counter31 = WorksheetFunction.Match("\\hcsesxicore1\Virtual Disk(vce-bvm-30_cucm-cust1)\Average MilliSec/Read", Rows("1:1"), 0)
counter32 = WorksheetFunction.Match("\\hcsesxicore1\Virtual Disk(vce-bvm-30_cucm-cust1)\Average MilliSec/Write", Rows("1:1"), 0)
counter33 = WorksheetFunction.Match("\\hcsesxicore1\Network Port(DvsPortset-0:33554484:3231822:vce-bvm-30_cucm-cust1.eth0)\MBits Transmitted/sec", Rows("1:1"), 0)
counter34 = WorksheetFunction.Match("\\hcsesxicore1\Network Port(DvsPortset-0:33554484:3231822:vce-bvm-30_cucm-cust1.eth0)\MBits Received/sec", Rows("1:1"), 0)

Sheets("sheet1").Columns(esxtime).Copy Destination:=Sheets("Results").Range("A1")
Sheets("sheet1").Columns(counter1).Copy Destination:=Sheets("Results").Range("A2")
Sheets("sheet1").Columns(counter2).Copy Destination:=Sheets("Results").Range("A3")
Sheets("sheet1").Columns(counter3).Copy Destination:=Sheets("Results").Range("A4")
Sheets("sheet1").Columns(counter4).Copy Destination:=Sheets("Results").Range("A5")
Sheets("sheet1").Columns(counter5).Copy Destination:=Sheets("Results").Range("A6")
Sheets("sheet1").Columns(counter6).Copy Destination:=Sheets("Results").Range("A7")
Sheets("sheet1").Columns(counter7).Copy Destination:=Sheets("Results").Range("A8")
Sheets("sheet1").Columns(counter8).Copy Destination:=Sheets("Results").Range("A9")
Sheets("sheet1").Columns(counter9).Copy Destination:=Sheets("Results").Range("A10")
Sheets("sheet1").Columns(counter10).Copy Destination:=Sheets("Results").Range("A11")
Sheets("sheet1").Columns(counter11).Copy Destination:=Sheets("Results").Range("A12")
Sheets("sheet1").Columns(counter12).Copy Destination:=Sheets("Results").Range("A13")
Sheets("sheet1").Columns(counter13).Copy Destination:=Sheets("Results").Range("A14")
Sheets("sheet1").Columns(counter14).Copy Destination:=Sheets("Results").Range("A15")
Sheets("sheet1").Columns(counter15).Copy Destination:=Sheets("Results").Range("A16")
Sheets("sheet1").Columns(counter16).Copy Destination:=Sheets("Results").Range("A17")
Sheets("sheet1").Columns(counter17).Copy Destination:=Sheets("Results").Range("A18")
Sheets("sheet1").Columns(counter18).Copy Destination:=Sheets("Results").Range("A19")
Sheets("sheet1").Columns(counter19).Copy Destination:=Sheets("Results").Range("A20")
Sheets("sheet1").Columns(counter20).Copy Destination:=Sheets("Results").Range("A21")
Sheets("sheet1").Columns(counter21).Copy Destination:=Sheets("Results").Range("A22")
Sheets("sheet1").Columns(counter22).Copy Destination:=Sheets("Results").Range("A23")
Sheets("sheet1").Columns(counter23).Copy Destination:=Sheets("Results").Range("A24")
Sheets("sheet1").Columns(counter24).Copy Destination:=Sheets("Results").Range("A25")
Sheets("sheet1").Columns(counter25).Copy Destination:=Sheets("Results").Range("A26")
Sheets("sheet1").Columns(counter26).Copy Destination:=Sheets("Results").Range("A27")
Sheets("sheet1").Columns(counter27).Copy Destination:=Sheets("Results").Range("A28")
Sheets("sheet1").Columns(counter28).Copy Destination:=Sheets("Results").Range("A29")
Sheets("sheet1").Columns(counter29).Copy Destination:=Sheets("Results").Range("A30")
Sheets("sheet1").Columns(counter30).Copy Destination:=Sheets("Results").Range("A31")
Sheets("sheet1").Columns(counter31).Copy Destination:=Sheets("Results").Range("A32")
Sheets("sheet1").Columns(counter32).Copy Destination:=Sheets("Results").Range("A33")
Sheets("sheet1").Columns(counter33).Copy Destination:=Sheets("Results").Range("A34")
Sheets("sheet1").Columns(counter34).Copy Destination:=Sheets("Results").Range("A35")

End Sub
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
You can't paste a column into a cell. Your first paste goes into column A, because you specified the first cell of the column. Your second paste is trying to put a column into a cell. Did you really mean to paste into B1 and then C1 and so on?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
436
maiwarits
M
P
  • Solved
VBA Find first empty cell in row
2
Replies
10
Views
6K
MARK858
MARK858
N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
662
myall_blues
myall_blues
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
408
mumps
mumps
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
257
chazrab
C

Forum statistics

Threads
1,224,518
Messages
6,179,259
Members
452,901
Latest member
LisaGo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top