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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,182
Messages
5,857,812
Members
431,899
Latest member
jstanley

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