2 event codes under single object

[Truiz]

Good afternoon chaps,

I have an excell workbook which I'm trying to apply 2 events codes under the same object so I found some help and came up with this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Call Macro1(Target)
    Call Macro2(Target)
End Sub


Sub Macro1(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Target.Cells.Count = 1 Or Target.Row = 1 Then Exit Sub
    If Target.Offset(1, 0) = "" Then
        Target.Offset(-1, 0).Copy
        Target.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    End If
    Application.EnableEvents = True
End Sub


Sub Macro2(ByVal Target As Range)
    Application.ScreenUpdating = False
For Each Value In Target.Columns
    Columns(Value.Column).ColumnWidth = 8.43
    Worksheets(Sh.Name).Columns(Value.Column).AutoFit
Next Value
Application.ScreenUpdating = True
End Sub

But I'm getting an error on the second macro Run-time error '424':

Object required

it seems the problem is on this line

Worksheets(Sh.Name).Columns(Value.Column).AutoFit

I've tried to come up with something to make it work but so far I've failed

FYI The codes work perfectly by separate.

 

[Norie]

You don't declare, define or set Sh to reference anything anywhere in the code you posted.

What, in words, is the code meant to do?

Is this code only meant for a specific worksheet?

 

[Redwolfx]

Try

    Private Sub Worksheet_Change(ByVal Target As Range)
    Call Macro1(Target)
    Call Macro2(Target)
End Sub

Sub Macro1(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Target.Cells.Count = 1 Or Target.Row = 1 Then Exit Sub
    If Target.Offset(1, 0) = "" Then
        Target.Offset(-1, 0).Copy
        Target.PasteSpecial Paste:=xlPasteFormats
    End If
Application.EnableEvents = True
End Sub

Sub Macro2(ByVal Target As Range)
    Application.ScreenUpdating = False
For Each Value In Target.Columns
    Columns(Value.Column).ColumnWidth = 8.43
    ActiveSheet.Columns(Value.Column).AutoFit
Next Value
Application.ScreenUpdating = True
End Sub

 

[Truiz]

Seems to be working had to tweak it a bit since when I was typing the cell above the one I was Writing remained selected

Try

    Private Sub Worksheet_Change(ByVal Target As Range)
    Call Macro1(Target)
    Call Macro2(Target)
End Sub

Sub Macro1(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Target.Cells.Count = 1 Or Target.Row = 1 Then Exit Sub
    If Target.Offset(1, 0) = "" Then
        Target.Offset(-1, 0).Copy
        Target.PasteSpecial Paste:=xlPasteFormats
    End If
Application.EnableEvents = True
End Sub

Sub Macro2(ByVal Target As Range)
    Application.ScreenUpdating = False
For Each Value In Target.Columns
    Columns(Value.Column).ColumnWidth = 8.43
    ActiveSheet.Columns(Value.Column).AutoFit
Next Value
Application.ScreenUpdating = True
End Sub

 

[Truiz]

I have a question I forgot to ask...

Can this go in the "ThisWorkbook" object?

 

[Truiz]

The code is supposed to first copy the format of the cell above the one I'm inputting data and then to resize the cell to Autofit the code is not meant for a specific worksheet but it should be triggerd in all the workbook affecting all sheets

You don't declare, define or set Sh to reference anything anywhere in the code you posted.

What, in words, is the code meant to do?

Is this code only meant for a specific worksheet?

 

[Truiz]

So far the code only works in sheet 1 and I need the code to work in all sheets pasting the code in all the sheets does not work

 

[Norie]

For the code to work for all sheets you would need to use the workbook event SheetChange.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim col As Range

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Not Target.Cells.Count = 1 Or Target.Row = 1 Then Exit Sub
    
    If Target.Offset(1, 0) = "" Then
        Target.Offset(-1, 0).Copy
        Target.PasteSpecial Paste:=xlPasteFormats
    End If

    
    For Each col In Target.Columns
        col.EntireColumn.ColumnWidth = 8.43
        col.EntireColumn.AutoFit
    Next col
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

 

[Truiz]

Its working, however if I manually inpot some formating it stops working have to close all instances of excell and then open the workbook again also if I delete several cells they do not resize as they supposed to and then the code stops working

For the code to work for all sheets you would need to use the workbook event SheetChange.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim col As Range

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Not Target.Cells.Count = 1 Or Target.Row = 1 Then Exit Sub
    
    If Target.Offset(1, 0) = "" Then
        Target.Offset(-1, 0).Copy
        Target.PasteSpecial Paste:=xlPasteFormats
    End If

    
    For Each col In Target.Columns
        col.EntireColumn.ColumnWidth = 8.43
        col.EntireColumn.AutoFit
    Next col
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

 

[Norie]

What do you mean 'manually' input formatting?

That code will not be triggered by formatting applied manually or with code.

Also, how can you resize cells that have been deleted?