Copy and Paste - Last Row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a new to most of this but have done ok with google so far. I have a problem doing a copy and paste , trying to paste into the first blank row of a workbook. Here is what I am trying to use right now

..Columns("A:F").AutoFilter Field:=6, Criteria1:="Brand
..Columns("A:F").SpecialCells(xlCellTypeVisible).Copy Destination:=
Windows("AllBrands").ActivateSheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1

I get this error message - and I am not sure what it mean
"Object Doesn't Support This Property Or Method

It is pasting from one workbook to another - in case that is importan

I would appreciate any advise I can get on this - I am really not sure what is wrong
 
Adam,

A few things that I can see:
Your object error is because the Windows collection does not have a property
called ActivateSheets. Instead of ActivateSheets("Sheet1").Range, try
ActiveSheet.Range
You're copying a few columns of data, but pasting it into a subsection of
itself?
I suspect what you're really trying to do is copy all the visible rows, not
columns? Am I correct?

This code will probably do what you're after, assuming row 1 is your header
row.

Sub testit()
Dim lngLastRow As Long

With Sheet1
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Columns("A:F").AutoFilter Field:=6, Criteria1:="Brand"
.Range(.Rows(2),
..Rows(lngLastRow)).SpecialCells(xlCellTypeVisible).Copy Destination:= _
.Rows(lngLastRow + 1)
End With
End Sub


Rob


Adam Jones said:
I am a new to most of this but have done ok with google so far. I have a
Click to expand...
problem doing a copy and paste , trying to paste into the first blank row of
a workbook. Here is what I am trying to use right now.
.Columns("A:F").AutoFilter Field:=6, Criteria1:="Brand"
.Columns("A:F").SpecialCells(xlCellTypeVisible).Copy Destination:= _
Windows("AllBrands").ActivateSheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)


I get this error message - and I am not sure what it means
"Object Doesn't Support This Property Or Method"

It is pasting from one workbook to another - in case that is important

I would appreciate any advise I can get on this - I am really not sure
Click to expand...
what is wrong
 
Ok, I think I follow what you are saying about coping rows instead of columns. I tried the code you provided me, but first I inserted a window("AllBrands") after the destination part - because after the data that is visiable is copied, it has to then be pasted into a different workbook

With Sheet
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Ro
.Columns("A:F").AutoFilter Field:=6, Criteria1:="Brand
.Range(.Rows(2), Rows(lngLastRow)).SpecialCells(xlCellTypeVisible).Copy Destination:=
Windors("AllBrands.xls").Rows(lngLastRow + 1
End Wit

But now I still get errors. Arrgh...., I wish I understood this bette
Any further advise would be great, I am not sure where I went wrong
 
Please outline your requirements. It's difficult to determine your needs by
reading your code.

Adam Jones said:
Ok, I think I follow what you are saying about coping rows instead of
Click to expand...
columns. I tried the code you provided me, but first I inserted a
window("AllBrands") after the destination part - because after the data that
is visiable is copied, it has to then be pasted into a different workbook.
With Sheet1
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Columns("A:F").AutoFilter Field:=6, Criteria1:="Brand"
.Range(.Rows(2),
Click to expand...
Rows(lngLastRow)).SpecialCells(xlCellTypeVisible).Copy Destination:= _
 
Looks like this line is miss spelled.
Destination:= _
Windors("AllBrands.xls").Rows(lngLastRow + 1)
Click to expand...
Is word "Windors" valid? If not that would give your macro some problems.

Adam Jones said:
Ok, I think I follow what you are saying about coping rows instead of
Click to expand...
columns. I tried the code you provided me, but first I inserted a
window("AllBrands") after the destination part - because after the data that
is visiable is copied, it has to then be pasted into a different workbook.
With Sheet1
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Columns("A:F").AutoFilter Field:=6, Criteria1:="Brand"
.Range(.Rows(2),
Click to expand...
Rows(lngLastRow)).SpecialCells(xlCellTypeVisible).Copy Destination:= _
 
Back
Top