Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

combining two codes into one not work so far

0

hello  I need  combining  two  codes  in one  

when  I  open  inputbox  I  will  writing  name  file  then  when  create  the  file  should  filter  data   without  implement  in  open  file  , the  open  file  should  keeping  the  orginal  data  without  any  changes  . the  changes  just  in  new  file .which  creat  by inputbox .

I try  this 

Sub test()
    Dim wb As Object
    Application.ScreenUpdating = False
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
TryAgain:
    Flname = InputBox("Enter File Name :", "Creating New File...")
    If Flname <> "" Then
    Set wb = Workbooks.Add
    wb.Sheets(1).Name = "sheet1"
    wb.Sheets(1).Cells(1).CurrentRegion.Clear
    With ThisWorkbook.ActiveSheet.Cells(1).CurrentRegion
      Dim LR As Long
        LR = .Range("H" & Rows.Count).End(xlUp).Row

        .AutoFilterMode = False

        .Range("m2:m" & LR).Formula = "=or(n(j2)<>0,n(k2)<>0)"

        With .Range("H1:M" & LR)

            .AutoFilter Field:=6, Criteria1:=False

            If .Columns(1).SpecialCells(12).Count > 1 Then

                .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete

            End If

            .Parent.AutoFilterMode = False

            .Columns(.Columns.Count).Clear

        End With
    End With
wb.SaveAs ThisWorkbook.Path & "\" & Flname, FileFormat:=51
If Err.Number = 1004 Then
            wb.Close
            MsgBox "File Name Not Valid" & vbCrLf & vbCrLf & "Try Again."
            GoTo TryAgain
        End If
        ActiveWorkbook.Close
    
    Application.ScreenUpdating = True

I  hope  somebody  help .

Answer
Discuss

Discussion

Hi Tubrak

Don't think I'll have time to look at this today but please attach a representative Excel file (to save respondents time recreating your scenario/ data).     Please note your question only just appeared online for me (I think Don is travelling so has little time to moderate new questions to avoid spam attacks)
John_Ru (rep: 6142) Sep 27, '22 at 8:14 am
ok I attached  two files file , the result  in second  file  based on code where  start from 
 With ThisWorkbook.ActiveSheet.Cells(1).CurrentRegion
 . you  can  see  when  you  have  free time 
best regards,
Tubrak 
tubrak (rep: 24) Sep 27, '22 at 9:54 am
Add to Discussion

Answers

0
Selected Answer

Tubrak

Your combined code will work with the changes in bold below (some lines are removed, some replaced). If you need the original file unaltered, it's better to  copy the data to a new file then filter that- the revised code and new file does that. Therea re a few comments so you can see hat is happening:

Sub test()

Dim LR As Long

On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False

TryAgain:
Flname = InputBox("Enter File Name :", "Creating New File...")

'### copy sheet to a new file
If Flname <> "" Then ThisWorkbook.ActiveSheet.Copy

' in new file (leaving original in tact)
With ActiveSheet
    LR = .Range("H" & .Rows.Count).End(xlUp).Row
    .AutoFilterMode = False
    .Range("m2:m" & LR).Formula = "=or(n(j2)<>0,n(k2)<>0)"
    ' filter data
    With .Range("H1:M" & LR)
        .AutoFilter Field:=6, Criteria1:=False
        
        If .Columns(1).SpecialCells(12).Count > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        End If
        
        .Parent.AutoFilterMode = False
        .Columns(.Columns.Count).Clear
    End With

    ' rename sheet and save as .xlxs
    .Name = "sheet1"
    .SaveAs ThisWorkbook.Path & "\" & Flname, FileFormat:=51
    .Parent.Close
    If Err.Number = 1004 Then
        .Parent.Close
        MsgBox "File Name Not Valid" & vbCrLf & vbCrLf & "Try Again."
        GoTo TryAgain
    End If
End With
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Hope this works well for you.

Discuss

Discussion

thanks  john ,  but  why  close orginal  file? actually  I try  using  like  this 
.SaveCopyAs .Path & "\" & Flname, FileFormat:=51

to  keep orginal  file  is  open.
tubrak (rep: 24) Sep 28, '22 at 9:32 am
If you use that, the macro gets saved too (I think, I'm not near my PC to check.

All you need in my code is to comment out the line:
ActiveWorkbook.Close
John_Ru (rep: 6142) Sep 28, '22 at 9:50 am
actually  this  line  close  the  orginal  file and  open  new  file when save xlsx . 
I want the opposite .
thanks  again .
tubrak (rep: 24) Sep 28, '22 at 9:58 am
Sorry but I have no more time today to look at this. Might find time tomorrow. 
John_Ru (rep: 6142) Sep 28, '22 at 11:02 am
Currently my version if your macro:

1. filteted the originsl file

2. creates a new (filtered). xlsx file (with no macro) using the name entered in the input box

3. closes the original (macro) file, without saving changes. 

What do you mean by " I want opposite" please? 
John_Ru (rep: 6142) Sep 28, '22 at 11:59 am
to  step 2  is  ok . as  to  step 3   shouldn't   close the original (macro) file at  all . I  need it  to  do  some  adustments .
tubrak (rep: 24) Sep 28, '22 at 12:07 pm
See revised Answer/ file. All filtering is done in the new file.
John_Ru (rep: 6142) Sep 28, '22 at 1:45 pm
great ! 
much appreciated for your  help .
tubrak (rep: 24) Sep 28, '22 at 2:01 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login