combine two codes

Ali M

Board Regular
Joined
Oct 10, 2021
Messages
173
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi
I want combining two codes
first
VBA Code:
Sub test()
   
    Dim OUTrng As Range
    Set wsOUT = Sheets("OUT")
    activesheet.Range("A1").CurrentRegion.Copy wsOUT.Range("A1")
    Set OUTrng = activesheet.Range("A1").CurrentRegion
    With OUTrng
        .AutoFilter 5, ""
        .AutoFilter 6, ""
        Application.DisplayAlerts= False
        .Offset(1).SpecialCells(xlCellTypeVisible).Delete
        Application.DisplayAlerts= True
        .AutoFilter
    End With
End Sub
second
Code:
Sub copysheet()
    Dim ws As Worksheet, StrPath As String
    Application.DisplayAlerts = False
    StrPath = ActiveWorkbook.Path & "\"
    Set ws = MONTHLY
        ws.Copy
        wb_name = ws.Name
        ActiveWorkbook.SaveAs Filename:= _
            StrPath & " RTP " & Format(Date, "MM-DD-yyyy") & ".xlsx", FileFormat:=51
        ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub
so the open file should search for the empty or zero value for columns E,F together if so then should delete it and save file as xlsx with same formatting and borders.
any help will be appreciated .
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,981
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@rlv01 thanks for trying to fixing code , honestly I no know how deal with this code , when I change to sheet name OUT based on your version , it gives me message as in first code , I try following your instruction , but I don't understand how works , what I suppose to do?
Did you run the code I posted (Sub combined())?

What message did it give you? I don't know what you mean by " it gives me message as in first code" so please supply the actual message.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Ali M

Board Regular
Joined
Oct 10, 2021
Messages
173
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Did you run the code I posted (Sub combined())?
yes I did it , it will clear the sheet in open file as in picture
t.PNG
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,981
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
That message is telling you that the Active Worksheet cannot be worksheet "OUT". You should select another worksheet as the activesheet before running the macro. The active worksheet must be the one containing the data that that you want to copy to worksheet "OUT" and then filter. Here is an example of what the message should look like when you run the macro from another worksheet:

1664227234718.png


Note that in my example above, the source worksheet is named 'SourceData'. SourceData was the Active Worksheet when I ran the macro. For you, it will a different name for some other worksheet. What worksheet? I do not know as you have not chosen to share that with us.
 

Ali M

Board Regular
Joined
Oct 10, 2021
Messages
173
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
ok this works very well .
just I have two quetion here I try to filter based on zero instead of empty
VBA Code:
 Set OUTrng = wsOUT.Range("A1").CurrentRegion
    With OUTrng
        .AutoFilter 5, "0"
        .AutoFilter 6, "0"
        Application.DisplayAlerts = False
        .Offset(1).SpecialCells(xlCellTypeVisible).Delete
        Application.DisplayAlerts = True
that doesn't work for me ,can you test it for me and tell me if works for you ,please?
as to me doesn't work .
second question can I get rid of sheet OUT , what's the purpose from it ?
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,981
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
...that doesn't work for me ,can you test it for me and tell me if works for you ,please?
as to me doesn't work .

I tested it with some sample data and it works for me.

second question can I get rid of sheet OUT , what's the purpose from it ?

No you cannot get rid of it. sheet OUT serves as a location to take a copy of the data and filter it and delete empty rows prior to saving it so as to allow the original data to remain unchanged.
 

Forum statistics

Threads
1,181,764
Messages
5,931,910
Members
436,808
Latest member
Dalton50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top