Sum On Selected Sheets

Feb 12, 2008

I have a worksheet that has data for separate sheets for each date of the month.

Therefore, the month of January has sheets for 31 days.

I need to sum a particular cell in each sheets only for Monday.

Therefore, I need the formula to sum cell B4 only for the sheet for Jan 7, Jan 4,Jan 21 and Jan 28.

View 9 Replies


ADVERTISEMENT

Search Only Selected Sheets

Feb 1, 2007

I'm probably asking something very complicated in coding but I
was hoping someone could give me a macro code that would do a search
for a cell's input, but only search selected sheets.

example: "Please enter the word you are searching for here, and click the enter button:"

'here' would be cell C20. the value of cell C20 would be searched for on various
selected sheets written in the macro only.

View 12 Replies View Related

Copy Selected Sheets

Dec 18, 2008

I use the command ActiveWindow.SelectedSheets.Copy to copy selected sheets into a new workbook. As a result of this line is a new workbook created and a the selected sheets are pasted into the new workbook. Is it possible to paste only values by using a single line like this?

View 2 Replies View Related

Rename All Selected Sheets (For Each)

Apr 8, 2009

how I can change this code to apply to only the sheets that are selected (grouped) as opposed to every sheet in the workbook?

For Each shName In ActiveWorkbook.Sheets

View 9 Replies View Related

Macro To Print Selected Sheets ...

Mar 16, 2009

in creating a macro so that I can print from sheet 1 to sheet name "XYZ" as an array. In other words I want to print selected sheets as one command so that page number in the footer will automatically change.

View 14 Replies View Related

Macro For All Selected Sheets Into One PDF File

Nov 24, 2013

I'm looking for a string of code that will prompt the user to check-off boxes specifying the sheets they'd like to print, then have it save into a SINGLE pdf file. I'm using the below code right now and it will prompt check-boxes, and print using PDF but it does it one sheet/one PDF at a time. Any way to mod this so it will combine and save into a single PDF?

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False

[Code] ........

View 2 Replies View Related

Email Selected Sheets Via Userform

Apr 3, 2014

I am trying to set up something similar to the old "print dialogue" box whereupon the user presses a command button on the userform and all sheets within the work book are listed (eg:sheet1, sheet2 etc) The user can select any amount of sheets and and using "OK" outlook is fired up- pretty similar to Ron de Bruins code for email.

I have tried taking the print dialogue vba and stooping it at copy, then trying to gets Ron's code to continue- without success.

There are great bits of code I've seen for select tabs and selct sheets from listbox, but I have found none to email once selected.

View 4 Replies View Related

Copy Selected Sheets To New Workbook

Jan 25, 2009

I have a workbook with about 25 sheets. All the sheets are named. I'd like either a Macro or some VBA code whcih, when the user clicks a button will Unhide selected sheets, say "Equip Labor", "Equipment", Proj. Summary" and "Implementation Guide" and then copy these sheets into a new workbook created on the fly (Book1.xls) and then in the original workbook re-hide all the selected sheets. This way the user can save the new workbook as whatever name they want.
Is this possible? The closest post I could find was this:
http://www.mrexcel.com/forum/showthr...opy+Worksheets
I tried to modify this, but I'm not that good yet with VBA.

View 9 Replies View Related

Refresh Queries On Selected Sheets

Aug 4, 2009

I was wondering if there was a way to Refresh Queries on a sheet by sheet basis. I have several sheets of queries, and I would like to refresh all of them except one or two.

The only way I know how to do this is to stick a line of code for each query into the script while leaving out the queries I don't want. I was hoping there was a more elegant way to do it. If not, no big deal. I'll just do it the old fashioned way.

View 8 Replies View Related

Copy User Selected Sheets

Jun 5, 2006

how you would write a macro to move a selection of sheets to a workbook called Digi's in the D drive. Also if there isn't a Workbook called Digi's already it needs to add one. I've found some code to loop through sheets but nothing to show what sheets the user has selected

View 4 Replies View Related

Process Sheets Selected From Listbox

Nov 17, 2007

I have a workbook with roughly 25 sheets, each sheet represents a customer. Each month, I want to be able to run a macro in the workbook that will produce a UserForm containing a Listbox of each unhidden customer (worksheet) in the workbook. After I select all of some of the customers, hit a "process" button which will run a macro on each of the selected customers from the UserForm one worksheet at a time.

I currently have code written to produce the UserForm and populate the list, but I am uncertain how to write the code for the "process" button to run the macro on each selected customers one at a time. All I know how to do is have excel select all the chosen customer worksheets all at once.

View 8 Replies View Related

Excel Unhide Sheets When Check Is Selected?

Sep 19, 2013

I am trying to create a spreadsheet which will open with sheet1 showing 'Terms and conditions' with 2 check box's. Once the check box's are checked I then want subsequence sheets to unhide. E.g check box 1 ticked - will unhide sheet 2 - (containing the report) check box 2 ticked - will unhide sheet 3 - (containing the data)

Basically, by the use of check box's I am trying to get the user to read and agree to terms/disclaimer of the use of data. Therefore can the workbook also always open with the one sheet 'terms and conditions' showing only.

View 2 Replies View Related

VBA - Apply A Code To All Selected Sheets To Group

Feb 24, 2014

I would like to group some columns to all the sheets that I will have selected. Unfortunately the below code only apply the code to the sheet I am looking at.

Code:
Sub Group()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
With ws.Range("F:Q").Group
End With
Next ws
End Sub

View 3 Replies View Related

Pulling Data From Sheets If Certain Name Selected In Dropdown

Mar 29, 2014

I am creating an excel workbook for my consignment store to keep track of sales for the store as well as the consignors. I have a sheet for each day of the month, and in the sheets I have it to where I can select the consignor from a drop down box. I am trying to create a sheet at the end of the workbook that would allow me to use the drop-down list to select a consignor and have it pull the sales for the month onto that sheet, an end of the month summary of sales. I don't mind creating a formula for each day. IM taking my time on this to make it work the way I want it to. But I cannot figure out how to make it reference that through the drop down box. Consignors name may not be in the same spot each day and might not have sales for that day.

At first I was thinking an IF formula, but how to do an IF for a range of cells from one sheet and have it pull the sales from that same sheet.

View 6 Replies View Related

Print To PDF Selected Sheets From An UserForm Listbox

Jun 13, 2006

I have created a custom Menu (excel add-in) to make my work easy in excel. My problem is to print only selected sheets from Workbook in one PDF file, for that I've created a Userform with 2 listboxes, add sheet and print buttons. In the first listbox are listed all the sheets and in the second listbox are the sellected sheets to be printed. What I've succeded so far is to print selected sheets, but it creates one PDF file for each sheet, only if I put my code in workbook and not in Menu add-in (.xla file). As PDF Printer I use PDF reDirect Pro v2.

View 8 Replies View Related

Compare And Update Selected Data On Two Sheets

Feb 7, 2007

Each monday i get a new price list from our supplier. My job is to compare this list with ours and update if neccesary. The reason, why I'm writing here is simple -I need to automate the process. Since their and our pricelist is somewhat different, it's only possible to use selection comparison. So, I need something that can do this:

1. First I open those two files and make a selection on both of them (like all the apples on the supplier list and all the apples on our list)
2. Push a button that executes a code
3. The code compares a value in the first column
4. If it finds a match, compares the data in second column
5. If data is same, color the cell (or the text) lets say yellow
6. If data is different, update field in our pricelist and color the cell (lets say red), so I can find and recheck it later
7. If the supplier has a _new_ product, the code will insert it somewhere in our list. Doesn't matter where, it may as well be a new sheet
8. Compare the next cells in selected area

View 2 Replies View Related

Hide/unhide Selected Range In All Sheets

Aug 10, 2007

I have several workbooks, and the workbooks can include several sheets. Is there's an easy way to create a macro so the user selection to be hidden or unhidden is hidden or unhidden in all sheets in the workbook. E.g. if the user selection is to hide rows 54-189, the macro hides rows 54-189 in all sheets in the workbook.

View 2 Replies View Related

Hide Selected Sheets And Not Unhide Without Entering Password

Mar 18, 2014

I want to hide selected sheets and when i want to unhide them, ask for password to unhide the sheets.

View 10 Replies View Related

Copy Selected Cell To Another Worksheet Instead Of Sheets (Interior)?

Dec 24, 2011

How do I copy the selected cell to another worksheet instead of sheets("Interior") ?

Code:
Private Sub OptionButton1_Click()
With Range("B19")
.Font.ColorIndex = 3
.Copy Destination:=Range("P19")
Application.Goto Sheets("Interior").Range("C20")
End With
End Sub

View 3 Replies View Related

Export Selected Sheets To New And Save But Ignore Formulas

Mar 28, 2007

I borrowed the macro below from this forum. My formulas in the sheets I'm copying refer to other sheets that I'm not saving. can someone tell me how to change it so that it copies values only to the new workbook as I'm only saving it for records purposes and some cells are saved with #REF errors.

I'm guessing there's a spot where I should type .Value ? Copy.Value doesn't work.

View 9 Replies View Related

Merge Selected Rows From Multiple Sheets Into One Sheet

Jul 14, 2009

I have around 150 excel files with sample data as follows in "sheet 1" of each workbook,

Excel doc 1:

ABC1
Column 1Column 221Data 1Data 132Data 2

Excel doc 2:

ABC1

Column 1Column 223Data 3Data 334Data 4

I want the rows with data in column 'B' and empty column 'C' from every sheet to be copied into 1 sheet.

Output to be as,

Final Excel doc:

ABC1

Column 1Column 222Data 2
34Data 4

I have a VBA code sample to select the required files in a folder and run the macro over it. The VBA is as follows,

Sub Importxlsrows()
'Import all selected rows to one sheet
Dim xlsDoc As Object
Dim xlsFileName As Variant
Dim RowNo As Integer 'row number in excel
Dim iRow As Long 'row index in Excel


'probably here we need to insert the required logic

End With
Set xlsDoc = Nothing
End If
Next i
ShowStatusFree
MsgBox "Required rows of selected files are imported into the sheet", vbInformation, "Done!"

End Sub

View 9 Replies View Related

Outputting Selected Rows To New Sheets On Basis Of Values In One Column?

Oct 16, 2013

I would like to be able to output the rows in the attached spreadsheet to separate sheets on the basis of whether they have a Y or an N in the four rightmost columns - i.e. I want to make SOLO, DUO, TRIO and FULL BAND sheets.

I would ideally like these sheets to update automatically when I change the data in the main spreadsheet.

View 3 Replies View Related

Copy All Selected Sheets (data / Formats / NOT Formulas) To New Workbook

Oct 2, 2013

I had a macro on my previous laptop that worked, but didn't have the macro backed up so it was lost.

I need a vba macro that will copy all active/selected worksheets into a new workbook. Included requirements:

* Maintain tab names

* Only bring in contents/data with all formats (including logo image, but NOT formulas or hidden rows/columns)

* Keep the file name the same except adding "- FINAL" to the end

View 3 Replies View Related

VBA Create New Folder In Current Location And Save Selected Sheets As PDF

Feb 2, 2014

I have the below code that saves selected sheets of my workbook as pdfs in the current file location. What I would like this code to be able to do is to create a new folder (named with todays date), and then save each of the pdfs into this folder.

Code:
Sub SaveWorksheetsAsPDFs()
Dim sFile As String
Dim sPath As String
Dim fPath As String
Dim wks As Worksheet

[Code] ........

View 3 Replies View Related

Excel 2010 :: VBA - Convert Selected Ranges In Multiple Sheets Into One PDF

Feb 10, 2014

I'm using Excel 2010 and would like to know if it's possible to convert selected ranges in multiple sheets into one PDF file? For example, I want to select range("A1:O10) in Sheet1 and range("A1:N25") in Sheet2, then convert both Excel sheets into PDF file with two pages.

View 2 Replies View Related

Print Selected Sheets 1004 Application Defined Or Object

Oct 7, 2007

i have found this code on the net but can only get the control sheet to work when i run macro "PrintSelectedSheets" i get a run time error 1004 application defined or object error i have put the code in the workbook object but having problems, it seems to be because the sheets that i am trying to print are hidden can this code be edited

Sub PrintSelectedSheets()
Dim i As Integer
i = 2

Do Until Sheets("Control Sheet"). Cells(i, 1).Value = ""
If Trim(Sheets("Control Sheet").Cells(i, 2).Value <> "") Then
Sheets(Sheets("Control Sheet").Cells(i, 1).Value).Select
'ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
i = i + 1
Loop
End Sub

This code seems to work ok

Sub CreateControlSheet()
Dim i As Integer

On Error Resume Next 'Delete this sheet if it already exists
Sheets("Control Sheet").Delete
On Error Goto 0...........................................................

View 2 Replies View Related

Track Changes On Multiple Selected Ranges On Multiple Sheets

Jul 6, 2007

I need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.

is there a way to track changes on multiple selected ranges on multiple sheets

View 9 Replies View Related

Fill Range Of Cells With Text When Listbox Option Selected - Clear When Not Selected

Jul 25, 2014

I am using this code to hide or unhide rows of text on another sheet:

VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)

Dim sAddress As String
Dim sValue As String

'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)

[Code]....

When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.

The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..

Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)

Code solution can be entered directly beneath:

VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True

View 1 Replies View Related

Elseif Statement In Vba: If The Selected Cell Falls Between 1/01/06 And 31/01/06 Then Jan Would Be Selected

Oct 10, 2006

Basically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months.

Sub test()
Dim SelectDate As Range
Set SelectDate = Range("SelectedDate")
If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then
ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Jan"
ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then

ActiveSheet.PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Feb"........................

View 4 Replies View Related

Copy Selected Rows & Insert As Many Times As Rows Selected

Feb 6, 2008

The following code inserts a row below the selected row, and copies the formula of the row above into it.

Dim Rw As Integer
Rw = ActiveCell.Row
Selection.Insert Shift:=xlDown
Rows("" & Rw - 1 & ":" & Rw - 1 & "").Copy
Rows("" & Rw & ":" & Rw & "").Paste

However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved