Free Microsoft Excel 2013 Quick Reference

Macro to scroll through worksheets

Would like to create a macro that works similar to the Ctrl + Pgup or Ctrl + Pgdwn short-cuts to scroll through worksheets. I would like it to cycle through the worksheets though instead of stopping at the last worksheet... Eg. when run it will go:

Sheet1 --> Sheet2 --> Sheet3 --> Sheet1 --> Sheet2 --> Sheet3 --> Sheet1... and so on.


Post your answer or comment

comments powered by Disqus
I know how to scroll through worksheets in a workbook, but how do I say to start on the fourth worksheet through the end. I will not know the name of the worksheet, all I know it is the fourth worksheet in the workbook.

Thanks

Hi,

I'm trying to get a macro to cycle through all the sheets in a work book which have a value in cell Q2.

For each cell it should perform a simple function involving entering some code and basic copying and pasting. It should then copy and paste some cells into another (already opened) workbook, 30aug.xlsx, before returning to the original workbook and cycling through to the next sheet.

I wrote the following piece of code:

    Sub
myloop()
    
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets

    
    If Range("Q2") = "" Then
    Next ws
    End If
       
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "=LOOKUP(0.48,R[5]C[2]:R[52]C[2],R[5]C[4]:R[52]C[4])"
    Range("K7").Select
    Selection.Cut
    Range("L2").Activate
    ActiveSheet.Paste
    Range("A2,L2,Q2").Select
    Range("Q2").Activate
    Selection.Copy
   
    Set orig = ActiveWorkbook
    
    Windows("30aug.xlsx").Activate
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
              
               
orig.Activate
    
    
    Next ws
    
    End Sub
the first error I encounter is a next without for (applied to the for contained within the if code), however, even when i remove the if code and ask it to cycle through every worksheet in the workbook it just keeps looping through the same sheet over and over, never progressing to the next sheet.
My worksheets have random names as oppose to sheet1, sheet2, sheet3 etc... is this the problem.

I'm an novice, what am I doing wrong?

Thanks for all and any help
Ben

For the following part of the code in my workbook I need to modify the macro to run through every worksheet and perform the same copy paste function. Right now the macro only runs on the JP Morgan worksheet. I would still like all of the data to be consolidated onto the JP Morgan worksheet starting in B6.


Sub CombineData()

Dim C As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim R As Long
Dim Rng As Range
Dim RngEnd As Range
Dim Wks As Worksheet

Set Wks = ActiveSheet

R = 6
FirstCol = Wks.Columns("G").Column
LastCol = Wks.Cells(6, Columns.Count).End(xlToLeft).Column

Set Rng = Wks.Range("B6")
Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))

Rng.ClearContents
Application.Calculation = xlCalculationManual

For C = FirstCol To LastCol Step 2
Set Rng = Wks.Cells(6, C)
Set RngEnd = Wks.Cells(Rows.Count, C).End(xlUp)
If RngEnd.Row >= Rng.Row Then
If R + Rng.Rows.Count > Wks.Rows.Count Then
MsgBox "Aborting - not engough rows for data."
Exit For
End If
Set Rng = Wks.Range(Rng, RngEnd).Resize(ColumnSize:=2)
Wks.Cells(R, "B").Resize(Rng.Rows.Count, 2).Value = Rng.Value
R = R + Rng.Rows.Count
End If
Next C

[code/]

My scroll moves the entire worksheet window instead of being able to scroll
through individual active cells. Its like my freeze panes is on butits not.

Hi Guys,
I have worksheet with a column with mutliple cell values. I am looking for a macro to loop through each cell ina column and do the following:-

If there is only one value in a cell then keep it.
otherwise
If there are multiple entries in a cell then just keep the second entry and delete the first, third, so on.

I have attached a sample sheet with two tabs " Original" and "formatted data" to show what i am looking for.
Any help will be much appreciated.

Thanks
Roop

I would like a macro to search through all sheets in a workbook for a given value. I have it working with one exception: it won't go to the next sheet and it just keeps repeating the same results. I've been looking for help on this for days and maybe I'm just not asking the right question. So here is the code I have so far:


	VB:
	
 workbookSearch() 
     
    Dim ws As Worksheet 
    Dim sString As String 
    Dim sCell As Variant 
     
Top: 
    sString = InputBox("Enter search information:", "Workbook Search") 
     
    For Each ws In Worksheets 
        ws.Activate 
         
First: 
        sCell = Cells.Find(What:=sString, After:=ActiveCell, LookIn:=xlValues, _ 
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
        MatchCase:=False, SearchFormat:=False).Activate 
         
        Do 
            Msg = "I found: " & Chr(13) & _ 
            ActiveCell.Value & Chr(13) & _ 
            "Would you like to keep looking?" 
            Style = vbYesNo + vbQuestion 
            Title = "I found something!" 
            Response = MsgBox(Msg, Style, Title) 
             
            If Response = vbYes Then 
                Goto First 
            End If 
            Exit Do 
        Loop While Not vbNo 
         
        If Response = vbNo Then 
            Goto Done 
        End If 
         
         
         
        Msg = "I found nothing to match: " & sString & _ 
        Chr(13) & Chr(13) & _ 
        "Would you like to look for something else?" 
        Style = vbYesNo + vbQuestion 
        Title = "I found nothing..." 
        Response = MsgBox(Msg, Style, Title) 
         
        If Response = vbYes Then 
            Goto Top 
        Else 
            Goto Done 
        End If 
         
    Next ws 
     
Done: 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance
/me

Hi guys,

I need help for a macro to loop through all worksheets (identical
structure), copy the contents of B2 and rename the sheet with it, all at one
go. B2 will hold text (different for each sheet). I'm using xl97.

TIA
Max

Please everyone,

I seriously need help on this!!!

I need a macro to loop through a column 'A' in the 'raw' worksheet and pick out each tag and paste it in the tag column 'A' of the 'done' worksheet.

For each tag, there are certain attributes such as BAO, SAO, HHALIM, HAL, in adjacent columns. Their values are in the cell next to them. In the 'done' worksheet they are all arranged out .

As the macro loops through the column A, for each tag pasted, it should pick out these attributes and pastes their values in under these attributes in the 'done' worksheet.

the workbook is attached.

Thank you very much for your help.

Excel

Is it possible to scroll a worksheet automatically at a speed of my choosing ? Hitting the scroll arrow moves it too quickly and using the mouse wheel slowly going thru 10000 cells will make me insane. I'd like to be able to use a speed which gives me just a split second to comprehend what is in the cell and then for the worksheet to scroll to next cell. I figure three cells a second scroll speed would be about right.
As always, thanks for the help.

Hello Experts!

Basically:
I am a VBA novice and have hit a roadblock. I need to create a macro to search through a column for records within a range of values. Then I need to copy the relevant rows and populate them in a new worksheet.

Specifically:
I need the macro (executed by clicking the orange rectangle in DataEntry) to search in the worksheet named QuarantineData for records that have both: dates between today and two weeks ago (in column I) null values (in column T)The corresponding rows of relevant data need to be copied and entered into a new worksheet. Ultimately, I would like to create a new worksheet each time this macro is run, named whatever today's date is. I know that there is a limit to the number of worksheets allowed but I can manage that later.

Worst case: it's not possible to name a sheet after today's date, then I need to be able to delete all the old data and copy over the new records into the same worksheet each time the macro is run.

I'm hoping some, if not all, of this is possible. If you need any further explanation or clarification, I'll be glad to explain. Any assistance you can offer would be greatly appreciated. Thank you for your time!

Hi,

I have 3 macros currently and wish to run them through a set list of workbooks found in file Auto Comment Control.xls, the Auto Comment (test) files represent the workbooks that I want the macros to run through. Thanks in advance for anyones' help

here is a list of the three macros

Public Sub ME_comment()
Dim c_ell As Range, Date_Ref As Date, S_heet As Worksheet
Date_Ref = InputBox("Enter a reference date", "DATE ENTRY", 200, 200)
For Each S_heet In ActiveWorkbook.Sheets
If Left(S_heet.Name, 3) <> "Raw" Then
    S_heet.Select
    For Each c_ell In Range("E2", Cells(Rows.Count, 2).End(xlUp))
        If IsDate(c_ell) Then
            If c_ell <= Date_Ref Then
                c_ell.Offset(0, -1) = "ME related"
            End If
        End If
    Next c_ell
End If
Next S_heet
End Sub
Public Sub OK_comment()
    Dim c_ell As Range, Date_Ref As Date, S_heet As Worksheet, c_ell2 As Range

    For Each S_heet In ActiveWorkbook.Sheets
        If Left(S_heet.Name, 3) <> "Raw" Then
            With S_heet
                For Each c_ell In .Range("F2:F" & .Range("F" & .Rows.Count).End(xlUp).Row)
                    If c_ell = "C to BBNT" Or c_ell = "Will C SDCNT" Then
                        c_ell.Offset(0, 1) = "OK"
                    Else
                        If c_ell <> "" Then c_ell.Offset(0, 1) = "Not OK"
                    End If
                Next c_ell
            End With
        End If
    Next S_heet
End Sub
Public Sub NT_comment()
Dim c_ell As Range, Date_Ref As Date, S_heet As Worksheet, c_ell2 As Range

For Each S_heet In ActiveWorkbook.Sheets
If Left(S_heet.Name, 3) <> "Raw" Then
    S_heet.Select
    For i = 2 To Cells(Rows.Count, "C").End(xlUp).Row
    Cells(i, "F").Value = Cells(i, "A").Value & Cells(i, "C").Value & Cells(i,
"B").Value
Next i
End If
Next S_heet
End Sub


I recorded a macro to select the worksheet to my right, however, it automatically recorded it with the worksheet name so it currently says...

Sheets("Sheet2").Select
End Sub

I imagine this will be easy for someone out here, but how can I change this so it just advances to the "next" worksheet where "next" will always be the worksheet to the right? I want to assign the macro to a button that I can use on multiple worksheets and with the named worksheet I'd have to create a unique macro on every page vs. just copying and re-using the same macro over and over.

Sorry, I'm a novice, but I hope this is clear enough. I realize that there are probably other ways to do this as well, but this is what I need for this project. Thanks in advance for the help!

It seems like it is locked and won't allow me to scroll through my document.
The scroll lock is NOT on. Can anyone help?

Trying to understand the scroll bar function. The small tests i've experimented with demonstrate this as a slider range for inputting values. ie adjusting the Min/Max.

Can it be used in a typical fashion such that when a cell or textbox contains text too large to display, the scroll bar will allow to scroll through the entire contents?

thanks

~jeff

Hi Guys,
I am trying to write a macro to loop through all the subfolders in a main folder and open the latest excel workbook on the basis of latest modified date and time . I have something that does this but i have to manually change the subfolder name and file extension, I want it to look for files with .xlsx and .xls , when inside a subfolder. Any help will be highly appreciated.

Thanks
Roop

Here is what i have right now.

Sub LastModifiedFilewithinFolder()

Dim strFile As String, strFolder As String
Dim dtLast As Date, strLMFile As String

strFolder = "T:HVM LSPF32PCDP1270BT"
strFile = Dir("T:HVM LSPF32PCDP1270BT*.xlsx*", vbNormal)
Do While strFile <> ""
If FileDateTime(strFolder & strFile) > dtLast Then
dtLast = FileDateTime(strFolder & strFile)
strLMFile = strFolder & strFile
End If
strFile = Dir
Loop

MsgBox "Last Modified file is : " & strLMFile
Workbooks.Open (strLMFile
)

Hi all,

I was wandering if anyone can offer any advice on a problem.

Basically, in A:A I have a list of numbers. In B:B I have a list of corresponding values. The values in B:B are always unique, but values in A:A may be the same. Identical values will always appear together (eg A1, A2, A3, etc).

I want to make a macro to sort through this data, and if A1=A2, copy B2 to C1. If A1=A3, copy B3 to A1.

Then move down the list in A:A until it finds the next value....and repeat.

So in essence...where values in A:A are identical, column B:B is copied and then transposed into the first cell containing the value.

If this does not make sense...the diagram below may help explain!

Original list -

A B
1 569
1 572
2 433
3 625
4 744
5 766
5 767
5 792

Sorted List -

A B C D
1 569 572
1 572
2 433
3 625
4 744
5 766 767 792
5 767
5 792

Thank you in advance for any advice!

Robert

Hi guys. I need to have a quite clever macro to loop through some rows and if certain criteria is met then perform certain actions.

Information on the spreadsheet is a on going and ever growing (row wise) master list so over period of time more and more lines will be added to it.Currently its more than 2000 lines. About 30 to 150 lines is added each day or so. Horizontally information range on the spreadsheet is from Col A to Col J. Cells in columns C and G are used to determine if criteria is met. If criteria is met then cells in column G and I will be manipulated. I will try to give plenty of examples to try to explain what I need to achieve.

Criteria.
First objective is to is to figure out if "No" duplicate line, "Semi" duplicate line or "Full" duplicate line exists.
So macro should loop through columns C and column G to determine if one of these three might exist. I imagine the loop trough happens in from bottom to top.

No duplicate line - cell in column C is unique (it does not equals to any other cell in column C). Looking cell in column G is not required.
Example:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207368----------------------------------------------------------1
181026688710------------RN-207386----------------------------------------------------------1
181026688810------------RN-207414, RN-207415, RN-207416------------------------5
181026691110------------RN-207394----------------------------------------------------------1
181026691210------------RN-207392----------------------------------------------------------1
181026691310------------RN-207400, RN-207401, RN-207402------------------------7
181026689510------------RN-207397----------------------------------------------------------1
181026689610------------RN-207399----------------------------------------------------------1
181026716910------------RN-207368, RN-207369, RN-207370------------------------2
181026688710------------RN-207399----------------------------------------------------------1
181026688810------------RN-207414----------------------------------------------------------1

none of the cells in col C is duplicate.

Semi duplicate line - cell in column C is duplicate (it equals to some other cell in column C. Now we are comparing cell value in column G (lowest row of the duplicate against the values of the upper duplicates. If the upper duplicates do not contain the value of lowest duplicate cell we then have a semi duplicate .
Example1:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207368----------------------------------------------------------1
181026688710------------RN-207386----------------------------------------------------------1
181026691210------------RN-207414, RN-207416-----------------------------------------4
181026691110------------RN-207394----------------------------------------------------------1
181026691210------------RN-207392----------------------------------------------------------1
181026691310------------RN-207400, RN-207401, RN-207402------------------------1

Edit: notice the red ones in column C are duplicates. In column G there are no duplicates

Or
Example2:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207380----------------------------------------------------------1
181026688710------------RN-207090----------------------------------------------------------1
181026688810------------RN-207343----------------------------------------------------------1
181026699410------------RN-207209----------------------------------------------------------1
181026699410------------RN-207210----------------------------------------------------------1
181026699410------------RN-207211----------------------------------------------------------1
181026713110------------RN-207418----------------------------------------------------------1
181026712910------------RN-207412----------------------------------------------------------1

Edit: notice the red ones in column C are duplicates. In column G there are no duplicates

Full duplicate line - cell in column C is duplicate (it equals to some other cell in column C . Now we are comparing cell value in column G (lowest row of the duplicate) against the values of the upper duplicates. If the upper duplicates contain the value of lowest duplicate we then have a Full duplicate .
Example1:

-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207368----------------------------------------------------------1
181026688710------------RN-207386----------------------------------------------------------1
181026691210------------RN-207414, RN-207415, RN-207416------------------------4
181026691110------------RN-207394----------------------------------------------------------1
181026691210------------RN-207415----------------------------------------------------------2
181026691310------------RN-207400, RN-207401, RN-207402------------------------1

Edit: notice the red ones in column C and G are duplicates.

Or
Example2:
-------col C ------------------------------------col G----------------------------------------------Col I
181026716910------------RN-207380----------------------------------------------------------1
181026688710------------RN-207090----------------------------------------------------------1
181026688810------------RN-207343----------------------------------------------------------1
181026699410------------RN-207216----------------------------------------------------------1
181026699410------------RN-207216----------------------------------------------------------1
181026699410------------RN-207216----------------------------------------------------------1
181026713110------------RN-207418----------------------------------------------------------1
181026712910------------RN-207412----------------------------------------------------------1

Edit: notice the red ones in column C and G are duplicates.

Action taken if criteria is met
Action taken if criteria is met

Depending of the outcome of criteria check:
1) there is no action taken
2) certain rows are combined together and then others will be deleted.
3) certain rows are deleted

No duplicate line - in this scenario no action is taken
All the rows will be left as they were

Semi duplicate line- there are some actions to be taken with cells in column G and Column I. Column G contains certification numbers. 90 percent of the time they start with letters RN. Column I contains quantities ie numbers. So now we need to combine all the information of column G and I. It should be done on the lowest row of these duplicates. So all the certification numbers should be combined together in this following style if possible: RN-xxxx, RN-xxxx, RN-xxxx, RN-xxxx,. So comma and space would be used. Other styles could be used as well if comma and space is not be possible. Now with column I the numbers should just be summed up. All these should be done in that last row of respective semi duplicate. Alternatively a new row can be created to the to the end of the list (first available empty row) and get the information there. But then all the information in other columns should be carried along as well. Now all the duplicate rows except that last one should be deleted.

Continues with POST 2

Hello all,

I'd like a macro to rename a worksheet from its current name of

"FullScreen (2)"

to say Numbers, plus today's date (without the plus) For example...

Numbers as of 02-17-09

Can this be done?

Thanks much

Hi All,

I am not sure if this is feasible. I am trying to find a group of 20 to 30 combinations in a group of 50 combinations that doesn't consist of 6 to 10 of the numbers in 1 to 52.

I.E. below I have 50 combinations of numbers 1 to 52...

5 8 15 26 36 47
14 24 25 35 43 48
7 19 29 32 35 45
17 19 20 32 39 41
1 2 27 31 32 39
16 18 20 26 28 29
28 32 33 40 49 51
14 20 26 27 28 39
2 8 39 44 49 50
6 25 26 35 39 50
16 19 20 25 43 52
2 3 32 34 39 47
11 17 25 26 35 49
14 17 32 38 46 49
1 5 12 14 27 30
5 12 22 26 31 47
2 8 18 38 39 46
6 7 18 30 47 50
15 20 23 30 44 50
1 7 11 34 48 52
13 14 30 31 38 47
2 3 19 29 32 47
3 7 21 35 42 50
4 8 18 33 49 50
5 6 7 9 12 19
9 16 17 33 34 36
13 15 20 27 46 49
8 10 12 34 40 48
11 19 21 34 39 47
18 25 29 35 38 51
1 4 5 17 27 38
4 18 19 24 31 32
4 15 19 23 24 25
9 23 25 34 37 51
9 20 24 36 44 46
10 23 30 43 47 52
5 6 15 16 39 44
3 4 7 9 25 44
4 5 7 26 33 45
5 18 19 30 40 50
2 5 24 33 37 39
3 6 13 14 17 35
12 19 24 28 30 39
18 35 36 39 45 47
7 10 22 31 36 37
5 12 15 35 38 50
11 20 24 35 39 49
5 7 10 15 36 37
13 21 31 38 39 40
8 18 21 25 40 45

For the above of 50 combinations, I would like a macro to scan through all possible combinations of 20 to 30 combinations to look for a group of combinations that is missing 6 or 7 or 8 or 9 or 10 numbers in 1 to 52.

So its like the marco will prompt me asking how many combinations I want to scan through that 50 combinations. Maybe I enter 25 and it will look through all possible combinations of 25 in the 50 combinations for a group of combination that is missing 6 or 7 or 8 or 9 or 10 numbers in 1 to 52.

For the above case, I know excel would have to look through 126410606437752 groups of 25 combinations to look for 1 group of combination that has 6 or 7 or 8 or 9 or 10 missing numbers in 1 to 52, so I was wondering is it possible.

Thanks in advance for the help/advise.

Flex

I want to create a macro to email the worksheet. For some reason (unknown to me) I can not automate this with a macro. The selection of File/Send to/Mail Recipient does not record in the macro.

Please help, I am not a VB programer.

Thank you

Hello all. I am a long time reader, but this is my first post. I have a project I've been working on for some time now, and I've hit a point where I'm stuck.

What I want to do is this -- I'm going to have a database of worksheets where users will enter data on whether orders of parts are accepted or rejected (via validated dropdown box), and the inspection level on those orders of parts (again via validated dropdown box.) Then they will fill in the date they entered the data, and their initials.

I want a sheet at the end (labeled "Output") that has a button assigned to a macro, which will shuffle through all of the sheets, and pull only the data from the date the button is clicked, to a week prior. So basically if it sees data posted today up to seven days ago, it will copy the 5 relevant cells to the output sheet. The problem is that I'm going to have each worksheet set up as a product "family" which has several types of similar parts, and so there will be several separate sets of data on each sheet.

I'll attach the workbook I have created thus far, and the programming is within it. I know I'm close, and I don't think that this is a particularly difficult thing to do. I just can't figure out what kind of code I will need to copy the specific cells pertaining to each date. Hopefully the workbook will help explain what I'm trying to do better than my own convoluted words.

Thanks, guys.

There will be many worksheets within a workbook file. Instead of the user scrolling through many worksheets, I would like to be able to create a form with macros attached to goto the appropriate worksheet quickly.

Has anyone done something like this or have an example to get me started?

Thank you,

CC

My eventual goal is to create a function that will scroll through every worksheet displaying them 1 at a time then being able to select one. -

That being said here is where I currently stand: I have a function that gives me a comma seperated string of the worksheet names, so I decided to add a section that would display the current worksheet and wait for my entry in the inputbox before continuing to the next worksheet, but for some reason when I try to select the worksheet I bounce out of the function.

Any Ideas?
Code:
Function GetwsNames(sWBName) As String
'
' Return a comma seperated string of all the Worksheet Names in the Workbook
'
Dim answer As String
Dim ws As Worksheet
Dim sWSNames As String
With Workbooks.Open(sWBName)
    For Each ws In .Sheets
        If sWSNames = "" Then
            sWSNames = ws.Name
        Else: sWSNames = sWSNames & "," & ws.Name
        End If
        'the following should display each worksheet as it is selected
        'I have tried many combinations of code to get this to work
        'and am now trying in round about ways
        answer = ws.Name
        Worksheets(answer).Cells(1, 1).Activate
        Range("A1").Select
        answer = Application.InputBox("Press any key", "Waiting", Type:=2)
    Next
End With
GetwsNames = sWSNames
End Function


Hello,

I have a work book that has been designed as a form.
The form goes through several pages and works in the following way.

sheet 1 - collect personal information. the information collected is copied over to a hidden worksheet (main template)
a button on the sheet unhides the next worksheet2. re selects the first sheet, hides it and then selects the new sheet2

sheet2 does the same as sheet 1 ... but moves on to sheet 3

and so on.

After passing through 5 stages the final worksheet (main template) is opened with all of the previously added data from the other worksheets neatly added in to the correct place.

This works like a charm.

________

I am aware that some people won't like to use macros, or will have them disabled, so i want to be able to do the following.

If a user opts in to using the macros on this workbook, it will work as i have described above.

If a user decides not to use macros with this workbook, it will automatically default to the main template so that all information can be added manually.

I hope this makes sense.

______

Best of luck to you guys. I appreciate this a lot.


No luck finding an answer? You could always try Google.