I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode() ' 'Sheets("Summary"). Select If Range("a7") = "complete" Then ' Sheets(Array("1", "0")).Select Sheets(ArrSh(1)).Activate
I'm trying to select multiple sheets and print them out. At this time the code is only printing out the "Work Order" sheet. I'm guessing it's something to do with the PrintOut command trying to print the active sheet and not the array?
All sheets are identical in that they have the same headings within the same range.
I want to apply formatting (bold some text, apply borders, center, etc.), but to all the sheets at once. I know I can do this with a simple loop, but I was wondering if there is a more efficient way of doing it.
I've been playing with sheet( array(sheet1.name, sheet2.name))
I don't think I have correct syntax or use using the the above.
I tried coding what I would do if I was performing this manually, which is to select all the sheets and apply the formatting to sheet1. Then all the sheets would have the formatting.
I am having trouble getting the selection of sheets to work. I have a workbook that has multiple sheets and one constant sheet (Summary). There is code to create new forms in this workbook and insert them after the Summary sheet. These forms all have a date input that is formatted as a date (mm/dd/yyyy), these dates get modified on the day the form is created, there may be any number of sheets created during this process. I have to print the summary sheet and only the newest forms created. I need a code to select sheets to print based on the date input of a user for each form. This is what I have so far:
VB: Dim i As Variant i = Range("B5").Value >= InputBox("What date to start PDF from? Format = mm/dd/yyyy") Sheets(Array("i")).Select [code]....
If the dim can be taken out and just included in the line for the array that would be fine with me. The cell "B5" is where the date is located in each form. I want to input a date and the macro will select the sheets where the date is equal to and greater than the date entered. The Summary sheet will always be included in the print set. I have a dialog box for setting which printer to use - this file will be used at different offices and therefor the printers will be different and it will also allow to create a PDF if desired.
How can I select sheets in a workbook based on the premise that the sheetname does not have the letter "Q" in it? New to forum, so apologies if format is not kosher. I have a group of 50+ workbooks which are all set up in the following format:
-Contains 30+ sheets. -Sheet names are varied, but follow a pattern. I try to keep the sheet names consistant on all workbooks so that it is easier to reference, but other users make this impossible. -However, I managed to keep two things consistant: There are two categories of sheets. In one group all sheetnames have a "Q" in them and in the other group all sheetnames DON'T have a "Q" in them.
Here is my problem: I need to run a macro to perform retative tasks on the GROUP OF SHEETS WITHOUT A "Q". To begin the macro I need to select these sheets and copy them to a new workbook, but since the actual sheetnames are varied, I am having trouble coming up with a flexible way of selecting sheets. The closest I have come is using a IF ... LIKE ... THEN statement, but I can only get it to work to select the sheets with a "Q" in the name and not the opposite.
I'm trying to write a macro to select all the Sheets in a Workbook, and set some properties [Auto ColumnWidths, Landscape, and Fit to 1 page wide] for all of them. I don't know the names of the sheets, nor how many there will be - this part is tagged on the end of a long macro that creates new files and pastes various data into them. The code below only seems to work on the Active sheet - not any of the others selected. Curiously, I can set a specific column width for all sheets, but not Auto Widths.
With ActiveWorkbook Sheets.Select With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .Orientation = xlLandscape .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False End With End With
Sheets.Select ActiveWindow.Zoom = 80 ' This line works! Cells.Select Selection.RowHeight = 13.5 Selection.EntireColumn.AutoFit Columns("C:C").Select Selection.ColumnWidth = 34 ' This line works Range("A1").Select
Currently, I have 65536 rows of data per sheet in an excel file. I have a total of 8 sheets. I need to combine all these sheets into one csv file. As my company uses excel 2003, I can't consolidate all sheets into 1 sheet before saving as CSV format as it will exceed the number of rows available in excel 2003.
However, the number of rows and number of sheets in the excel file will change monthly, and I need to consolidate the excel file accordingly.
I am wondering if I could save a new workbook as csv format first before copy and paste all data into that csv file.
Also, all the 8 tabs have the same header. I only need to copy the header on the first tab, and for the sequence tab, I will only need to copy row 2 onwards.
I have recorded a macro based on a sample data.
Sub Macro1() ' ' Macro1 Macro ' ' ActiveWorkbook.SaveAs Filename:= _ "C:Documents and Settingse31425My DocumentsBook1.csv", _ FileFormat:=xlCSV, CreateBackup:=False
I have a workbook that contains something like 50 worksheets and they all contain data with the same columns, for example column A is Project, column B is Project Name, etc. I need to convert data in each worksheet into an Excel Table. There has to be an easier way than manually converting each worksheet into a Table. However, when I group all of the sheets, the option to Insert a Table is not available even though the data starts in the same row and contains the same number of active columns in each worksheet. Is there an easier waty to insert a table in all of the worksheet simultaneously?
I've seen a few threads on here about this issue but none of them do quite what I am looking for. I'd like for a single page "report" to be created when a user presses a button (which runs a macro, of course) The macro should be able to run through certain named sheets (even if hidden) and if a cell in any row is red within a sheet then the entire row or rows that meet the criteria should be copied and pasted into the Report sheet.
On the report sheet, for each sheet that has had rows that were copied, I'd like to have the name of the sheet as the header above the pasted rows so that the user knows which sheet the data came from. Any sheet that doesn't have red cells would be excluded from the report. I've attached a sample file but had to limit the number of sheets because of Orgrid's file size limit. Hopefully, you'll see what I am getting at here.
Is there any way to select the last Worksheet, other than clicking on the Last Tab?
Because i created a macro which inserts more sheets after the Last Sheet, but that Last Sheet is named (lets call it LS), so everytime the macro runs it inserts new sheets after the LS sheet, rather than the 'real' last sheets.
In a worksheet, I have a listing of people. In a different sheet, I have a list of trips. I want to be able to select a person (a right-click, function), then move to the other sheet, find the desired trip and, double-clicking, select that trip to be associated with the selected person. Those two data elements would be written to another sheet. I can select the person and switch to my listing of trips. I can double-click on the desired trip. But I can't figure out how to join the two and write the data.
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
I hava a macro-routine in my workbook: "SwitchBoard.xlsm"
The macro opens a workbook: "OriginalFile.xlsx"
When OriginalFile.xlsx is opened I will select some of the sheets in this opened book, let the macro copy them into "SwitchBoard.xlsm" as new sheets before the macro proceeds and "do stuff" after they are renamed (I have a routine for this renaming.)
How do I programme this temporary macro-pause/stop-proceed when I manually select the sheets I will copy for further use in SwitchBoard.xlsm?
Can someone please help? I am very new to vba but I have managed to write code ( multiple subs strung together with Call statements) for about 25 sheets in this one workbook. Each sheet has a command button that initiates the data maipulation that I want to accomplish on that particular sheet. It all works very well when I go to each sheet and click on the command button.
Now I want to make all of these processes run from one command button on the first sheet. All of the subs on every sheet now have unique names because I anticipated wanting to string them all together and run them from one command button. I guess you call this a module for the entire workbook, but I am still struggling with the terminalogy of all this programming, so don't know for sure.
I added the code from the second sheet/command button to the end of the code from the first sheet/command button and joined them with a Call statement. Figured I would go about this sistematically, sheet by sheet. Instantly, I got an error message " Activate method of range class failed" when I ran it. At the start of the added code, I had added something like Worksheets("Sheet2").Activate figuring that I needed to activate that worksheet in order to make the rest of the code work. That is where I got the error.
Sorry for the long winded explanation. Can someone tell me what I need to add/do in order for the code to run without error as I string together all the pieces from each sheet?
i want to type 1-1 in my excel sheet and it is automatically coverting to 1-Jan and then i checked for formatting to the way i want then i founf excel automatically selecting custom option in format then if i select general my 1-jan is converting to 41640 number. Now i want excel sheet should show what typed in it i .e. 1-1 only.
I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.
I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.