Stop macro from changing sheets.

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
We have a recorded macro that works fine, basically one types their work number and it triggers a Vlookup and auto fills a disposition log with details like if they were to checkout a tool. We have the same function when a tool is returned. There are two things I'm looking for; the first one is when the macro is activated it switches sheets to the log. We don't want this to happen, we want to sheet to remain on the data entry sheet. If we can do that will we be able to hide the log (Sheet) or do we need another macro to do that?
Here is our macro.
Sub SIGNOUT1()
'
' SIGNOUT1 Macro
'
'
Range("C28:G28").Select
Selection.Copy
Sheets("Sign out ").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("3:3").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Sub SIGNIN1()
'
' SIGNIN1 Macro
'
'
Range("C28:G28").Select
Selection.Copy
Sheets("Sign in ").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("3:3").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
See how this works

VBA Code:
Sub SIGNOUT1()
'
' SIGNOUT1 Macro
'
'
Range("C28:G28").Copy
With Sheets("Sign out ")
    .Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    .Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With
End Sub

Sub SIGNIN1()
'
' SIGNIN1 Macro
'
'
Range("C28:G28").Copy
With Sheets("Sign in ")
    .Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    .Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With
End Sub
 
Last edited:

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thank you and it works, but the strangest thing happened the entry sheet shifts down a row, every time we select the sign-in or sign-out button. More good news is now we can at least hide the sheets.
 

Attachments

  • Tool accountability.JPG
    Tool accountability.JPG
    26.2 KB · Views: 2

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You copied the code before I finished editing it.
VBA Code:
With Sheets("Sign in ")
    .Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    .Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With

Note that the line beginning with .Rows has a period in front of rows. That ties it to the sheet in the With statement. You need to add the period to that line in both the Sign in and Sign out codes. That will stop the row insert on the active sheets and put them on the correct sheets.
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Wow, strange one little period makes all the difference. It works perfectly now. Thank you so much!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Wow, strange one little period makes all the difference. It works perfectly now. Thank you so much!
Periods are important things.
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,129,394
Messages
5,636,050
Members
416,894
Latest member
Hari1992

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