Combine two macros in VBA Excel-CodePudding

I'm trying to combine two macros in Excel.

Both are working separately, but I don't know how to combine the two. Both do a loop.

The first one is:

Sub UpdateSheet()

Dim shtRng As Range
Dim c As Variant
Dim rng As Range

Set shtRng = Worksheets("SheetB").Range("F2:F39")

For Each c In shtRng
Worksheets("SheetA").Range("CA1:CZ99").Copy Worksheets(c.Value).Range("CA1")


Worksheets(c.Value).Range("CA1:CZ1").Orientation = 90

Worksheets(c.Value).Columns("CA:CZ").ColumnWidth = 5
  
Worksheets(c.Value).Columns("CA:CZ").HorizontalAlignment = xlCenter
Worksheets(c.Value).Columns("CA:CZ").VerticalAlignment = xlBottom
 
Worksheets(c.Value).Columns("CA").Font.Size = 14
Worksheets(c.Value).Columns("CA").Font.Bold = True

Worksheets(c.Value).Range("CB2:CZ2").Font.Size = 14
Worksheets(c.Value).Range("CB2:CZ2").Font.Bold = True


Worksheets(c.Value).Range("CB3:CZ99").Font.Size = 14
Worksheets(c.Value).Range("CB3:CZ99").Font.Bold = True
Worksheets(c.Value).Range("CB3:CZ99").HorizontalAlignment = xlCenter
Worksheets(c.Value).Range("CB3:CZ99").VerticalAlignment = xlCenter

Next c

End Sub

The second one is:

Sub HideEmptyColumns()

Dim rng As Range

Application.ScreenUpdating = False

For Each rng In Worksheets("c.Value").Range("CA1:CZ1")
If rng.Value = "" Then
rng.EntireColumn.Hidden = True
End If
Next rng

End Sub

It is probably very simple for all you wizards, but I'm still learning.

CodePudding user response：

This should get you in the ballpark:

Sub UpdateSheet()

Dim shtRng As Range
Dim c As Variant
Dim rng As Range

Set shtRng = Worksheets("SheetB").Range("F2:F39")

For Each c In shtRng
Worksheets("SheetA").Range("CA1:CZ99").Copy Worksheets(c.Value).Range("CA1")


Worksheets(c.Value).Range("CA1:CZ1").Orientation = 90

Worksheets(c.Value).Columns("CA:CZ").ColumnWidth = 5
  
Worksheets(c.Value).Columns("CA:CZ").HorizontalAlignment = xlCenter
Worksheets(c.Value).Columns("CA:CZ").VerticalAlignment = xlBottom
 
Worksheets(c.Value).Columns("CA").Font.Size = 14
Worksheets(c.Value).Columns("CA").Font.Bold = True

Worksheets(c.Value).Range("CB2:CZ2").Font.Size = 14
Worksheets(c.Value).Range("CB2:CZ2").Font.Bold = True


Worksheets(c.Value).Range("CB3:CZ99").Font.Size = 14
Worksheets(c.Value).Range("CB3:CZ99").Font.Bold = True
Worksheets(c.Value).Range("CB3:CZ99").HorizontalAlignment = xlCenter
Worksheets(c.Value).Range("CB3:CZ99").VerticalAlignment = xlCenter

'Adding other sub's code here:
For Each rng In Worksheets(c.Value).Range("CA1:CZ1")
   If rng.Value = "" Then
     rng.EntireColumn.Hidden = True
   End If
Next rng

Next c

End Sub