Select Different Lines From Different Worksheets
Jan 3, 2013
I would like to reduce the size of a macro, but not managed to have success.
My goal is to just have the cells of these worksheets (in the code below), selected
Code:
Sub SumazeCode()
Sheets("PEDIDO 2013").Rows("42:67").EntireRow.Select
Sheets("Print_Cliente").Rows("32:45").EntireRow.Select
Sheets("Print_Producao").Rows("30:43").EntireRow.Select
Sheets("Print_Orcamento").Rows("32:45").EntireRow.Select
Sheets("PEDIDO 2013").Rows("C43:D43").EntireRow.Select
End Sub
View 2 Replies
ADVERTISEMENT
Jan 14, 2009
Is it possible to have worksheets in 2 or more lines at the bottom of excel window?
View 9 Replies
View Related
Mar 31, 2009
I am having a problem with a sumproduct strand. I've written 4 different ways to no avail. What I'm trying to accomplish is to select specific the sum of various lines of data that fit within 3 parameters
1.) =SUMPRODUCT((Sheet1!$H:$H="XYZ"),(Sheet1!$G2:$G136="Withdrawal"),(Sheet1!$I2:$I136="113183"),Sheet1! $K2:$K136)
Response: #NUM!
2.)=+SUMPRODUCT(--(Sheet1!$H2:$H136='Sheet2'!$A$5),--(Sheet1!$G2:$G136="Withdrawal"),--(Sheet1!$I2:$I136='Sheet2'!$B$5),Sheet1!$K:$K)
Response: #Num!.................
View 2 Replies
View Related
Sep 11, 2012
I have spreadsheet application which have a ton of sheets and macro into it, and i work on devlop and program it , and add new feature and codes
i wana Module_Macro to find and replace lines of vba code and replace it with lines of codes .
i know ,i should use vbide and vbcompnent , but i dont know from where to start ?
View 9 Replies
View Related
Jun 17, 2014
I need to delete line if 2 conditions are meet.
If col A is blank and col D or E is blank then I want the line deleted for lines 1no more than 100 The option of D or E has me stymied I end up with it not working right. This is what I had tried
Dim lastrow As Long, c As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For c = 1 To lastrow
If Range("a" & c).Value " " and Range("d" & c). Value " " or Range("e" & c)Then ActiveSheet.HPageBreaks.delete
Next c
But doesn't work
I also need macro to run multiple worksheets which varies anywhere from 12 sheets to 50 sheets.
View 6 Replies
View Related
Apr 15, 2014
I want to add a user-form on a chart, which will have check-boxes that will allow me to select series(lines) that I want see and compare in a chart. Currently my line chart has 24 series (Lines) which makes the chart very difficult to view and looks very busy. How do I add list of check-boxes that will allow me to select one or multiple lines that I want to see at a time?
View 2 Replies
View Related
Feb 19, 2014
I have one excel file sheet in which we enter client data and another page in the same file that formats the data. Each new row is another client.
It is a simple formula in the data formatting sheet ='data sheet'A23 for example.
The problem is when we add client data above A23, say 2 rows, the format data page will follow the original cell and now say ='data sheet'A25.
How do I keep the cell as ='data sheet'A23 no matter what I do the original page?
View 1 Replies
View Related
Aug 3, 2006
I am trying to select all the worksheets within my active workbook, and then copy them all to a new workbook. due to later calculations, this can only be done using the Worksheet.copy command. I am getting away with using the Worksheet Array command at the moment, but this can only select named worksheets, and the code will need to be ammended if any worksheets are added/changed etc. Is there any Select all worksheets command that can be used without naming indiviual worksheets?
View 2 Replies
View Related
Jul 18, 2009
how to select the same range of cells on all worksheets via VBA?
I tried google several times but could not find anything that helps. I need to select a range of cells on all worksheets and then clear their content. However, google only tells me to select all sheets manually, which is not good since new ones are added every now and then.
Is there maybe a possiblity to select a range on all un-selected sheets?
View 10 Replies
View Related
Jan 19, 2009
I was checknig here for shortcuts...
http://office.microsoft.com/en-us/ex...116591033.aspx
I wanted to find a shortcut to select all sheets in a workbook. There are about 30 worksheets in my workbook, and selecting all is a pain.
in Excel 2003
View 9 Replies
View Related
Jun 19, 2014
I need to create a macro that inserts a row on a selected cell on a worksheet then performs the following function on 5 different worksheets. The worksheet names are "MD Forecast", "ED Forecast", "MB Forecast", "PW Forecast", and "RV Forecast".
The code I need ran on the worksheets is:
Formula:
[Code] .....
If possible, the row that is initially inserted would be the row that the code needs to be performed on. The function of the code above is to copy everything on the inserted row and 300 rows below, paste one row below the new row, and finally clear the contents of the inserted row. The column range for this to be done to is I:BH.
View 1 Replies
View Related
Jul 26, 2009
I am trying to write a macro to:
1. Randomly selects 50 rows from 834 on "worksheet 1". There are only two cells per row.
2. Then, it clears "worksheet 2" and "worksheet 3".
3. From each selected row in "Worksheet 1", it splits the two cells and copies the results into two different worksheets, one column per sheet.
4. The rows in the two new worksheets should be congruent (i.e. same selected order from worksheet 1). Keeping the order the same is important.
This is what I have so far:
--------------------
Sub Macro1()
Dim rng As Range
Dim iRow As Long
With Rows("1:834")
Do
iRow = Fix(Rnd() * 834 + 1)
If rng Is Nothing Then
Set rng = .Rows(iRow)
Else
Set rng = Union(rng, .Rows(iRow))
End If
Loop Until rng.Areas.Count >= 50
End With
rng.Select
Selection.Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End Sub
-------------------
View 9 Replies
View Related
Jun 24, 2008
I have a column where each cell contains the names of a variable number of worksheets in the format ["sheet1", "sheet2", etc. ] without the brackets but with the quotation marks. I would like to have some code that will select all of the sheets mentioned in a given cell. I found lots of threads about selecting an individual sheet based on a cell, but none about selecting multiple sheets. If it is easier, I can change the format of of the cells, or break each cell out into multiple cells in a given row. The best code I could come up with was
Sub Selects()
Dim strSht As String
Sheets("Criteria").Select
strSht = ActiveSheet.Range("L31")
Sheets( Array(strSht)).Select
End Sub
View 7 Replies
View Related
Jul 12, 2006
I have this workbook with 22 sheets and 21 are hidden. On the one open sheet there is a button that opens a userform with a listbox. I have radio buttons on the side to control whether the list box allows single selection, multiple selection, and extended selection. I want to change extended to open all sheets.
Anyway, I have the list box populated but I can't figure out how to code opening single sheets, multiple sheets, or all sheets depending on the radio button selected when the OK button is pressed. I know the listbox depends on the selected property but I am stuck. Here is the code I had but it is a mess. I am still new to VBA.
Private Sub OKButton_Click()
Dim Msg As String
Dim i As Integer
Dim UserSheet As Object
If ListBox1.ListIndex = -1 Then
Msg = "Please select a sheet."
Else
Msg = ""
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
For Each i In ListBox1..........................
View 3 Replies
View Related
Jan 20, 2012
I've got some code which some one posted me a link to on here which set up a pivot table on more that 1 sheets (using excel 2002).
I inserted this code into a rountine I do daily which works fine.
The problem Im having is that Im getting more and more info which I need to pivot over more than one sheet. So I was going to amend the rountine so It picked what ever sheets are in the work book to pivot.
At the moment I have to go into the code and name the sheets to pivot. Is there some code which can work out how many sheets that need pivoting and just do it with out naming them? and also just add a new sheet with the pivot on rather than having to name the destination?
I've posted the code i got given below and have highlighted the bit where I have to name the sheets.
Code:
'---------------------------------------------------------------------------------------
' Procedure : CreateConnection
' Author : KL (Kirill Lapin)
' Date : 18/08/2009
[Code]....
View 8 Replies
View Related
Sep 29, 2012
I am making custom time sheets to suit our agriculture business - these excel sheets get sent out to the different farm managers who send back in staff times.
This code below is brilliant and works perfect for our needs. However I need to be able to emailPDF the sheets not print.How / where do I change code so the selected sheets go to Save & Send via email as a PDF instead of going straight to the default printer .( hard copy )
VB:
Option Explicit
Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
[Code] .....
View 5 Replies
View Related
Nov 4, 2013
I have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.
I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.
Sub WorksheetLoop()
'
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet
[code]....
View 2 Replies
View Related
Oct 30, 2012
I have a multiselect listbox with values that gets populated from a sql statement, and I would like to get is the first or second index from the selected item. I know how to get the listindex from a combobox by using:
VB:
cbnumber.List(.ListIndex, 0)
How can I loop through and get the 1st index number for the selected items only from the listbox? I want to pass this index number to another sql statement.
View 5 Replies
View Related
Jul 7, 2008
I want to put a range select statement to select a cell and count down 10 cells and copy.
View 9 Replies
View Related
Oct 4, 2007
I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now.
Private Sub Worksheet_Activate()
If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then
Me.ClearPrevious.Visible = True
Else
Me.ClearPrevious.Visible = False
End If
Sheets("MASTER").Select
End Sub
View 9 Replies
View Related
May 15, 2013
I have read that there is a VBA macro in F11, but I also read that it would only sort the workshhet names, but not the data. I have Excel 2010.
View 2 Replies
View Related
Jan 16, 2009
Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?
View 2 Replies
View Related
Jul 7, 2014
Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)
What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).
But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.
Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".
View 4 Replies
View Related
Aug 10, 2009
I'm trying to perform the same process to all the worksheets in my workbook. This is the code I have now, but it will only apply to the single active worksheet:
View 2 Replies
View Related
Nov 16, 2006
I need to get a macro to select all the data in column "A", sort it in ascending order, omit the blanks if any, then select (highlight) all the data so that another macro can be run.
When I record it, it will only record up to the last row I highlight but the data always changes so there could be more or less.
View 9 Replies
View Related
Aug 13, 2007
I have column A with various values in cells.
For instance, DG, GS, HG etc
I wanna do if a cell in column A is equal to DG then select the entire row that contains the cell. then call other sub.
View 9 Replies
View Related
Oct 28, 2008
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
View 4 Replies
View Related
Apr 26, 2006
How do I modify this macro so that the worksheet array will select all the worksheets except sheet 1?? My workbooks will have varying numbers of worksheets ...
View 3 Replies
View Related
Nov 22, 2012
I have a sheet which looks a bit like this
Day
A
B
Day
Cost A
Cost B
[Code]....
For each column with a header A, B (these are people), 1 in their column represents they works that day on a project. The day columns are days of the week, and people cost different amounts on different days. I need to work out the total cost for each person for the working period.
The cost for the first day for person A is =if(b2=1,vlookup(a2, f2:h8, 2),0)
How do I sum the costs for each day in a single formula without creating a cost per day column for each person i.e. sum each line of vlookups?
View 1 Replies
View Related
Feb 19, 2014
I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :
7,7,K,A,8,7
K,6,7,7,A,9
To reach the conclusion I need to build six such functions:
=1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66))
=1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66))
=1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66))
=1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66))
=1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66))
=1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))
The result is 4
Is there a single formula that will give the result ?
Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?
View 8 Replies
View Related
