ive tried creating a macro/code that when you open a certain workbook it automatically deletes the contents. you see i have a workbook containing 100 sheets that i need to update each week but i have to delete all the contents first, is there a quick way to do this when i open the workbook up?
I have a work book with many sheets and staff continue to mess this up so I would like to know how I can have a specific sheet called Instructions open when this workbook is opened preferably no macro's is this possible
I am trying to write code in the VB behind Excel that will allow me to navigate to an excel file (can be open or closed) and delete it. ie navigates to file xxx.xls at N:/xxx/xxx/ selects the file and then deletes it.
I have a workbook that has worksheets that serve as data input pages for the user. I have control boxes created by the forms toolbar.
I created a splash screen that is to be launched when the file is opened. Instead, often the last sheet that the user was on when they last saved the file (usually but not always the worksheet called "Main Menu") is seen first.
I understand that not all the sheets can be hidden - that one must be unhidden.
So I created a sheet called "Background" that is a solid color with no gridelines.
When the file is opened, I want the "Background" sheet to be seen first and then the splash screen to be initiated.
Now - when the user opens the file, the Main Menu worksheet is seen for a few seconds (or whatever the last sheet was on before it was last saved), then it disappears and the solid background screen is shown with the splash screen. When the splash screen ends, the "Background" sheet disappears.
Here is the code that I have in the workbook
Private Sub Workbook_Open() Dim wks As Worksheet On Error Resume Next For Each wks In ThisWorkbook If wks.Name "Background" Then wks.Visible = False End If Next wks End Sub
What has to be changed so that the first sheet that is seen upon opening the file is the "Background"; then the splash screen happens; then it returns to the Main Menu sheet?
my code to loop through sheets in another workbook does not want to work. in This Workbook (SourceWb), I copy the current workbook using SaveCopyAs and then reopen the new workbook. from ThisWorkbook (SourceWb), I am trying to delete any sheet between "First" and "Last" where a specific criteria is not met. so starting from the For Each to get my loop values, where PMs is a list of people's initials:
That particular question has been solved, but now i need it to work with multiple values from combobox.
So for example, if "Master" is selected in combobox3, it will delete sheets 7, 8 if "CSR" is selected in combobox3, it will delete sheets 1, 8 if "Original IND" is slected in combobox, it will delete sheets 1,7
The current code will work when "master" is selected, however i am getting compile errors when i select "CSR" or "Original IND"
WhenI select CSR, it does create a new wb for me (without sheets 1,8) correctly but i get a Run time error.
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook Copy range (c8,d69) Close work sheet Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
I am looking for a code that would copy the data from each worksheet in a given workbook and then paste to just one worksheet within a different workbook. The Sheet names are auto generated when I run this canned report but the naming structure is always the same...the first worksheet is named Repair Details and then the next sheet is named Repair Details_1, the next sheet is named Repair Details_2 and so on for every sheet in workbook. So I would like to copy all of the data(Headers to last cell) and then paste in a worksheet(ex: Master Repair Report.xlsx and the worksheet could be titled Master Repair Details) on a different workbook, then the next sheet would copy from the one under the header to the last record and paste to the same workbook. This process would repeat for every worksheet in the Repair Details Workbook and paste to Master Repair Details worksheet in the Master Repair Report workbook.
I have been tasked with creating a macro which creates a new workbook wherein each sheet contains the information for one site from the active sheet. The active sheet already has the values sorted by the site such that all information needing to be copied from the active sheet into the new workbook is together.
ATL ATL ATL ATL CEN CEN JCK JCK etc.
There are 8 different sites on the active sheet: ATL, CEN, DAL, HAR, JAS, JCK, VIS, NOV
The macro needs to find the range for all of the data of each site and copy/paste that data into a new workbook such that ATL would have its own sheet, CEN would have its own sheet, and so on. The data ranges from A:R.
So, for example, the macro would find that the last row with ATL in the "B" column is 6095 and would then copy A2:R6095 and insert that data into the new workbook under Sheet 1.
I had some code that I had adapted to select the range for each of them, but the code loops through the entire sheet (which is 44,307 rows long) for each site making it a quite clunky and very slow step in an even longer macro. Since the data is already sorted, I know there must be a way to have the macro stop searching when it reaches data not equal to the data the row before, however, my experience with VBA is limited, and I have been unable to find a solution. Also, the data does not have to be conserved after being sent to the new workbook, if that would speed up the macro.
I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook
Private Sub Workbook_Open() Dim dTime As Date dTime = Time If dTime >= TimeValue("9:30 PM") And _ dTime < TimeValue("9:40 PM") Then ImportData End If End Sub
This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?
I am copying the times from a bi-weekly timesheet to a monthly timesheet. What I am doing is opening the bi-weekly timesheet, executing VBA code that opens the monthly timesheet and inserts the appropriate data. What is happening is if I execute the code via the VBA editor the program works flawlessly and copies the data. When I try to bind the code to a macro and execute it, it opens the monthly timesheet but just flat out stops where it should begin inserting the data. No errors or anything.
Here is the Sub tstoitsrev3()
' Define Variables Dim wsheet As String, wsheet2 As String Dim wb As Workbook Dim hourarr(9), dayarr(9), mchkarr(9) Dim mcheck, mdiff, edate, sdate, wsmnth, yr
If there is anything I can do to perhaps help streamline the code or a smarter way of accomplishing my task please let me know. I had to upload the workbook screenshots to a seperate source due to their size. Thank you in advance for all your help and I hope to become a valuable part of the community.
On a quick side note I cannot modify the timesheets in any way other than through the code. For example, I cannot re-locate where data is placed on the time sheets.
I have a cell that is pulling data from a separate file, no problem there. However, I want the name of one of the folders within that path to change monthly (eg. by monthly name). How can I make a file path incorporate a cell link within a filepath (eg. instead of it saying 'Aug 06' I want it to refer to cell A1 which reads 'Aug 06').
I have a VBA-coded macro in an Excel workbook that is to open another existing workbook -- a .xls file, not .csv -- copy data from it, paste that into the original workbook where the macro resides, then close the source workbook.
If I use the debugger to step through the macro -- putting a breakpoint at its first executable line, run the macro, then F8 step through it, it works fine. (With delays at the open, close statements.) Free running, it doesn't. I suspect it's a timing issue, that the macro runs full tilt even though the new workbook isn't yet open -- but I don't know how to remedy that.
Here is the situation and the simplified code (the actual macro does a lot more, of course):
Main workbook: Two sheets, "Parameters" that has the fully-qualified filename for the desired source .xls in A1; and "Data", into which I want to paste data from that source.
Source workbook: Single sheet, "Sheet1"
Macro code. For simplicity, it is trying to copy/paste all of the source worksheet's content:
Sub Go() ' ' Go Macro ' Test of getting data from another spreadsheet ' ' Keyboard Shortcut: Ctrl+Shift+G ' Dim SourceFile As String Dim HomeBook As String Dim OtherBook As String Sheets("Parameters").Select SourceFile = Range("A1").Value HomeBook = ActiveWorkbook.Name
right after the open, hoping that might cause a dwell for the open to complete.
When I step through it, the Locals view has all the parameters, getting set properly. If I try to simply run it, it stalls at the opened source workbook, the Locals view is empty, no data has been transferred into the initiating workbook.
If I place the breakpoint after the open, it is not reached. (I have the VBA open.) Hitting F8 then yields a VB error window, "Compile Error" "Expected: To"
What im trying to do is have a macro that will open a file called Blue 1.xls in location "I:SchedulesBlue" and then copy data from cells N13:034. Then paste it into another excel file (Press.xls)on worksheet "Press Break" cell G14.
The data that is copied not always fills up the cells N13:O34 so I need it to go to the next blank cell in column N and paste the information from file Blue 2, and so on for Yellow 1, Yellow 2, YellNR, and Green.
So to summarise I need a macro to open 6 files copy data from the same location on each of the files(N13:O34) then close and paste it into a master document(Press.xls) worksheet "Press Break".
I have 3 workbooks open. 1 is my working file and I name it as "Final[date].xlsm". The other 2 files are my source files which are also open are named as source1.xlsx and source2.xlsx. Both the source files has only one tab/sheet as "Sheet1". My objective is to bring the contents of the source file to my working file in 2 different sheets. all the contents in Sheet1 of "Source1.xlsx" should be paste.values only to the "Final[date].xlsm" with a sheet name "BankDetails". This will enable the user also to replace the old data in "BankDetails" tab. The "source2.xlsx" sheet1 has a different situation. I need to copy only the cells with values, not the entire cells, because it has to be pasted (values only) to range C2:L. I have formulas in the other columns before C and after L.
Take note that my working file name is changing every based on the date while my source file has always same file name.
I created a new workbook that contains a list of additional design requirements than our old checklist. These requirements are answered by placing a 1, 0 or .5 in the cell next to the requirement (1 = yes, 0 = no and .5 for half credit). I can't simply go to the old workbook/worksheet and copy the results into the new workbook/worksheet as the rows of requirements don't match up since some requirmements have been deleted and new ones added between the old and new checklists. I created a command button in the new checklist and what I want it to do is: - open a dialog box that lets me select the old checklist file (these are all excel files, but with different names). - go the the proper worksheet in the old file (DFT Checklist) and copy a column of results from the old checklist ("DFT Checklist" worksheet) and paste them into the proper cells of the new checklist ("DFT Checklist" worksheet). - The macro would know (i'll have to tell it) which requirements and cells are still valid answers between the old checklist and the new one. It would disregard requirements that are no longer in the new checklist. - Once complete, it would close the old checklist without saving.
I want to create a standard macro that will copy values from a Master workbook to the current, active workbook I have open. The problem I have run into is when I record a macro it also records the name of the active workbook I'm copying data into and I would like it so when I bring up a workbook I just run the macro and it copies the values in. I'll be doing 8 different macros that each copy different values from the Master spreadsheet
I need to import the data from a specific sheet (same named sheet on all closed workbooks) to a sheet in an open workbook. All the columns are identical in every workbook but the number of rows is variable, so the data from each subsequent workbook must be appended to the end of the current data.
Whenever a button is pressed, this macro will clear the sheet, then import the data starting in A3. The workbooks are in different folders but they all have the same name, so some sort of explorer window will probably be needed to actually select each file.
My Splash screen opens a few seconds after the workbook has loaded. Is there a way to make the workbook open minimzed until the the splash screen closes then open properly? What I trying to say is that only the splash screen is visible until it closes.