Help with sheet naming in a macro

courtlandre

New Member
Joined
Feb 27, 2008
Messages
31
I use the following macro to look at a row, take info from that row, switch sheets, enter the info, copy something, switch back to the original sheet and enter the info. It does this for every row on the sheet.

Code:
Sub GetSS()
Application.ScreenUpdating = False
    BeginRow = 9
    Endrow = 80
    ChkCol = 3
    GrabCol = 2
    PutCol = 12
    
For RowCnt = Endrow To BeginRow Step -1
        If Cells(RowCnt, ChkCol).Value = "*" Then
            Cells(RowCnt, GrabCol).Copy
                Sheets("Dashboard").Select
                Cells(7, 3).Select
                ActiveSheet.Paste
                Cells(24, 5).Copy
                Sheets("CUT").Select
                Cells(RowCnt, PutCol).Select
                 Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        End If
        
        
    Next RowCnt

End Sub

I want to be able to use this macro for all sheets in the workbook. "Dashboard" is used by all sheets. but how do I get "CUT" to be dynamic and have the macro figure out which sheet it started with?

In other words, I need a macro that when ran, determines which sheet it was ran from and set that as a value that can be used.

Thanks for any help!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
Code:
Sub GetSS()
Application.ScreenUpdating = False
dim actsht as worksheet
set actsht = activesheet
    BeginRow = 9
    Endrow = 80
    ChkCol = 3
    GrabCol = 2
    PutCol = 12
    
For RowCnt = Endrow To BeginRow Step -1
        If Cells(RowCnt, ChkCol).Value = "*" Then
            Cells(RowCnt, GrabCol).Copy
                Sheets("Dashboard").Select
                Cells(7, 3).Select
                ActiveSheet.Paste
                Cells(24, 5).Copy
                actsht.Cells(RowCnt, PutCol).PasteSpecial Paste:=xlPasteValues, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        End If
        
        
    Next RowCnt

End Sub
 

courtlandre

New Member
Joined
Feb 27, 2008
Messages
31
Code:
Sub GetSS()
Application.ScreenUpdating = False
dim actsht as worksheet
set actsht = activesheet
    BeginRow = 9
    Endrow = 80
    ChkCol = 3
    GrabCol = 2
    PutCol = 12
    
For RowCnt = Endrow To BeginRow Step -1
        If Cells(RowCnt, ChkCol).Value = "*" Then
            Cells(RowCnt, GrabCol).Copy
                Sheets("Dashboard").Select
                Cells(7, 3).Select
                ActiveSheet.Paste
                Cells(24, 5).Copy
                ***actsht.select**
                ***Cells(RowCnt, PutCol).PasteSpecial Paste:=xlPasteValues, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        End If
        
        
    Next RowCnt

End Sub

Edit: Got it working. Made the changes noted above.

Doesn't work. It seems that actsht is not being set correctly. The macro runs and copies the first item, then switches to the other page, pastes, copies and then never switches back to the original sheet. I dont know enough about setting variables to know how to fix it.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,803
Members
416,983
Latest member
LessThanAverageUser

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
Top