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?
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.
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
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.
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.
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.
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
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.
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.
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.
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.
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
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.
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.
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.
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
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.
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...........................................................
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)
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
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
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.