Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro
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: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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro
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 "Gemz" wrote: 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: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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro
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: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 On Feb 22, 11:44*am, Gemz wrote: 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: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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Macro? | Excel Programming | |||
Simple Macro | Excel Worksheet Functions | |||
Simple Macro | Excel Programming | |||
a simple macro? | Excel Discussion (Misc queries) | |||
Simple help with macro please | Excel Programming |