Column count

[Aksel Børve]

I am going to use the value from MPA "C23" in the first column on the COPY
sheet. But each time I use the macro, the value has to paste to the first
empty cell in that column (Next Cell) .
The "Rows Count" works, but not the "Column count"
Why?

Sheets("MPA").Range("C23").Copy
Sheets("COPY").Select
Range(Column.Count, "C1").End(xlToLeft).Offset(0, 1).PasteSpecial
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("MPA").Range("A1:W33").Copy
Sheets("COPY").Select
Cells(Rows.Count, "A").End(xlUp).Offset(2, 0).PasteSpecial
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Thanks
Aksel

 

[Mike Fogleman]

Change column to columns (plural).

 

[Tom Ogilvy]

Range(Columns.Count, "C1")

would resolve to

Range(256,"C1")

which will raise an error.

It is unclear where you are trying to paste

If you are trying to find the next empty cell on row 23

cells(23,columns.count).end(xltoLeft).Offset(0,1).PasteSpecial

if row1

cells(1,columns.count).end(xltoLeft).Offset(0,1).PasteSpecial

 

[Melanie Breden]

Hi Aksel,

I am going to use the value from MPA "C23" in the first column on the COPY
sheet. But each time I use the macro, the value has to paste to the first
empty cell in that column (Next Cell) .
The "Rows Count" works, but not the "Column count"
Why?

try this:

Dim lngLastRow As Long
Dim intLastCol As Long

With Sheets("COPY")
' First empty cell in Row 1
intLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 2

.Cells(1, intLastCol).Value = Sheets("MPA").Range("C23")
.Range("A" & lngLastRow & ":W" & lngLastRow + 33).Value = _
Sheets("MPA").Range("A1:W33").Value
End With

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)