Click here to Skip to main content
15,881,424 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Good morning,
I would like to add 2 actions, but I can't:

Basically I have to add 2 actions:

1) Finished saving all the files, I want that from the A2: T delete all the values;
2) I want the cell back to A2.

Thanks again

What I have tried:

VBA
Sub SalvaFile()
Dim wkA As Worksheet, ur As Long, j As Long, riga As Long, nomeFile As String
Dim percorso As String
Set wkA = Worksheets("Scadenziario")
wkA.Range("A2:T" & Rows.Count).ClearContents
ur = Range("A" & Rows.Count).End(xlUp).Row
riga = 2
percorso = "C:\Users\Itaerxga\Desktop\Scadenziari\"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error GoTo errori
For j = 2 To ur
    Cells(j, 1).EntireRow.Copy wkA.Cells(riga, 1)
    riga = riga + 1
    If Cells(j, 5) <> Cells(j + 1, 5) Then
        nomeFile = Cells(j, 5)
        wkA.Copy
        ActiveWorkbook.SaveAs Filename:=percorso & nomeFile & ".xlsx"
        ActiveWorkbook.Close
        wkA.Range("A2:T" & Rows.Count).ClearContents
        riga = 2
    End If
Next j
esci:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Copiato"
Exit Sub
errori:
MsgBox Err.Number & " - " & Err.Description
Resume esci:
End Sub
Posted
Updated 13-Jan-22 6:19am
v2
Comments
CHill60 15-Dec-21 13:08pm    
You already have wkA.Range("A2:T" & Rows.Count).ClearContents - which is your action 1)
For Action 2) I am guessing you want the active cell to be A2 so just use wkA.Range("$A$2").Select
Maciej Los 12-Jan-22 16:17pm    
My virtual 5!

1 solution

It's not clear why you "can't" add these steps but as I intimated in my comment you already have the code to do that.

Having looked at this again, you may be having problems because your code entirely relies on having the correct sheet active in the workbook when it runs. Always explicitly state which sheet you are referring to and that should avoid the problem e.g.
VB
Sub SalvaFile()
    Dim wkA As Worksheet, ur As Long, j As Long, riga As Long, nomeFile As String
    
    Dim wkB As Worksheet: Set wkB = Worksheets("Sheet1") 'change this to your source worksheet
    
    Dim percorso As String
    Set wkA = Worksheets("Scadenziario")
    wkA.Range("A2:T" & Rows.Count).ClearContents
    ur = wkB.Range("A" & Rows.Count).End(xlUp).Row
    riga = 2
    percorso = "C:\Users\Itaerxga\Desktop\Scadenziari\"
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error GoTo errori
    For j = 2 To ur
        wkB.Cells(j, 1).EntireRow.Copy wkA.Cells(riga, 1)
        riga = riga + 1
        If wkB.Cells(j, 5) <> wkB.Cells(j + 1, 5) Then
            nomeFile = wkB.Cells(j, 5)
            wkA.Copy
            'Instead of using ActiveWorkbook use the name of the workbook!
            ActiveWorkbook.SaveAs Filename:=percorso & nomeFile & ".xlsx"
            ActiveWorkbook.Close
            wkA.Range("A2:T" & Rows.Count).ClearContents
            riga = 2
        End If
    Next j
    
    'Clear the contents after saving all the files
    wkA.Range("A2:T" & Rows.Count).ClearContents
    'select cell A2 on the Scadenziario worksheet
     <s>wkA.Range("$A$2").Select</s>'Select and Activate can cause unintended effects 
     wkA.Range("$A$2").Show
    
esci:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Copiato"
    Exit Sub
errori:
    MsgBox Err.Number & " - " & Err.Description
    Resume esci
End Sub
You are also relying on the workbook with this code never becoming active - have a look at this sub-routinePublic
VB
Sub Demo()
    Dim countWbs As Integer
    countWbs = Workbooks.Count

    Dim i As Integer, wkb As Workbook

    For i = countWbs To 1 Step -1
        Set wkb = Workbooks(i)
        If wkb.Name <> ThisWorkbook.Name Then
            wkb.Activate
            'Do something with the Active Work book - e.g. your save thing
            Debug.Print wkb.Name
            wkb.Close
        End If
    Next
End Sub
Things to note:
- because I am using the index to the workbooks collection I have to start at the end and work backwards (so that book number 1 is always at position 1 and is always the same workbook even after one is closed)
- the Activate is not necessary if I explicitly refer to wkb in whatever I am doing
- I make sure I don't close the workbook that contains my code!
 
Share this answer
 
v4
Comments
Maciej Los 13-Jan-22 12:47pm    
I'd suggest to remove line with "Select" method. As long as we don't know what events are used in this worbook, such of line (instruction) may cause several dramatic consequences.
So, at this moment my vote is 4.
CHill60 13-Jan-22 13:11pm    
Fair comment! How else can we place the cursor in that cell? I'm thinking .Activate could also trigger an event? (Can't try it out at the moment :-()
CHill60 13-Jan-22 13:57pm    
Yes indeed. However I think the OP just wanted cell A2 to be "at the top" in a visual sense. I've always struggled with that. However I will update my solution
Maciej Los 13-Jan-22 14:42pm    
:thumbsup:
Upvoted!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900