how can ı use sheet names in a macro?

March 3, 2012 at 09:36:49
Specs: Windows 7
Hi Everyone!
I have an excel file with one cover sheet includes tab names in column A and many tabs which include tables related defective devices. Each of these tabs includes details of delivered defective devices at a time from one customer and the tab names represents related customer code and the arrival date. I have exactly the same table format in all sheets. I wrote a macro which copies and adds special parts of these tables to different sheets in the same workbook. For example; I copy customer code, device models, serial numbers, quantities and warranty situstions to the sheet with the name of "received defective devices" and problem, repairement details, technician name, cost, return date etc. to the sheet with the name of "delivered repaired devices". When macro runs from active sheet, go to "received defective devices" sheet and return to the active sheet again and this repeated a few times in order to complete the necessary operation.
I want to run this macro as a uniq macro for each sheet without changing sheet names manually. How can I revised my macro so the tab name part of it can be automatically updated using the active sheet? In other words; when I activate another sheet and run the same macro, it gives true results. Person who will add a new sheet so that he/she can input the new records in new sheet, should be add necessary records to the sheets "received defective devices" and "delivered repaired devices" by only pressing macro button on the new sheet.
Your prompt reply would be highly appreciated.
Thanks in advance!

See More: how can ı use sheet names in a macro?

Report •

#1
March 3, 2012 at 11:42:16
You provided a lot of detail related to defective materials, delivery dates, customer codes, etc., none of which seems to be directly related to your question.

Unfortunately , you didn't provide any details related to the code you are running.

As far as I can tell, your only question is this:

"How can I revise my macro so the tab name part of it can be automatically updated using the active sheet?'

Imagine how much easier it would be for us to help you if you had posted "the tab name part of it" (at a minimum) so that we could see what we're working with?

How can we tell you how to "revise" something if we don't know what we're revising?

Anyway, this syntax will reference the ActiveSheet by it's name. I don't know if this will help you, but I'll toss it out there anyway.

Sheets(ActiveSheet.Name)

as in...

Sub ShowSheetName()
 MsgBox Sheets(ActiveSheet.Name).Name
End Sub

If you are going to post any of your code, please click on the following line before doing so and read the instructions on how to post code in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 3, 2012 at 14:35:18
Sub Macro1()
'
' Macro1 Macro
' Gelen bozuk ürünlerin tümünü bir listede toplar.
'
' Keyboard Shortcut: Ctrl+Shift+G
' Sheets("sheetname").UnProtect userinterfaceonly:=true
    Sheets("2-100212-001-1-1").Select
    Application.Goto Reference:="R1C18"
    Selection.Copy
    Sheets("GELENLER").Select
    Range("f65536").End(xlUp).Offset(1, -5).Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("2-100212-001-1-1").Select
    Application.Goto Reference:="R7C1"
    Range("A7:B13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("GELENLER").Select
    Range("f65536").End(xlUp).Offset(1, -1).Select
    ActiveSheet.Paste
    Sheets("2-100212-001-1-1").Select
    Application.Goto Reference:="R7C29"
    Range("AC7:AC13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("GELENLER").Select
    Range("g65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("2-100212-001-1-1").Select
    Application.Goto Reference:="R7C6"
    Range("F7:F13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("GELENLER").Select
    Range("h65536").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Sheets("2-100212-001-1-1").Select
    Application.Goto Reference:="R7C26"
    Range("Z7:AA13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("GELENLER").Select
    Range("I65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    [F1:F65536].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   End Sub


Report •
Related Solutions


Ask Question