Debug error points to:
BuildPlan(Worksheets(SourceWorksheet).Cells(j, "A"), Worksheets(SourceWorksheet).Cells(j, "E"),
Worksheets(SourceWorksheet).Cells(j, "F")) = Worksheets(SourceWorksheet).Cells(j, "K")
Sub is below:
Sub UpdateMonthForecast()
Dim Month As Integer
Dim Year As Integer
[Code]....
The attached workbook contains a macro ("Transfer_Data") that copies values from one cell to another when the "Update Progress" button (near cell A3) is selected. It works perfectly fine in this workbook but when I try the same code in another workbook, which looks and operates exactly like this one, nothing happens.
Is this due to a security setting or missing code line items?
copy&paste in next blank cell2.xlsm
I am an excel novice trying to create a list of local churches. There are several online lists that I am taking the data from but, being a novice, I am entering the data manually. How to set up a method to bring the data into excel and order it where I want it.
I am attaching the spreadsheet.
These are the websites I am taking my data from: [URL]
I have a bunch of spreadsheets in a folder and I need to take one line of each spreadsheet (which is in the same place on every one) into a master spreadsheet.
I am always adding to the spreadsheets in the folder.
Is there a way to have my master spreadsheet look out for changes in the folder and when there are changes add the row into it?
I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself.
The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups.
This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.
I have a simple list of data (Name, Room, etc.) in a spreadsheet. Each day I click on the spreadsheet tab and create a "copy" and (move to end). This creates a spreadsheet for the next day's data.
My problem is that the macro I use to sort this data never works in the newly created spreadsheet within the same workbook. As usual, I'm sure it is something relatively simple that I am overlooking.
I've got an Excel 2007 spreadsheet that I want to use to take the attendance in the school where I teach. I've got it more or less the way I want it, but there are 2 improvements I'd like to make:
1. I don't want to have press Enter to go down to the next cell. If a kid is present, I want to press 'p' and automatically be one cell lower.
2. When I open the spreadsheet (which contains multiple worksheets for different classes), I'd like it to open at today's date on the left regardless of which worksheet I go to (I've frozen the first four columns, so I'd like 'today's column' to appear next to them). I've put the dates in row 1. I've put the dates in the format ddd, d/m/y. (I don't know if that info makes any difference, hence I'm including it). I've already looked up some posts on this and copied and pasted a few different suggestions, but none worked.
I have a macro that is taking a long time, so long that I think something is wrong but I dont really have a great way to check. I want to put up a little window showing the progress either by saying "i'm on row XX" or having a progress bar.
Looks like msgbox requires action before the code continues so it is not a good thing to use if you want to get progress. My formula looks for bad values in a data file and fixed them. It takes forever. I would like to have a box there that simply tells me what row it is working on.
Clearly I dont want to hit the OK button for it to continue processing. I want it to update the message box on its own and then take away the message box when it is finished processing.
I have a number of reports in excel (all in the same format) which I need to take certain data out of and store it into one main spreadsheet. Searching through the forums I found some code which I think will do this:
Sub test()
Dim myDir As String, fn As String, ws As Worksheet
myDir = "C: est"
fn = Dir(myDir & "*.xls")
If fn = "" Then Exit Sub
Do While fn ""
Set ws = Workbooks.Open(myDir & fn).Sheets(1)
ws.Range("a7", ws.Range("a" & Rows.Count).End(xlUp)).EntireRow.Copy
ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1)
Workbooks(fn).Close False
fn = Dir
Loop
End Sub
I have tried using this code, altering the Dir String to the folder the documents are in.
However, when I run the code, nothing happens. I don't get any errors, and nothing appears in the spread sheet.
To be honest, I am not totally familiar with macros in Excel. I can get the gist of what the code is doing having using VB before, but am not totally familiar with some of the functions being used here.
I'm using the excel built in function to sort columns with my macro. However since my columns have 64,000 entries it takes a while for the computer to sort it.
View 14 Replies View RelatedI have a spreadsheet with members details in and I've added a few columns showing:
Col Q: Paid 2008 (blank if No, 1 if Yes)
Col R: Show Paid Yes or No (shows a Yes or a No)
Col S: Date Joined (only entered if new and joined during 2008)
Col Z: Subs owing from 2008 (if existing members and owe last yr's subs)
Col AA: Subs to Pay in 2009 (a standard £35 or pro rata if joined during 2008)
Col AB: Pro Rata (if they joined other than at the beginning of 2008)
Col AC: EA Sub? (based on a yes/no column, to add £5 if yes)
Col AD: Total to Pay for 2009
So the formulae (or data entered) for the above columns are as follows:
Col Q: Paid 2008 - blank if no, 1 if Yes
Col R: Show Paid Yes or No - =IF(Q2=1,"Yes","No")
Col S: Date Joined - a date entered in format d/m/yy
Col Z: Subs owing from 2008 - =IF(R2="no", 35,0)....I want a default 35 in here
Col AA: Subs to Pay in 2009 - =IF(S2="",35,"Pro Rata")....says that if there is no date which indicates they are an existing member, they will pay the standard 35, otherwise they'll be paying a pro rata fee...................
Problem:
It all works fine until a certain row (27 actually) - then I get a VALUE! error which points to the Date Joined field. Now I've tested that value against the vlookup table and it returns the correct data, so why doesn't it do it in my spreadsheet????? I've tried changing the formats on the cells; clearing all data; entering a different date, but from that row in the spreadsheet down to the last row, it simply won't work! I've looked at each formula in each cell on the rows where it is working and the rows where it isn't and I can't see anything different.
What else can I look for? It is SO frustrating as it does what I need it to to do but only for a third of the spreadsheet!!!!
I've been working on a spreadsheet to control the 'booking diary' at work and linking it with the movements in and out of our 'chambers'
If you look at Column L & M, they are the volumes and column N is where the 'pallet' is coming / going to .
If you look at line 149 and below I've tried to translate these 'volumes' into movements by time but as you can see it is not to totalled up correctly during certain times.
For example line 158 should total 40 and line 187 should total 80.
Can anyone work it out for me as this happens on numerous lines
Using MS Excel 2010.
As I understand it hyperlinks set in a spreadsheet should work regardless of where you save and/or copy the spreadsheet i.e. as long as the directory location of the location / file the hyperlink is referencing doesn't change (notwithstanding, the user must have appropriate access rights to the relevant directory locations)
However, the situation I have is the spreadsheet was generated and saved to directory location A (intention being this would be the 'master' yearly template) and the hyperlinks set, all of which work when you open the spreadsheet in this directory location. But when I copy the spreadsheet to directory location B, which is intended to be the yearly 'register' location for the spreadsheet, none of the hyperlinks work? When I hold my cursor over a link in the copied spreadsheet, in directory B, I do not get a 'pop-up' path like I do in directory A and when I click a link I get an error message:
Microsoft Excel The address of this site is not valid. Check the address and try again.
As stated above the documents the hyperlinks relate to have not moved, they remain in the same template directory (i.e. the link path has not altered).
I am inputting rows of data to test my spreadsheet and all of the sudden the formulas stop working??
Can someone take a peak at it and see if they can tell why its no longer working at I19?
the if stattement works perfectly and does exactly what i want except when it comes to the else part. if there is no error the statements are run perfectly but if there is an error (in this case the error is generated when a match cannot be found in the spreadsheet) the else statement doesnt kick in and post the msgbox.
the code just crashes. and returns an error 1004 on the line i have highlighted in yellow
res = WorksheetFunction.Match(invvar, Columns(1), 0)
If Not IsError(res) Then
Me.txtClientID.Value = ws13. Cells(res, 7)
Me.txtNumber.Value = ws13.Cells(res, 7)
Me.txtDate.Value = ws13.Cells(res, 8)
'save client id as a variable
'Print to invoice------------------------------------------------------------------.....................
I am working on a macro that copies tabs from the spreadsheet the macro (button) is located in, creates a new book for them, and then pastes values so that no formulas still exist. The problem I am having is with named ranges. The named ranges do not reference the new sheet, but rather the old spreadsheet. This is fine because the new file is solely for presentation, however, one macro (which selects print ranges based on user selected checkboxes) tries to reference the old spreadsheet once it runs. I would like the macro that I currently have working, to also make this macro in the NEW spreadsheet so that it can still highlight the correct print area when used. The macro does not reference the name of the workbook at any point, it is purely "Range("ai10") type references so I don't understand why it is trying to open the old sheet back up.
View 2 Replies View RelatedBelow is a code which I added to my 'Quick access toolbar' (and which is based off a hidden workbook). I use this macro to fulfill tasks to regular excel spreadsheets that are not macro enabled, however, I can't seem to get this macro to work for other workbooks. How to modify my code to work for other spreadsheets.
[Code] .....
It gives me a error and says my password is wrong. I have user names set to return the password so that it is unlocked for certain people. It works for unprotecting the sheets. I modified it and added it back in to try to do it for the workbook.
[Code] .........
I realize that they " and () around the password are differant, but that's how I last left them. I have tried several combinations and it just gives me more errors saying expected end of statement, etc.
I am using this code in a macro to find a substring from a string in cell G1.
View 11 Replies View RelatedI have this macro which sits in the workbook module:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
[Code] .......
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code:
Sub Send_Mail()
'this sends an email that sends a text message
Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String
[Code] .......
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
I've got a semi-recorded macro to format a report I run, and towards the end it deals with sorting, subtotalling, formatting text etc. The main function of the macro is to split a large report into several smaller reports defined by whichever manager name appears in the relevant column. The smaller reports need subtotalling by 2 different columns so I have the following macro code :
Cells.Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
For some reason this only subtotals by the second column and not the first. I tried swapping them over so GroupBy1 then GroupBy2 but it's still the same. Anyone got any ideas why this might be? I appreciate it's not the smoothest code I could use but I don't know enough about code to do any better.
Have recorded a Macro which is doing a great job, but when i try to run the same macro from other pc its not running,pops an search error.
View 9 Replies View RelatedI've got a column that auto-populates from anther column depending on what's input, and that's working fine. I also have a button set up to select and copy the auto-populated data. I just recorded myself select the top cell and hit shift+ctrl+down to select just the values that are populated. What it does, however, is selects the blank ones too. I know the counta() function would work, but I don't know how to input that into a macro.
The macro I have now is:
Sub Copy()
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End Sub
The column is only 25 rows tall.
I have a macro to export two worksheets and save as, but the save as part will not work, it just exports to Book1.
Ive checked the directory and everything is fine.
I want the value of a cell in one of the exported worksheet to be included in the save as name.
I'm just wondering if the code needs to somehow specify the worksheet to get the cell value from or will it default to the first worksheet. but either way my code is still not working and i can't tell why.
Should i just create a seperate macro for the save as?
Here is the ...
I got a problem with a macro i'm working on. I got column D which contains text as "Figures", "Toys", "3DS", "PS3" etc. I also got column N where i need the macro to display "Toys" if it's a toy, figure, etc; or "Games" if it's "PS3", "PS4" etc.
I'm using the following code, to also select only blank rows (so to ignore row 1 which is table header). Problem is that the result shows only "Games".
VB:
Columns("N:N").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(RC[-9]=""*toys*"",""Toys"",""Games"")"
I'm having a problem with my macro .However, if i use the following script:
[Code]......
If i am correct all the rows with a value less than 1 should be hidden right? However, some rows are hidden when they shouldn't be.
Im working on a quotation with different products in it. And the rows with a 0 in it should be hidden and that's pretty much working. But it also hides some of the subtotals(which i dont want) and some stay unhidden.
I have macro for creating months. Code worked fine, but now I use merged cells in range where days should be created, and code no longer works (Error : "Cannot change part of merged cells").
Tried with unmerging cells, but then code inserts days in shorter range that I need, so code needs to be changed.
[Code] .....
Attached File : Macro_months.xls‎
i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?
View 10 Replies View RelatedI found the following code elsewhere on the net, and have attempted to use it in my workbook to change the names of all 31 worksheets when a certain cell's (C7) value changes. The code looks good to me, so I cannot understand why it is not working. I am not getting error messages, just no changes to the sheet names.
I believe this may be due to the value of C7 on each sheet being dictated by a formula, so the value is not being changed with the sheet active, but indirectly with a formula that pulls the value from a cell on another sheet, and then adds.
(i.e. =TEXT('Populator Tools'!$C$25+1,"mm-dd-yy") )
Am I completely off base with this assumption, or on the right track; and if I am on the right track how do I fix this so it works?
Code Below.
The Workbook_SheetChange Code Stored in This Workbook Module
I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:
View 14 Replies View Related