• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to edit my sheet name in macro

Hi Jack

Use the following;

set sh = sheet1

Where Sheet1 is the sheet object number. You will never need to edit your sheet name again provided the sheet does not get deleted.

Take care

Smallman
 
Sir I got a code like below, but I want to to sort out and copy each party's sum of sales, cash received, cheque received, discount given, goods return etc. in a new sheet w. Sample file attached. Can somebody give a solution? That is Total of Invoice amount, Total of Cash received, Total of Cash Transfer, Total of Discount given, Total of Credit note for Each party.

Sir I want to Total by each party's Name Column B associated with its Narration

Sub totBygrp()
Dim sh As Worksheet, lr As Long, rng As Range, nSh As Worksheet
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh.Range("B1:B" & lr)
Columns("G").Insert
rng.AdvancedFilter xlFilterCopy, , sh.Range("G2"), True
Set nSh = Sheets.Add(After:=Sheets(Sheets.Count))
For Each c In sh.Range("G3", sh.Cells(Rows.Count, "G").End(xlUp))
c.Copy nSh.Cells(Rows.Count, 1).End(xlUp)(2)
On Error Resume Next
rng.AutoFilter Field:=1, Criteria1:=c.Value, VisibleDropDown:=False
With sh
nSh.Cells(Rows.Count, 2).End(xlUp)(2) = Application.Sum _
(.Range("D2" & lr).SpecialCells(xlCellTypeVisible))
nSh.Cells(Rows.Count, 3).End(xlUp)(2) = Application.Sum _
(.Range("E2:E" & lr).SpecialCells(xlCellTypeVisible))
nSh.Cells(Rows.Count, 4).End(xlUp)(2) = Application.Sum _
(.Range("F2:F" & lr).SpecialCells(xlCellTypeVisible))
End With
rng.AutoFilter
On Error GoTo 0
Next
End Sub
 

Attachments

  • AC-Statement.xlsx
    36.9 KB · Views: 3
Smallman said:
Hi Jack

Use the following;

set sh = sheet1

Where Sheet1 is the sheet object number. You will never need to edit your sheet name again provided the sheet does not get deleted.

Take care

Smallman
Click to expand...
Sir I got a code like below,but I want to sort out and copy each party's sum of sales, cash received, cheque received, discount given, goods return etc. in a new sheet w. Sample file attached. Can somebody give a solution? That is Total of Invoice amount, Total of Cash received, Total of Cash Transfer, Total of Discount given, Total of Credit note for Each party.

Sir I want to Total by each party's Name Column B associated with its Narration


Sub totBygrp()
Dim sh As Worksheet, lr As Long, rng As Range, nSh As Worksheet
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh.Range("B1:B" & lr)
Columns("G").Insert
rng.AdvancedFilter xlFilterCopy, , sh.Range("G2"), True
Set nSh = Sheets.Add(After:=Sheets(Sheets.Count))
For Each c In sh.Range("G3", sh.Cells(Rows.Count, "G").End(xlUp))
c.Copy nSh.Cells(Rows.Count, 1).End(xlUp)(2)
On Error Resume Next
rng.AutoFilter Field:=1, Criteria1:=c.Value, VisibleDropDown:=False
With sh
nSh.Cells(Rows.Count, 2).End(xlUp)(2) = Application.Sum _
(.Range("D2" & lr).SpecialCells(xlCellTypeVisible))
nSh.Cells(Rows.Count, 3).End(xlUp)(2) = Application.Sum _
(.Range("E2:E" & lr).SpecialCells(xlCellTypeVisible))
nSh.Cells(Rows.Count, 4).End(xlUp)(2) = Application.Sum _
(.Range("F2:F" & lr).SpecialCells(xlCellTypeVisible))
End With
rng.AutoFilter
On Error GoTo 0
Next
End Sub
Sample Data attached
Can you help me
 

Attachments

  • AC-Statement.xlsx
    36.9 KB · Views: 1
NARAYANK991 said:
Hi Jack ,

Try this :

Set sh = Sheets("Invoice-Cash-Cheque")

Narayan
Click to expand...
Sir I got a code like below,but I want to sort out and copy each party's sum of sales, cash received, cheque received, discount given, goods return etc. in a new sheet w. Sample file attached. Can somebody give a solution? That is Total of Invoice amount, Total of Cash received, Total of Cash Transfer, Total of Discount given, Total of Credit note for Each party.

Sir I want to Total by each party's Name Column B associated with its Narration


Sub totBygrp()
Dim sh As Worksheet, lr As Long, rng As Range, nSh As Worksheet
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh.Range("B1:B" & lr)
Columns("G").Insert
rng.AdvancedFilter xlFilterCopy, , sh.Range("G2"), True
Set nSh = Sheets.Add(After:=Sheets(Sheets.Count))
For Each c In sh.Range("G3", sh.Cells(Rows.Count, "G").End(xlUp))
c.Copy nSh.Cells(Rows.Count, 1).End(xlUp)(2)
On Error Resume Next
rng.AutoFilter Field:=1, Criteria1:=c.Value, VisibleDropDown:=False
With sh
nSh.Cells(Rows.Count, 2).End(xlUp)(2) = Application.Sum _
(.Range("D2" & lr).SpecialCells(xlCellTypeVisible))
nSh.Cells(Rows.Count, 3).End(xlUp)(2) = Application.Sum _
(.Range("E2:E" & lr).SpecialCells(xlCellTypeVisible))
nSh.Cells(Rows.Count, 4).End(xlUp)(2) = Application.Sum _
(.Range("F2:F" & lr).SpecialCells(xlCellTypeVisible))
End With
rng.AutoFilter
On Error GoTo 0
Next
End Sub
Sample Data attached
Can you help me
 

Attachments

  • AC-Statement.xlsx
    36.9 KB · Views: 4
Hi ,

I am sorry but I will not be able to spare time for this for at least 2 days ; if you can wait till then , I can certainly do it ; if it is urgent , I hope someone else will come forward and help out.

Narayan
 
Hi Jack

This is pretty quick and dirty but is this what you were trying to do.

Code: 
Option Explicit
 
Sub totBygrp()
Dim sh As Worksheet
Dim i As Integer
 
Set sh = Sheet3
Application.ScreenUpdating = False
 
    For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
        Sheet1.[b2] = sh.Range("A" & i)
        Sheet1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = sh.Range("A" & i)
    Next i
 
Application.ScreenUpdating = True
End Sub

File attached to show workings.

Smallman
 

Attachments

  • 1AC-Statement.xlsm
    45.4 KB · Views: 4
Smallman said:
Hi Jack

This is pretty quick and dirty but is this what you were trying to do.

Code: 
Option Explicit
 
Sub totBygrp()
Dim sh As Worksheet
Dim i As Integer
 
Set sh = Sheet3
Application.ScreenUpdating = False
 
    For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
        Sheet1.[b2] = sh.Range("A" & i)
        Sheet1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = sh.Range("A" & i)
    Next i
 
Application.ScreenUpdating = True
End Sub

File attached to show workings.

Smallman
Click to expand...
Sir I want all party name in one sheet

Party Name Invoice Amount Cash Received Discount Given Credit Note Sales Return Cash Transfer

1
2
3
4
5
6
7
8
9
10
11
 
Jack

I don't understand what you are trying to do. Post an example with an output page which clearly shows what you are looking for after the procedure is run.

Take care

Smallman
 
Smallman said:
Jack

I don't understand what you are trying to do. Post an example with an output page which clearly shows what you are looking for after the procedure is run.

Take care

Smallman
Click to expand...


Sir I want to sum each party's by pary Bill Amount, Cash Received, Cash Transfer, Cheque Received, Goods Return etc...
I mean, In Invoice-Cash-Cheque sheet, Sum each Party's transaction by with its Narration; Column B (Customer Name) Column C (Narration Heading)


I have posted a Macro for edit code, which total only Sales, Cash Cheque

I need a macro for sum of Party’s Total Sale, Cash, Cheque, Cash Transfer, and Goods Return Etc... if in any category in Invoice-Cash-Cheque sheet Column C in Narration Heading
As you told Sample file attached as per my requirement. In need result as on output sheet.

Thanks & Regards
 

Attachments

  • AC-Statement.xlsx
    19.2 KB · Views: 5
Smallman said:
Jack

I don't understand what you are trying to do. Post an example with an output page which clearly shows what you are looking for after the procedure is run.

Take care

Smallman
Click to expand...

Sir I have posted a macro which Total only Invoice, Cash and Cheque. Now here posted clearly in the uploaded sheet Output sheet what I required, so please look into this matter
Thanks and Regards
Click to expand...
 
Hi Jack,

I am not working on your logic or code.. I just want to point out few things..

19-10-2013 10-46-39.png

1. Referring to Sheet by Sheets Display Name
Set sh = sheets("Requirement")
Every sheet has unique Display Name, which is displayed in the Tab you can change. In above pic.. 3rd Sheet in the workbook (YELLOW).

2. Referring to Sheet by Sheets Code Name
Set sh = sheets99
Every sheet has unique Name, which you can change from Name Property .. 3rd Sheet in the workbook(PINK).

3. Referring to Sheet by Sheets Index Number
Set sh = Sheet(3)
An index number is a sequential number assigned to a sheet, based on the position of its sheet tab (counting from the left) among sheets of the same type .. You can't change it. 3rd Sheet in the workbook(BROWN).

1st check, which one in more trustworthy..
If your sheet's position changes randomly.. ignore referring sheet by Index Number..
If your Sheet's Display Name or Code Name is changes randomly use referring sheet by Index Number..

Its fully depends on the behavior of workbook, so decide and use any of the above code to
Edit sheet name in your macro..
i.e Set sh = Sheet(1) will refer to sheets("Database") which is also known as Sheet3
 
Debraj said:
Hi Jack,

I am not working on your logic or code.. I just want to point out few things..

View attachment 1003

1. Referring to Sheet by Sheets Display Name
Set sh = sheets("Requirement")
Every sheet has unique Display Name, which is displayed in the Tab you can change. In above pic.. 3rd Sheet in the workbook (YELLOW).

2. Referring to Sheet by Sheets Code Name
Set sh = sheets99
Every sheet has unique Name, which you can change from Name Property .. 3rd Sheet in the workbook(PINK).

3. Referring to Sheet by Sheets Index Number
Set sh = Sheet(3)
An index number is a sequential number assigned to a sheet, based on the position of its sheet tab (counting from the left) among sheets of the same type .. You can't change it. 3rd Sheet in the workbook(BROWN).

1st check, which one in more trustworthy..
If your sheet's position changes randomly.. ignore referring sheet by Index Number..
If your Sheet's Display Name or Code Name is changes randomly use referring sheet by Index Number..

Its fully depends on the behavior of workbook, so decide and use any of the above code to
Edit sheet name in your macro..
i.e Set sh = Sheet(1) will refer to sheets("Database") which is also known as Sheet3
Click to expand...

Thanks for your kind information
 
Debraj said:
Hi Jack,

I am not working on your logic or code.. I just want to point out few things..

View attachment 1003

1. Referring to Sheet by Sheets Display Name
Set sh = sheets("Requirement")
Every sheet has unique Display Name, which is displayed in the Tab you can change. In above pic.. 3rd Sheet in the workbook (YELLOW).

2. Referring to Sheet by Sheets Code Name
Set sh = sheets99
Every sheet has unique Name, which you can change from Name Property .. 3rd Sheet in the workbook(PINK).

3. Referring to Sheet by Sheets Index Number
Set sh = Sheet(3)
An index number is a sequential number assigned to a sheet, based on the position of its sheet tab (counting from the left) among sheets of the same type .. You can't change it. 3rd Sheet in the workbook(BROWN).

1st check, which one in more trustworthy..
If your sheet's position changes randomly.. ignore referring sheet by Index Number..
If your Sheet's Display Name or Code Name is changes randomly use referring sheet by Index Number..

Its fully depends on the behavior of workbook, so decide and use any of the above code to
Edit sheet name in your macro..
i.e Set sh = Sheet(1) will refer to sheets("Database") which is also known as Sheet3
Click to expand...

Sir still I didn't get a right answer. I think my post my be complicated to solve.
 
NARAYANK991 said:
Hi ,

I am sorry but I will not be able to spare time for this for at least 2 days ; if you can wait till then , I can certainly do it ; if it is urgent , I hope someone else will come forward and help out.

Narayan
Click to expand...
Sir Can you remove this post because still I did not receive any right solution.
 
Hi Jack,
* Please use proper title.. for your problem..
* Your title says.. you have problem of edit sheet name.. we tried our best to fulfil requirement..

anyway.. check the below code.. dirty and less validation.. but surely give you clue .. in which direction you need to go.. I think you have prior knowledge of vba and having 2007+ version of Excel..

Code: 
Sub OutputSheet()
    On Error Resume Next
        Application.DisplayAlerts = False
        Sheets("Output").Delete
    On Error GoTo 0
    Sheets.Add(, Sheets(Sheets.Count)).Name = "Output"
    With Sheets("Output")
        .Range("A1:I1") = Array("Party Name", "Amount", "Cash Received", "Cheque Received", "Sales Return", "Cash Transfer", "Goods Return", "Discount Given", "Cash T/F to Bank")
        Nudata = Sheets("Database").Range("A2").End(xlDown).Row
        Sheets("Database").Range("A2:A" & Nudata).Copy .Range("A2")
        .Range("B2:B" & Nudata).Formula = "=SUMIFS('Invoice-Cash-Cheque'!D:D,'Invoice-Cash-Cheque'!$B:$B,$A2)"
        .Range("C2:I" & Nudata).Formula = "=SUMIFS('Invoice-Cash-Cheque'!$E:$E,'Invoice-Cash-Cheque'!$B:$B,$A2,'Invoice-Cash-Cheque'!$C:$C,C$1)"
        .Range("D2:D" & Nudata).Formula = "=SUMIFS('Invoice-Cash-Cheque'!$F:$F,'Invoice-Cash-Cheque'!$B:$B,$A2,'Invoice-Cash-Cheque'!$C:$C,D$1)"
        With .Range("A1:I" & Nudata)
            .Value = .Value
            .Columns.AutoFit
            .NumberFormat = "$ 0.00"
        End With
    End With
End Sub
 
You must log in or register to reply here.
Back
Top