Menu
Ask a question Report

Update of Copied Excel sheet

-
Excel Ask a question Report
Hello,
I want to make copies of excel sheet such that if I delete rows/columns in main sheet they should be deleted in copy too.


System Configuration: Windows / Chrome 58.0.3012.0
See more 

2 replies

Best answer
Report Respond to ac3mark
Posts
12358
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
June 17, 2019
 1057
1
Thank you
Its called save as.....

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 4060 users have said thank you to us this month

Respond to ac3mark
Report Respond to TrowaD
Posts
2483
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 13, 2019
 354
0
Thank you
Hi Vinayak,

Could you give the following code a try and see how that works out for you? Keep in mind that actions done by macro's can't be reversed by using the blue arrows, so better test this on a copy of your workbook.

Here is the code: 
Sub RunMe()
Dim x, y As Integer, mCheck As Boolean, ws As Worksheet

If Selection.Columns.Count > 16383 Then
    x = ActiveCell.Row
    y = Selection.Rows.Count
ElseIf Selection.Rows.Count > 1048575 Then
    x = ActiveCell.Column
    y = Selection.Columns.Count
    mCheck = True
Else
    Exit Sub
End If

For Each ws In Worksheets
    If mCheck = False Then ws.Rows(x & ":" & x + y - 1).Delete
    If mCheck = True Then
        ws.Select
        Range(Columns(x), Columns(x + y - 1)).Delete
    End If
Next ws
Sheets("Main").Select
End Sub


After pasting the code in a standard module, you can go back to Excel and hit Alt+F8 to display available macro's. Clicking on the options button will allow you to create a shortcut button. I used CTRL+d.
The rows or columns need to be connected.

Best regards,
Trowa
Respond to TrowaD
Reply Ask a question