Have you paid the 10% to Ozgrid?
Transaction ID: 6CK992398M960273E
Have you paid the 10% to Ozgrid?
Transaction ID: 6CK992398M960273E
I have attached an example spreadsheet of what I am trying to accomplish here. Basically all of the rows with a date prior to today will be actual values pulled in from an external database.
In cells E2:M3 I have the different stages of our supply chain and how many days each stage takes. As units move through the supply chain they should stay at each stage for the number of days indicated in the table in E2:M3, with two exceptions:
Stage 1 is the ordering stage. You can see my formula starting at cell F29. This formula checks if the day is divisible by the reorder frequency, and if so, checks to see if we go below the safety stock number and need to order more inventory. If the total stock falls below the safety stock number in the period being checked, and order will be placed for the reorder amount.
Stage 8 is the other exception. This is our total stock available for sale. This number should check if any stock is available from completing stage 7, as well as subtract the unit sales from the previous day (Column O).
The issue I am having is writing a formula that will repeat the unit number each day until the number of days in the supply chain length table has been reached, and then moving those units to the next stage. Any formula I write results in a circular reference in the formula for stage 1.
Happy to provide any other details as needed.
Re: Copy sheet values to another workbook
FYI this is now cross posted here as its been almost a week with no replies. I'm not sure if I'm being very unclear or if this just can't be done but I thought I'd throw another line in the water just to be safe.
Re: Copy sheet values to another workbook
Bump?
I have a workbook with around 100 sheets and would like to create a macro to copy certain sheets to a new workbook (all the sheets in the same workbook) and keep the sheet names and formatting but only leave the values in the sheets, not the formulas. I've searched a lot and found some helpful answers but have been unable to put it all together and have it work. Something along these lines:
Sub createNewFile()Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Name, 1) = 0 Or Left(ws.Name, 1) = 1 Then
ws.Select
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Test File\" & ws.Range("B5") & ".xlsx"
ActiveWorkbook.Close (False)
End If
Next ws
Application.ScreenUpdating = True
End Sub
Display More
This code seems like it should work to copy all sheets to their own file (it actually errors out on the "ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value" line) but I can't figure out how to copy them all to one workbook. I'm sure its a simple fix that you experts will know in no time.
Thanks,
Ryan
Re: Save Excel Template within VBA Project
Thanks Roy, I'm new to creating add-ins and didn't realized I could create a sheet within the add-in.
I've developed an Excel Add-In for my company that runs certain reports based on data in the spreadsheet. I'd like to have the reports created from a template rather than using VBA to format the sheets every time it is run, i.e. rather than adding headers, formulas etc, create new sheet from a custom template and import the data.
As the template will just be part of the project, is it possible to store it within the add-in, or will I need to distribute the template to all users in order for the add-in to work properly?
Re: $15 comparing two spreadsheets, 10% paid to Ozgrid
Code works great, and very quickly. As you mentioned, the files are very large. I'm fairly new to programming and haven't dealt much with memory usage, but I think this may be cause by creating and deleting hundreds of sheets so often. Do these remain in the memory? What other measures can I take to reduce the size of the file?
Re: $15 comparing two spreadsheets, 10% paid to Ozgrid
Sent, thanks for your help!
Re: $15 comparing two spreadsheets, 10% paid to Ozgrid
Looking at the files it will take to long to annonymize all of my data. Rather, can I email you the two files with the agreement they will not be posted or shared due to confidential information?
I need an answer pronto to this problem. I have two spreadsheets, last month's customer statements and this months customer statements. The current month doesn't have invoice descriptions, just dates and amounts. Each sheet houses a single customer statement and has the same name in both workbooks. I need to compare each sheet with the same name and if there is a line that has a matching date and amount, copy the invoice description to the current months line. I've written the following code, but with over 100 statements it takes hours to run on my machine, and doesn't work to boot!
Sub updateInvoiceDescriptions()Dim curDate As Date
Dim curBalance, nextRow, oldNextRow As Long
Dim curDesc, custNum, oldWbPath As String
Dim ws As Worksheet
Dim oldWb As Workbook
oldWbPath = Application.GetOpenFilename(FileFilter:= _
"Microsoft Excel Workbooks, *.xls; *.xlsx; *.xlsm", Title:="Select Last Months Statement File")
Application.ScreenUpdating = False
Set oldWb = Workbooks.Open(oldWbPath)
For Each ws In ActiveWorkbook.Worksheets
For nextRow = 17 To 64
custNum = ws.Name
curDate = ws.Cells(nextRow, 1).Value
curBalance = ws.Cells(nextRow, 4).Value
For oldNextRow = 17 To 64
If oldWb.Sheets(custNum).Cells(oldNextRow, 1).Value = curDate And oldWb.Sheets(custNum).Cells(oldNextRow, 4).Value = curBalance Then
ws.Cells(nextRow, 2).Value = oldWb.Sheets(custNum).Cells(oldNextRow).Value
End If
Next oldNextRow
Next nextRow
Next ws
oldWb.Close
Application.ScreenUpdating = True
End Sub
Display More
I have made a custom ribbon tab for an application I am developing. On the tab a have a toggle button that hides and unhides certain sheets in the workbook. It does this by checking if "pressed" is true or false and then calling either hideSheets or unhideSheets. I've run into a problem because there are other macros that call the unhideSheets procedure, but the toggle button remains pressed. How do I tell the toggle button to "unpress" whenever the unhideSheets procedure is called?
Re: Rename copied sheet with ScreenUpdating turned off
I figured it out. You were right to warn about only turning off screen updating once. I didn't realize it but when i called "hideRows" screen updating was being turned on again with each sheet. On another note, my hideRows sub causes the total time it takes the macro to run to go from a minute 30 seconds to 6 minutes... Is there a more efficient way to write this code?
Re: Rename copied sheet with ScreenUpdating turned off
Here is the full code:
Sub createStatements()Dim customerList, custNum As Range
Dim lngRow, nextStmtLine, nextCell, nextTOC As Long
Dim formattedCustNum, prevCust As String
nextTOC = 7
prevCust = "Table of Contents"
lngRow = Sheets("CustList").Range("A" & Rows.Count).End(xlUp).Row
Set customerList = Sheets("CustList").Range("A2:A" & lngRow)
Sheets("Table of Contents").Range("A7:A500").Clear
Sheets("Table of Contents").Range("D7:D500").Clear
For Each custNum In customerList
nextStmtLine = 17
formattedCustNum = Mid(custNum.Value, 8, 3) & "." & Right(custNum.Value, 2)
Sheets("Blank Customer Statement").Copy after:=Sheets(prevCust)
Sheets(Sheets(prevCust).Index + 1).Name = formattedCustNum
Sheets(formattedCustNum).Range("A76").Value = "'" & custNum.Value
Sheets("Table of Contents").Cells(nextTOC, 1).Value = "'" & custNum.Value
Range("CuRe_RefCustSet").Value = "'" & custNum.Value
For nextCell = 9 To 90
If Sheets("Cust Report").Cells(nextCell, 28).Value > 0 Then
Sheets("Cust Report").Range("Y" & nextCell & ":AB" & nextCell).Copy
Sheets(formattedCustNum).Range("A" & nextStmtLine).PasteSpecial xlPasteValues
nextStmtLine = nextStmtLine + 1
End If
Next nextCell
prevCust = formattedCustNum
nextTOC = nextTOC + 1
Call hideRows
Range("A1").Select
Next custNum
Application.CutCopyMode = False
End Sub
Display More
If I turn off screen updating in the beginning it doesn't work, I still watch as every sheet is added and then populated. Also, RoyUK, the error I got before changing the code to the above was an Excel error, not a VBA error. It would tell me that it could not create a sheet with the same name.
Re: Rename copied sheet with ScreenUpdating turned off
That worked perfectly, as far as naming the sheets correctly, now I'm having an issue with the screen updating. Even though it is set to false, I am running the code right now and watching each sheet be copied and populated. Any idea why that would happen?
I have a large macro with the following code as a key component:
Sheets("Blank Customer Statement").Copy after:=Sheets(prevCust)
ActiveSheet.Name = formattedCustNum
Sheets(formattedCustNum).Range("A76").Value = "'" & custNum.Value
As it stands, the code works fine, however, if I set application.screenupdating = false then the code errors out, as the copied sheet doesn't become the active sheet. Is there a better way to handle this situation? The macro takes about 15 minutes to run, and I'd like to turn off screen updating to help speed things up a bit.
I have an edit box on a custom ribbon tab where a user can input a sheet number and the following macro navigates to that sheet and hides all others
Sub edbxSheetNum_Change(control As IRibbonControl, text As String)
Dim sheetNav As String
Dim ws As Worksheet
sheetNav = text
Application.ScreenUpdating = False
On Error Resume Next
Sheets(sheetNav).Visible = xlSheetVisible
Sheets(sheetNav).Activate
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> Sheets(sheetNav).Name And ws.Name <> Sheets("Table of Contents").Name Then
ws.Visible = xlSheetHidden
End If
Next ws
Application.ScreenUpdating = True
If Err.Number = 9 Then
MsgBox "Sheet Does Not Exist", vbCritical, "Error"
End If
End Sub
Display More
The problem is if the user inputs a sheet name that doesn't exist, the error message appears just fine but the "Table of Contents" sheet gets hidden for some reason. Can anyone see why this happens?
Re: Loop Until User Selects vbNo
Works like a charm! Another conundrum on the same code however...
While MsgBox("Any Volksbank receipts?", vbYesNo) = vbYes
vksReceipts = vksReceipts & InputBox("Country?") & ", " & InputBox("Customer name?") _
& " - " & FormatCurrency(InputBox("Amount? (Enter number only i.e. $100,000.00 as 100000"), 2) & vbCrLf
Wend
is the actual code I use. When I select yes it asks first for the amount, then for the country and customer name. It concatenates everything in the correct order, just asks the user in a weird way. Can anyone tell me why?
I've written the following code to ask a user if there are any cash receipts to input:
Sub addCashReceipts()
cshRec As String
If MsgBox("Any cash receipts?", vbYesNo) = yes Then
cshRec = InputBox("Input cash receipt:")
End If
End Sub
Display More
How do i cause this to keep repeating until the user selects no?
Re: Macro to Format Raw Data from Accounting System
Thanks for the reply and getting me started cytop. I've rewritten the code using your suggestions and have the following:
Sub formatData()
Dim lngRow As Long
'1. Clear first 3 rows of data
Rows("1:3").ClearContents
'2. Find bottom row of data, delete bottom 5 rows
lngRow = Range("A" & Rows.Count).End(xlUp).Row
Rows(CStr(lngRow - 4) & ":" & CStr(lngRow)).ClearContents
lngRow = lngRow - 5
'3. Cut totals from bottom of report and paste at top
Range("C" & CStr(lngRow) & ":F" & CStr(lngRow)).Cut Destination:=Range("H1")
Range("B" & CStr(lngRow)).Cut Destination:=Range("L1")
Range("A" & CStr(lngRow)).Clear
'4. Add subtotals to validate formatted data
lngRow = Range("A" & Rows.Count).End(xlUp).Row
Range("I2").Formula = "=subtotal(9,I5:I" & lngRow & ")"
Range("I2").AutoFill Destination:=Range("I2:M2"), Type:=xlFillDefault
Range("I3").Formula = "=I1-I2"
Range("I3").AutoFill Destination:=Range("I3:M3"), Type:=xlFillDefault
Range("M4").Value = "Total"
Range("M5").Formula = "=SUM(I5:L5)"
Range("M5").AutoFill Destination:=Range("M5:M" & lngRow), Type:=xlFillDefault
'5. Insert new column to compute customer number for each line of data
Columns("A:A").Insert shift:=xlToRight
Range("A5").Formula = "=if(iserror(find(""00000"",b5)),a4,b5)"
Range("A5").AutoFill Destination:=Range("A5:A" & lngRow), Type:=xlFillDefault
Range("A5:A" & lngRow).Copy
Range("A5:A" & lngRow).PasteSpecial xlPasteValues
'6. Filter data and delete unneeded rows
Range("A4:M" & lngRow).AutoFilter field:=6, Criteria1:="="
'Need code to select visible rows and delete
Range("A4:M" & lngRow).AutoFilter field:=6
lngRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A4:M" & lngRow).AutoFilter field:=8, Criteria1:="="
'Need code to select visible rows and delete
Range("A4:M" & lngRow).AutoFilter field:=8
lngRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A4:M" & lngRow).AutoFilter field:=3, Criteria1:="Total:"
'Need code to select visible rows and delete
Range("A4:M" & lngRow).AutoFilter field:=3
lngRow = Range("A" & Rows.Count).End(xlUp).Row
'7. Format data
Range("I1:M" & lngRow).Style = "Comma"
Cells.EntireColumn.AutoFilter
Cells.EntireColumn.AutoFit
End Sub
Display More
The only part I still can't figure out is how to delete the filtered data. Also, if there are any improvements I am open to all suggestions. My VBA skills are improving, but still rudimentary at best.