Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Macro for multiplication table

I'm trying to create a multiplication table for my daughter, and I thought I'd tinker with a macro that will randomly choose two numbers to multiply.

My idea is to create a simple macro that will randomly choose numbers between 1 - 20, multiply them, and ask the user for input until the correct answer is given. Basically, something like flash cards in Excel. I'm sure someone's done something similar before, I just haven't found it yet.

Any ideas?

Thanks.


Post your answer or comment

comments powered by Disqus
How can I copy the command button and macro for multiple rows ?

Private Sub CommandButton1_Click()
Dim Outlook As Object
Dim Appointment As Object
Dim Category As String
Dim Time As Variant

Const Item = 1

Set Outlook = CreateObject("Outlook.Application")
Set Appointment = Outlook.CreateItem(1)
Time = "08:30AM"

Appointment.Location = Range("a2") & ", " & Range("d2") & ", " & Range("e2")

Appointment.Subject = Range("i2")

Appointment.Start = DateAdd("d", 350, Range("b2")) & " " & Time

Appointment.Body = Range("a2") & ", " & Range("b2") & ", " & Range("c2") &
", " & Range("d2") & ", " & Range("e2")

Appointment.ReminderPlaySound = True
Appointment.Display

'Outlook.quit
'Set Outlook = Nothing
End Sub
--
problem

I need help creating a spinner macro for Pivot Tables. I have seen how they
work on Contextures.com. but don't understand how to create one. Ms.
Dalgleish referred my question to this board.

I am competent in Excel and comfortable with recording basic macros, but do
not know VBA at all. Is this doable for someone at my level? I am using
Office 2003.

Thanks.
Kip

This is directed at rylo, the creator of the original macro, but any help would be appreciated.
Original thread http://www.excelforum.com/excel-programming/614101-macro-for-multiple-tabs-from-a-data-set.html

I'm trying to tweak the macro you provided to Brigitte.
The original macro created sheets based on the values a column, then copied over relevant information.

Sub
create_tabs()

'AUTHOR: rylo
'DATE: 11/9/07
'REFERENCE: http://www.excelforum.com/showthread.php?t=614101
  Dim nodupes As New Collection
  'build a unique list of the values in column G
  On Error Resume Next
  For i = 2 To Cells(Rows.Count, 7).End(xlUp).Row
    nodupes.Add Item:=Cells(i, 7).Value, key:=CStr(Cells(i, 7).Value)
  Next i
  On Error GoTo 0
  
  'create a new sheet for each of the column G values, and create the headings
  For i = 1 To nodupes.Count
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = nodupes(i)
    Range("A1:BA1").Value = Sheets(1).Range("A1:BA1").Value
  Next i
  
  'create the criteria range
  Sheets(1).Activate
  Range("bd1").Value = Range("G1").Value
  
  'use advanced filter to copy over the relevant data
  For i = 1 To nodupes.Count
    Range("bd2").Value = nodupes(i)
    Range("A1").CurrentRegion.AdvancedFilter action:=xlFilterCopy, criteriarange:=Range("bd1:bd2"),
copytorange:=Sheets(nodupes(i)).Range("A1:ba1")
  Next i
  
  'clean out the criteria range
  Range("bd1:bd2").ClearContents
  
End Sub
The main changes are changing which column the unique list is built from, increasing the range of copied values, and changing where the criteria range is.

When vb gets to
it says subscript out of range.

I can't figure out why it's having troubles with it.

I appreciate your help,

Chris

I'm (Fairly) new to Excel and I have been fooling around with some worksheets. I am trying to figure out how to work the formulas currently and I cannot seem to do it (if it is possible). I was wondering, can you write a single formula that would fill in the content for a multiplication table if the numbers 1-9 are the headers for the columns and rows?

Thanks in advance for any help =)

Hey everyone, hope you can help me.

In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof.

The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible.

One suggestion i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them.

All suggestions are valid and i would love as many as possible.

Thank you in advance.

Hello,

I've been working on this problem now (intermittently) for about a month, and I've Googled to my fullest extent, but I've hit the wall and I need help from a more experienced VBA user.

What I am trying to do is write a macro that will automatically grab multiple data sets, then chart them, format the charts, name the charts, etc. My spreadsheet is set up like this: in the A:A column, I have my x-values. More specifically, this column contains two sets of x-values (time starting at zero), one of which corresponds to an experimental set of temperature data, the other corresponding to a modeled set of temperature data. So this column is discontinuous at one point, where the first x-dataset ends and the second one begins again (at time = zero). The y-data are then listed in all subsequent columns, and are located adjacent to one another...so y-data-set1 lies in columns B:C, y-data-set2 in columns D:E, and so on. If you were to see the data (which I probably shouldn't share on the web!), you would see columns A:B containing data, but the C column data wouldn't show up until hundreds of rows down, when the second time range begins.

In the end, each chart should have two temperature-time curves, one of which corresponds to (for example) the data range (A2:A500, B2:B500), and the other, (A501:A700, C501:C700). As long as this data is correctly “grabbed,” the formatting part of my code is fine, but something's apparently wrong with my grabbing method. I devised a For-Next procedure to cycle through the data columns based on the variable "col," (see the code below), and I am using a simple If-Then argument to ensure that the macro will stop trying to grab data when it comes to an empty column. However, although the macro seems to loop properly, and the chart formatting/naming is working, my problem is the following:

After the first chart is plotted, the data selection corresponding to that chart remains selected, so that all subsequent charts plot a cumulative data set (i.e. chart1 plots data from A:C, chart2 plots data from A:E, chart3 plots data from A:G, etc.), which I do not want. I can’t find a way to “deselect” the data after charting it, but I don’t believe I should have to do so, anyway. I had some other problems regarding sourcedata specification and x-values showing up incorrectly, but they seemed to vanish overnight(!). So here is the code I have right now, which works perfectly except for the fact that it’s plotting cumulative charts of data:

Sub all_charts_create_and_format()

' create and format all charts macro - for one TC test
'
' Macro created 1/11/2006 by mmf

Dim col As Integer

For col = 2 To 100 Step 2

Sheets("Model vs. Experimental").Activate

If IsEmpty(Cells(2, col)) = False Then

Sheets("Model vs. Experimental").Range("A1:A5000", Range(Sheets ("Model vs. Experimental").Columns(col), Sheets("Model vs. Experimental").Columns(col + 1))).Select 'this is the data selecting method

Charts.Add

ActiveChart.ChartType = xlXYScatterSmooth

ActiveChart.setsourcedata Source:=Sheets("Model vs. Experimental").Range("A1:A5000", Range(Sheets("Model vs. Experimental").Columns(col), Sheets("Model vs. Experimental").Columns(col + 1))) 'I had to use this seemingly redundant sourcedata method because originally, the data was not being plotted correctly

ActiveChart.Location Where:=xlLocationAsObject, Name:="Model vs. Experimental"

(I have excluded all of the chart formatting code, but this is where it lies in the actual code)

End If

Next col

End Sub

I would greatly appreciate any help in this matter; I feel like my data selecting method must contain an error that I am not familiar enough with VBA to understand. And please, try to keep it simple!

Thanks for reading,

Matt

Hi Guys, i am new here as well as in setting up macros in excel vba .

I have recorded the MACRO for my massive excel file pivot tables. i have recorded 3 macros for 3 work sheets ( please guide if i can set one macro for all 3 sheets, that excel work book got 6 sheet)

I need help to record this in a better way as my source file changes every day in a perticular folder and i have to change the name in macro every day ...is there anyway i can automise this ?

I have to activate ( open ) the source file to run the macro ..is there any way i can get result when file is closed and have results in new excel work book ?

I am really sorry if i sound bit confuse here ...thanks in advance whoever can help...

Gaurang Patel
PLEASE SEE THE DETAILS BELOW for 3 MACROS which i've created


	VB:
	
 
Sub Macro1() 
     '
     ' Macro1 Macro
     '
     '
    Windows("BOOK1.xlsx").Activate 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand") 
        .Orientation = xlPageField 
        .Position = 1 
    End With 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Style code") 
        .Orientation = xlRowField 
        .Position = 1 
    End With 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Colour-Fit") 
        .Orientation = xlRowField 
        .Position = 2 
    End With 
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable1").PivotFields("Total qty"), "Sum of Total qty", xlSum 
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Style code").Subtotals = _ 
    Array(False, False, False, False, False, False, False, False, False, False, False, False) 
    Windows("TRIAL MACRO FOR TARGET STOCK").Activate 
End Sub 
Sub Macro2() 
     '
     ' Macro2 Macro
     '
     '
    Windows("BOOK1.xlsx").Activate 
    ActiveWindow.SmallScroll Down:=-9 
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Brand") 
        .Orientation = xlPageField 
        .Position = 1 
    End With 
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Quoted seasons") 
        .Orientation = xlPageField 
        .Position = 1 
    End With 
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Style") 
        .Orientation = xlRowField 
        .Position = 1 
    End With 
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Style name") 
        .Orientation = xlRowField 
        .Position = 2 
    End With 
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Colour") 
        .Orientation = xlRowField 
        .Position = 3 
    End With 
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable2").PivotFields("Protected Stock in Stock qty (before)"), _ 
    "Sum of Protected Stock in Stock qty (before)", xlSum 
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable2").PivotFields("Protected Stock in Stock qty (after)"), _ 
    "Sum of Protected Stock in Stock qty (after)", xlSum 
    With ActiveSheet.PivotTables("PivotTable2").DataPivotField 
        .Orientation = xlColumnField 
        .Position = 1 
    End With 
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable2").PivotFields("Available to sell (before)"), _ 
    "Sum of Available to sell (before)", xlSum 
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable2").PivotFields("Available to sell (after)"), _ 
    "Sum of Available to sell (after)", xlSum 
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Colour").Subtotals = Array( _ 
    False, False, False, False, False, False, False, False, False, False, False, False) 
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Style name").Subtotals = _ 
    Array(False, False, False, False, False, False, False, False, False, False, False, False) 
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Style").Subtotals = Array( _ 
    False, False, False, False, False, False, False, False, False, False, False, False) 
    Range("D5").Select 
    ActiveSheet.PivotTables("PivotTable2").DataPivotField.PivotItems( _ 
    "Sum of Protected Stock in Stock qty (before)").Caption = "DC BEFORE" 
    Range("D9").Select 
    Columns("D:G").ColumnWidth = 16.43 
    Range("E5").Select 
    ActiveSheet.PivotTables("PivotTable2").DataPivotField.PivotItems( _ 
    "Sum of Protected Stock in Stock qty (after)").Caption = "DC AFTER" 
    Range("E8").Select 
    Range("F5").Select 
    ActiveSheet.PivotTables("PivotTable2").DataPivotField.PivotItems( _ 
    "Sum of Available to sell (before)").Caption = "ATS BEFORE" 
    Range("F8").Select 
    ActiveSheet.PivotTables("PivotTable2").PivotSelect _ 
    "'Sum of Available to sell (after)'", xlDataAndLabel, True 
    Range("G5").Select 
    ActiveSheet.PivotTables("PivotTable2").DataPivotField.PivotItems( _ 
    "Sum of Available to sell (after)").Caption = "ATS AFTER" 
    Range("G3").Select 
    Range("G8").Select 
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable2").PivotFields("Store sales last 6 months"), _ 
    "Sum of Store sales last 6 months", xlSum 
    Range("H8").Select 
    Windows("TRIAL MACRO FOR TARGET STOCK").Activate 
End Sub 
Sub Macro3() 
     '
     ' Macro3 Macro
     '
     '
    Windows("BOOK1.xlsx").Activate 
    ActiveWindow.SmallScroll Down:=-12 
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Brand") 
        .Orientation = xlPageField 
        .Position = 1 
    End With 
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Style") 
        .Orientation = xlRowField 
        .Position = 1 
    End With 
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Style name") 
        .Orientation = xlRowField 
        .Position = 2 
    End With 
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Colour") 
        .Orientation = xlRowField 
        .Position = 3 
    End With 
    With ActiveSheet.PivotTables("PivotTable3").PivotFields( _ 
        "Target stock quoted season") 
        .Orientation = xlPageField 
        .Position = 1 
    End With 
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable3").PivotFields("Positive store stock"), _ 
    "Sum of Positive store stock", xlSum 
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Colour").Subtotals = Array( _ 
    False, False, False, False, False, False, False, False, False, False, False, False) 
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Style name").Subtotals = _ 
    Array(False, False, False, False, False, False, False, False, False, False, False, False) 
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Style").Subtotals = Array( _ 
    False, False, False, False, False, False, False, False, False, False, False, False) 
    Range("C8").Select 
    ActiveWindow.SmallScroll Down:=-12 
    Windows("TRIAL MACRO FOR TARGET STOCK").Activate 
End Sub 

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


Hello,

I have been using the below macro and it has been working great, but I wanted to update it for multiple criteria. Currently it searches on sheet "Urgent Reformat" for "CATI" in column E, and if it is present the row is copied to the sheet "TP." I wanted to update my macro to look for CATI, IA, & DP in column E and then copy those rows to sheet "TP."

I know this is probably an easy update, but I can't get the code to work for me. Thanks for any assistance.

My Current Macro:

Sub CATISheet()
Sheets("Urgent Reformat").Select
Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 2
LSearchRow = 2

'Start copying ISQ Raw Data to row 2 in CATI (row counter variable)
LCopyToRow = 2

While Len(Range("E" & CStr(LSearchRow)).Value) > 0

'If value in column E = "CATI", copy entire row to CATI
If Range("E" & CStr(LSearchRow)).Value = "CATI" Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into CATI in next row
Sheets("TP").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("Urgent Reformat").Select

End If
LSearchRow = LSearchRow + 1

Wend
'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

Exit Sub

Err_Execute:
MsgBox "An error occurred."
End Sub

Hi ,

I want a macro for creating pivot table.However I get an error after a
certain comand (indicated by------)

Following is the set of commands I have typed -

Sheets("Final Sheet").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Final Sheet'!R1C1:R1567C43").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("CODE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Type"), "Count of Type", xlCount
Range("A3").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields ("Count of Type"). _
Orientation = xlHidden
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Type")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataFiel d
ActiveSheet.PivotTables
("PivotTable2").PivotFields("CTRY"), "Count of CTRY", xlCount

----------------------

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pre Checks"
Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("CHECKS").Select
Range("A2").Select
ActiveSheet.Paste

Can any one help me with this?

Hi, I'm having trouble building a macro on pivot tables. I hope some experts
here can help solve my problem. Any inputs will be greatly appreciated!!

I'm trying to build a macro to refresh data in multiple pivot tables. The
pivot tables are build below one another, and additional rows are inserted
between pivot tables.

When i built a macro to link all tables to the data in the first (master)
pivot table, I found Excel was refreshing the table based on the exact cell I
was clicking. This has become a problem since new rows are inserted between
tables when new data comes in, and pivot tables are no longer at those cells.
The macro i've built cannot refer to the tables and crashed as a result.

I was wondering if I can define or use a dynamic name to select a pivot
table instead of selecting a cell. My colleagues does not know VB and are not
interested in using codes to solve this problem. I was wondering if this can
be done simply.

Hope someone can offer me help. Thanks in advance.

Hi ,

I want a macro for creating pivot table.However I get an error after a
certain comand (indicated by------)

Following is the set of commands I have typed -

Sheets("Final Sheet").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Final Sheet'!R1C1:R1567C43").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("CODE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Type"), "Count of Type", xlCount
Range("A3").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Type"). _
Orientation = xlHidden
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Type")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField
ActiveSheet.PivotTables
("PivotTable2").PivotFields("CTRY"), "Count of CTRY", xlCount

----------------------

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pre Checks"
Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("CHECKS").Select
Range("A2").Select
ActiveSheet.Paste

Can any one help me with this?

I want a macro to perform the following thingsI have attached an excel sheet for better understanding)

1. The content in sheet1 should be copied and pasted in sheet 3

2. The steps containing the field name(field is in sheet 2) should be replaced with value under the field ( for each step in accordance with sheet 2 )

3. The fields (in sheet 2) for which there is no value, should not be displayed in the sheet3 (i.e. since there is no rating1 for performance 1 the B4 step which will be copied to sheet 3 should not contain (4= ) field

and also it would be better if it is done for multiple fields...
Thanks a lot in advance....

Hello,

I've been working on this problem now (intermittently) for about a month, and I've Googled to my fullest extent, but I've hit the wall and I need help from a more experienced VBA user.

What I am trying to do is write a macro that will automatically grab multiple data sets, then chart them, format the charts, name the charts, etc. My spreadsheet is set up like this: in the A:A column, I have my x-values. More specifically, this column contains two sets of x-values (time starting at zero), one of which corresponds to an experimental set of temperature data, the other corresponding to a modeled set of temperature data. So this column is discontinuous at one point, where the first x-dataset ends and the second one begins again (at time = zero). The y-data are then listed in all subsequent columns, and are located adjacent to one another...so y-data-set1 lies in columns B:C, y-data-set2 in columns D:E, and so on. If you were to see the data (which I probably shouldn't share on the web!), you would see columns A:B containing data, but the C column data wouldn't show up until hundreds of rows down, when the second time range begins.

In the end, each chart should have two temperature-time curves, one of which corresponds to (for example) the data range (A2:A500, B2:B500), and the other, (A501:A700, C501:C700). As long as this data is correctly “grabbed,” the formatting part of my code is fine, but something's apparently wrong with my grabbing method. I devised a For-Next procedure to cycle through the data columns based on the variable "col," (see the code below), and I am using a simple If-Then argument to ensure that the macro will stop trying to grab data when it comes to an empty column. However, although the macro seems to loop properly, and the chart formatting/naming is working, my problem is the following:

After the first chart is plotted, the data selection corresponding to that chart remains selected, so that all subsequent charts plot a cumulative data set (i.e. chart1 plots data from A:C, chart2 plots data from A:E, chart3 plots data from A:G, etc.), which I do not want. I can’t find a way to “deselect” the data after charting it, but I don’t believe I should have to do so, anyway. I had some other problems regarding sourcedata specification and x-values showing up incorrectly, but they seemed to vanish overnight(!). So here is the code I have right now, which works perfectly except for the fact that it’s plotting cumulative charts of data:

Sub all_charts_create_and_format()

' create and format all charts macro - for one TC test
'
' Macro created 1/11/2006 by mmf

Dim col As Integer

For col = 2 To 100 Step 2

Sheets("Model vs. Experimental").Activate

If IsEmpty(Cells(2, col)) = False Then

Sheets("Model vs. Experimental").Range("A1:A5000", Range(Sheets ("Model vs. Experimental").Columns(col), Sheets("Model vs. Experimental").Columns(col + 1))).Select 'this is the data selecting method

Charts.Add

ActiveChart.ChartType = xlXYScatterSmooth

ActiveChart.setsourcedata Source:=Sheets("Model vs. Experimental").Range("A1:A5000", Range(Sheets("Model vs. Experimental").Columns(col), Sheets("Model vs. Experimental").Columns(col + 1))) 'I had to use this seemingly redundant sourcedata method because originally, the data was not being plotted correctly

ActiveChart.Location Where:=xlLocationAsObject, Name:="Model vs. Experimental"

(I have excluded all of the chart formatting code, but this is where it lies in the actual code)

End If

Next col

End Sub

I would greatly appreciate any help in this matter; I feel like my data selecting method must contain an error that I am not familiar enough with VBA to understand. And please, try to keep it simple!

Thanks for reading,

Matt

Trying to figure out a way to assign multiple buttons to one macro, but have the macro perform differently depending on which button is pressed. Not sure if that's possible.

Hypothetical scenario: I have 2 sheets (Home, SheetB). On the home worksheet, I have three buttons (Button1, Button2, Button3). I would like each button to activate a different cell within SheetB. For example, Button1 selects Sheets("SheetB").Range("A5"); Button2 selects Sheets("SheetB").Range("A200"); Button3 selects Sheets("SheetB").Range("A70555").

In reality, I will have many more buttons, each with a respective range that needs to be selected. I just want to avoid making a separate macro for each one, if possible. Any way to do this?

Thanks in advance!

Hi,

I'm new to using macros in Excel and I've recently received help creating a macro to enable my worksheet to autosort by date upon opening the workbook, as well as automatically go to the most recent date listed. It works great for me, but it does not work for other users in my office. Is there something additional the macro needs to operate for multiple users?

Here's the code:

Private Sub Workbook_Open()
Dim DestinationRow As Integer

Cells.Select
ActiveWorkbook.Worksheets("Events").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Events").Sort.SortFields.Add Key:=Range("A2:A4000") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Events").Sort
.SetRange Range("A2:D4000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

DestinationRow = Worksheets(1).Range("Z1").Value
Worksheets(1).Range("A" & DestinationRow).Activate
End Sub

And here's the formula in cell Z1, if it helps:


Thanks in advance for your help!

Hi Folks,

I have a workbook with a userform and I have added some codes also to show the Multiplication Table of textbox1 value (for 1 to 10). The entered code returns only one multiplication result, e.g. If I entered “2” in the textbox1 I am getting the result 2 x 10 = 20 but I want to show 1 x 2 = 2, 2 x 2 = 4, 3 x 2 = 6...till 10 x 2 = 20. I am unable to build the looping code. I have attached a file of what I'm trying to accomplish. Thanks in advance for your expertise!

Joshi

Hi There, good day, i am currently working on an excel project for leave management. I need to create a macro for the following funtions; On Sheet 2 - from column A to column K is an appended leave report generated by the managers of an organization..... whereas column P to T of the same worksheet have the list of all the staff within the main unit. What i need the macro to do is1. I would need the macro compare values in range (p14150) to the values in range a:a e.g. P15 has a value of 1188338, the macro should then compare cells A1 onwards and look for the similar value. Once identified, the macro should then copy column E of the same row which in the sample attached is E11. copies this value and place it in T152. It should then go to Sheet 2 and take the value of P15 and compare it with Sheet 3 column A and when it finds the same value, then on the same row, it should take the value of T15 of Sheet 2, and does 2 checks in column K of sheet 3 before it paste the value. Check is to see cell if cell value is blank, if it is, then paste value else check if cell value in column K sheet 3 if it is similar to cell value T15 in sheet 2, if it is, then ignore and if value is different, then paste new value and inform user with a message box. 3. The macro should then copy cells R15 and S15 on Sheet 2, goes to the similar row of page 3, and paste the value of R15 to column O of Sheet 3 and paste S15 to column R of page 3. The macro created for this should actually be a loop to check for the range of P14:P150 of Sheet 2 on all 3 mentioned requirements above and for this case i gave the example of the data in P15 of sheet 2. An addtional infor is the data in range P14:P150 of sheet 2 will be similar to Range A14:A150 of sheet 3 as their are actually mapped to a different sheet.I hope i have made it detailed enough for the requirement to be understood. Appreciate if anyone out there could assist me on this. Thank you.

Hey, hope all you guys can help me out.

What I have in cell K13 is a function to lookup a certain cell using a column reference and a row reference. The rows/columns represents a measurement and the numbers in the table represent a cost for that certain dimension of, what will be, Conservatory Roofs.

This works fine but there is many other tables which i need to look up as there are many different styles of roof, some costing less, some costing more. This is why i need help, the function in cell K13 only works for one table (currently Table 1) and i have had no success in trying to make a formula for more than one table. On the real spreadsheet they're is about 15 different tables so this needs to be rock solid. All the tables have the same dimensions (ie, all the tables are the same size). I was thinking perhaps an Indirect function will help, but i am un familiar with them.

Hope you can help

I have a Excel spreadsheet with 10 columns and 900 lines.

I wrote a macro to take information off of line 2 on worksheet #1 and place
it in a form on worksheet #2

How do I get the macro to run on different lines say line 4 and then line
498 and then 987. (The macro is written for line #2)

The form on sheet #2 is the same. Do I need to write this same macro for
each line? Is there a way to run a macro on a highlighted area? Can I put
a button at the end of each line?

Any help will be appreciated.

Hi experts,
there are three columns A, B and C Column A and B has
numbers and C displays the sum of A and B. if the sum exceeds 30 it should
generate an email. now i want to do this with a macro for multiple records.
please help me

Hi. I am new to this forum and also new to macros to MS Excel. However, I do have a basic understanding of C++/VB codes and structures. So I really appreciate any help anyone can offer.

I currently am working on writing a macro for Excel to open a text data file and using the the data to make a graph. I already finished writing the part for opening and importing the file. However I am having trouble do the graphing part.

The data is in 3 columns. One is for x-axis (Column 1), one is for y-axis (Column 2) and one is for the IDs (Column 3). There are undefined number of IDs and the number entries under the same the IDs can vary, however they are all grouped together.

For example, the three columns would look like something below (sorry, I don't know how to embed the file in...):

Col1 (x)______Col2 (y)______Col3 (ID)
25.05______218.24______244
349.18______218.82______244
399.66______218.64______244
450.90______217.68______244
800.62______195.84______244
1200.61______160.37______244
24.94______215.09______686
50.91______214.91______686
99.97______214.12______686
150.45______213.16______686
401.91______204.67______686
450.16______202.09______686
800.66______178.10______686
1202.50______146.92______686
24.98______218.56______351
50.43______218.48______351
99.97______218.24______351
149.62______217.97______351

...and so on. There could be as few as one ID that only have one entry in the file or as many as 100 IDs with 100 entries under each of them. The part I cannot figure out is how to graph all the Column 1 vs Column 2 data with the same IDs (same values in Column 3) on the same graph.

Thanks for any help that you guys can offer.
Edit/Delete Message

Hey guys,

I am new to this forum and am desparately in need of some help! I just recently started a job and they asked me to build out a macro for some excel spreadsheets but I do not even know where to begin!

Here is the situation:
I have five different excel workbooks. The first four each have data that is updated from a database each month. The fifth is a workbook with four (4) different tabs in it. Each tab pulls data from one of the other four workbooks respectively and then outputs it the compiled workbook. The information from the first four workbooks always starts in the same place (A16) but the number of columns and rows differs, so I need to be able to copy from A16 to the last column and last row and then output that information into the respective tab in the 5th workbook starting in A16 again.

Is there anyway to link all five of these workbooks so that a single macro can be run on command and update the 5th workbook? If so, how? What would the code have to look like for this?

PLEASE HELPPPPPP!!!!

Hi All,

I have the below macro which opens a file from a directory and removes it's password and then saves it back on the same directory. Now I have excel workbook where I have a list of files in column A and there Passwords in Column B and Action to be taken in Column C .i.e. Remove Password. I want to the below macro to loop through the list of the files in Column A and open the workbooks and remove the passwords of those workbooks where in Column C against it is mentioned to remove password.

Sub RunMacroforMultiplefiles()
Application.DisplayAlerts = False
'Dim X as Range
'For Each Cell in X

    Workbooks.Open Filename:= _
        "C:Book2.xls", _
Password:="abc123", writerespassword:="abc123"

    ActiveWorkbook.SaveAs Filename:= _
        "C:Book2.xls", _
        FileFormat:=xlNormal, Password:="", writerespassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Windows("Run Macro for Multiple workbooks.xls").Activate
Application.DisplayAlerts = True
End Sub
And Below is the code which I have which can run the macro on all files present in a directory

Sub list_um()
Dim F As String
Dim roww As Long
roww = 0
Dim FileLocSpec As String
FileLocSpec = "C:Temp*.xls"
F = Dir(FileLocSpec)
Do Until F = ""
roww = roww + 1
Cells(roww, 1).Value = F
F = Dir
Loop

Set r = Range("A1")
While r.Value <> ""
Workbooks.Open Filename:="C:Temp" & r.Value
Call macroxx
ActiveWorkbook.Save
ActiveWorkbook.Close
Set r = r.Offset(1, 0)
Wend
End Sub

Please find the attached macro file.

Thanks a lot for your help in advance.

Hi!

I'm not very good at macro I need to run this macro for all of the sheets in the workbook when I run it once.

Sub calcols()
    Dim rcnt As Long, Cname As String

    rcnt = Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To rcnt
        Cname = Range("C" & i).Value
        If Cname = Range("B" & i).Value Then
            Range("F" & i).Value = Range("A" & i).Value - Range("D" & i).Value
        Else
            For j = 1 To rcnt
                If Cname = Range("B" & j).Value Then
                    Range("F" & i).Value = Range("A" & j).Value - Range("D" &
i).Value
                End If
            Next
        End If
    Next
End Sub



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