Run-time error 1004 with column counter

Hello Excel Forum,

On one worksheet I have a range of column headers. Underneath this range of column headers I'd like to copy and paste values that are calculated in another sheet. In the first step I set a variable equal to the length of the column headings, but I'm getting the run-time error 1004: method range of object worksheet failed. I'm thinking this may be an easy syntax problem but I couldn't find any solutions in the forum or through google.

This is my command button code:

Please Login or Register  to view this content.

My workbook is quite large and still in development so I have copied and pasted the three relevant worksheets to a new document:Modified Workbook for Excelforum.xlsm Let me know if any more information is required.

Many thanks in advance!
Griffin

This line doesn't make sense:

Please Login or Register  to view this content.

Do you mean:

Please Login or Register  to view this content.

Nice simple fix:

Please Login or Register  to view this content.

needs to be:

Please Login or Register  to view this content.

You're using the correct argument for the Range object, in that case, with a cell reference as a single argument (albeit in this case, that single argument is constructed by concatenating a constant and a variable)

In your original code, you referred to .Range(6, columns count) - you are referring to a cell by referencing the row and column number as separate arguments, so need to use the Cells object. In this case, the Range objects sees 6 and columns.count as separate arguments, and expects each argument to be a range reference. Neither of those values are range references, so it returns an error.

If it helps: http://excelmatters.com/referring-to-ranges-in-vba/