VBA

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
61
I have a workbook that contains 100+ worksheets. The far left workbook is named "List" the code below copies a selected range from the active sheets and places it in "List". The workbook is a contains a P&L for each department and the code is basically taking the review notes for each department and consolidating on the "List" worksheet.

The "List" worksheet is at the far left and you can see through worksheet "00-205" (Image 1 below). The worksheets increase in numerical order, so if I scroll to the right to where 00-205 is the left most sheet (Image 2 below) you no longer see the "List" sheet. Assuming I run the VBA on worksheet 00-210 while on Image 2, worksheet 00-210 remains the active sheet, however, the scroll bar with worksheet names reverts to that of Image 1.

Is there a way to modify the code so that it does not shift the tab names at the bottom to show "List" as the first worksheet after copy data each time?

VBA Code:
Sub Sheetname()

Application.ScreenUpdating = False

' Store the currently active worksheet
Dim originalSheet As Worksheet
Set originalSheet = ActiveSheet

Dim myRange As Range
Set myRange = Selection

Dim Lr As Long
Lr = Worksheets("List").Range("A" & Rows.Count).End(xlUp).Row
' Lr = Worksheets("List").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

myRange.Copy

Worksheets("List").Range("A" & Lr + 1).PasteSpecial Paste:=xlPasteAllExceptBorders
Worksheets("List").Range("I" & Lr + 1).Value = myRange.Parent.name

' Clear the clipboard and remove the "dancing ants"
Application.CutCopyMode = False

' Reactivate the original worksheet
originalSheet.Activate

Application.ScreenUpdating = True

End Sub

Image 1:
Image 1.png


Image 2:
Image 2.png
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I don't think there's a way to pin it. One option is to have it move to before/after your source sheet each time.
 
Upvote 0
Does placing this line of code towards the end of your code do what you want...

VBA Code:
ThisWorkbook.Worksheets.Item(1).Activate
 
Upvote 0
Solution
I'm thinking using Copy & Paste is the problem.

Dim Lr as range
Set Lr = Worksheets("List").Range("A" & Rows.Count).End(xlUp)
Lr.Offset(1) = Selection
 
Upvote 0
You're welcome, I was happy to help. Thanks for the feedback!
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,592
Members
452,653
Latest member
craigje92

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
Back
Top