Search results

Works good for me for the sample you have provided. See enclosed file for the result I get. Do you have more than one line breaks between text? IF so, pls can you upload a few more examples of cell values? Provide both sample files excel and word. i.e. how is it in Excel and what do you...

Does this help: s = Replace(Trim(Sheet.Sheets("Weekly Update").Range(Cell_name).Value), Chr(10) & Chr(10), Chr(10))

This should do the work: Sub CopySheets() Dim sWB As Workbook, dWB As Workbook Dim wSht As Worksheet Dim shtCount As Long Dim fName As String With Application .ScreenUpdating = False .DisplayAlerts = False End With Set sWB = ThisWorkbook For Each wSht In sWB.Worksheets If...

Try this: Sub CopySheets() Dim sWB As Workbook, dWB As Workbook Dim FldrPicker As FileDialog Dim wSht As Worksheet Dim shtCount As Long Dim fName As String With Application .ScreenUpdating = False .DisplayAlerts = False End With Set sWB = ThisWorkbook fName =...

Code is working fine for me on the sample files you have provided. Questions:- 1. Where have you placed the code in? Module or any specific sheet? 2. You have provided sample files in .xlsx format and in one of your comments you say they are .xlsb format "It means chandoo1.xlsb!test It means...

I guess, you need to change the sheet names as per your file.

Try this: Sub AppendData() Dim sPath As String, fName As String Dim dWBook As Workbook, sWBook As Workbook Dim lRow As Long With Application .ScreenUpdating = False .DisplayAlerts = False End With Set dWBook = ThisWorkbook sPath = dWBook.Path &...

Do you have a code that you have written or tried? This is something very basic and available all over the internet. A Google search would not do any harm instead of waiting for others to do your work.. :) Ron de Bruin have some excellent variants that you can start with and customize to your...

Change this line FileName = .Sheets("Short").Range("O6").Value to FileName = .Sheets("Summary").Range("O6").Value and it should work. Working for me.

I can't test your full code without a sample file but it should be something like this: Sub filename_cellvalue() Dim SourceWB As Workbook Dim NewWB As Workbook Dim strPath As String Dim FileName As String With Application .DisplayAlerts = False .ScreenUpdating = False End With Set...

I have already provided you that in code above. I was referring to this part: With Application .DisplayAlerts = False .ScreenUpdating = False EndWith which need to be set to true at the end. With Application .DisplayAlerts = True .ScreenUpdating = True EndWith

One more thing, you should be very careful while using Application level properties as they apply to Excel as a whole and not to specific workbook(s). For e.g. when you set Application.DisplayAlert = False at starting of your procedure, it should be reset to True at the end else you will not get...

This should help you: Sub filename_cellvalue() Dim SourceWB As Workbook Dim NewWB As Workbook With Application .DisplayAlerts = False .ScreenUpdating = False End With Set SourceWB = ActiveWorkbook With SourceWB .Save .SaveCopyAs (.Path + "\" & "Test.xlsx") End With...

Ideally in that case, you should first save a copy and then make changes to the new workbook. What you are doing is making change to old workbook and then saving a copy.

SaveCopyAs method does not take file format parameter. Try without it. ActiveWorkbook.SaveCopyAs fileName:=Path & fileName & ".xlsb"

Why not use ActiveWorkbook.SaveCopyAs in place of ActiveWorkbook.SaveAs. This way you will save a copy of file but will remain on active workbook and carry on with whatever you want to do with it. Thanks/Ajesh

It depends on what you are trying to achieve and how your loops are constructed. There's no "one" best method to do a task in excel. You can do same thing in multiple ways and all/most of them may be considered as one of the best. :) So it all depends on "what" and "how". Thanks/Ajesh

For every i loop we initialize Found as False and then change it to True when we get a match. Found variable will be changed to True only if we have found a match for below line: If .Cells(i, 3).Value = sWsht.Cells(j, 1).Value And .Cells(i, 5).Value = sWsht.Cells(j, 2).Value Then Once we have...

Small correction to the count formula above (Considering week start from Monday): =SUMPRODUCT((WEEKDAY(B$1:AF$1,2)<6)*($B3:$AF3=25)) Now to sum values other than weekends: =SUMPRODUCT((WEEKDAY(B$1:AF$1,2)<6)*($B3:$AF3=25)*($B3:$AF3)) BR/Ajesh

Not sure if the weekday names in row 2 are text or dates formatted as day names but this should help. Fill formula down as required In case weekday are text: =SUMPRODUCT((B$2:AF$2<>"Sa")*(B$2:AF$2<>"Su")*($B3:$AF3=25)) In case weekday are underlying dates...

Simple correction in your formula: =IF(SUM($G$8:G8)>20000,0,SUM($G$8:G8)) further, you have Accounting Format applied to your cells due to which you will see $ - for zero values, so if you want to see $0.00 in cells, apply custom format as $#,##0.00;-$#,##0.00. BR/Ajesh

This should give you a start: Sub test() Dim wSht As Worksheet Dim LastRow As Long Dim i As Long Dim MyArray() Set wSht = ThisWorkbook.Sheets("Sheet1") LastRow = wSht.Cells(wSht.Rows.Count, 2).End(xlUp).Row With wSht ctr = 0 For i = 8 To LastRow If .Cells(i, 8) = "OK" Then...

Try this: With ws.Range("D3:D250") .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _ , Formula1:="=6" .FormatConditions(.FormatConditions.Count).SetFirstPriority With .FormatConditions(1).Interior .Pattern = xlPatternLinearGradient...

See if this is what you want: Private Sub CommandButton1_Click() Dim sWB As Workbook Dim sWsht As Worksheet, dWsht As Worksheet Dim SLrow As Long, DLrow As Long Dim i, j As Long Dim Found As Boolean With Application .ScreenUpdating = False .DisplayAlerts = False End With On Error...

Replied to your original post. Please check. Thanks/Ajesh