Simple macro

[Gemz]

Hi,

I have a code but cant figure out how to change it so its just copies
everything rather than filter and then copy, the reason it filters is because
i copied this code from a filer code (but did think i removed all the filter
code bit.. obviously not!)

I think it might be this bit that is causing the problem: lastrow =
Cells(Cells.Rows.Count, "F").End(xlUp).Row
Set myrange = Range("F1:F" & lastrow)


All the code:

Sub CTS()
Sheets("A").Select
Dim myrange, copyrange As Range
Set copyrange = Rows(1).EntireRow
lastrow = Cells(Cells.Rows.Count, "F").End(xlUp).Row
Set myrange = Range("F1:F" & lastrow)
For Each c In myrange


Set copyrange = Union(copyrange, c.EntireRow)

Next
copyrange.Copy
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "B"
ActiveSheet.Paste
With ActiveSheet
.Range("D,H:I,K:L,N:U,W:AD,AF:AT,AV:IV").Delete

End With

Worksheets("B").Activate
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub


thanks alot.

 

[FSt1]

hi
your code is coping all date from the last row in column F that has data up
or meaning all rows have should have data. you would get into problems if
there was data below the last cell in F .
copy all?
Replace all code from Sheets("A").Select to ActiveSheet.Paste with this....
Sheets("A").Activate
Cells.Copy
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "B"
ActiveSheet.Paste

regards
FSt1

 

[paul.robinson]

Hi
Made a few changes. Comments are next to changed lines. Nothing in
your code filters anything, so a filter must be applied elsewhere.

Sub CTS()
Sheets("A").Select
Dim myrange as Range 'myrange not declared properly, copyrange not
needed
lastrow = Cells(Rows.Count, "F").End(xlUp).Row 'extra cells not
needed
Set myrange = Range("F1:F" & lastrow) .EntireRow 'includes Row 1 i.e.
copyrange
myrange.Copy
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "B"
ActiveSheet.Paste
With ActiveSheet
.Range("D,H:I,K:L,N:U,W:AD,AF:AT,AV:IV").Delete
End With
Worksheets("B").Activate
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
'sub doesn't set Displayalerts etc to false, so no need to make them
true
End Sub

regards
Paul

 

[Incidental]

Hi Gemz

Thought i would join in here... It would seem that you want to copy
all the data in a worksheet then create a new worksheet and paste this
data into it? Would it not be easier to copy the and paste the
worksheet rather than what it holds??? Anywho the code below would do
just that.

Option Explicit
Dim i As Integer

Private Sub CommandButton1_Click()

i = Worksheets.Count

Sheets("Sheet3").Copy After:=Sheets(i)


With Sheets(i + 1)

.Name = "B"

.Cells.EntireColumn.AutoFit

.Cells.EntireRow.AutoFit

End With

End Sub

Steve