Simplify the code, write everything in less than 20 lines

Ossian13

New Member
Joined
Oct 21, 2016
Messages
46
Hi Everyone,

i was wondering if this code
Code:
Range("A1").EntireRow.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Pending"
ActiveSheet.Paste
Cells.EntireColumn.AutoFit
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Closed"
Worksheets("Sursa").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Copy
Worksheets("Closed").Activate
Range("A1").Select
ActiveSheet.Paste
Cells.EntireColumn.AutoFit
Worksheets("Closed").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Approved"
ActiveSheet.Paste
Cells.EntireColumn.AutoFit
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Sursa2"
ActiveSheet.Paste
Cells.EntireColumn.AutoFit
Worksheets("Sursa").Select

could be simplified in fewer lines. It adds some sheets, copies the first row, paste the same row on all the sheets and autofits the size.

Thank you,
Ossian.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
something like this from the macro recorder and tweaked
Code:
Sub Macro1()

    Sheets.Add.Name = "Pending"
    Sheets.Add.Name = "Closed"
    Sheets.Add.Name = "Approved"
    Sheets.Add.Name = "Sursa2"
    
    Sheets("Sheet1").Select
    Rows("1:1").Select
    Selection.Copy
    
    Sheets(Array("Pending", "Closed", "Approved", "Sursa2")).Select
    Rows("1:1").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select

End Sub
as an example
 
Upvote 0
Just another method..

Code:
Sub CopyToAllSheets()
    Dim shArray(), i As Long, mysht As String
    mysht = ActiveSheet.Name

    shArray = Array(mysht, "Pending", "Closed", "Approved", "Sursa2")

    For i = LBound(shArray) + 1 To UBound(shArray)
        Sheets.Add().Name = shArray(i)
    Next i
    
    Sheets(shArray).FillAcrossSheets Sheets(mysht).Rows(1)
    
    For i = LBound(shArray) + 1 To UBound(shArray)
        Sheets(shArray(i)).Columns.AutoFit
    Next i
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top