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...
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...
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...
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.
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...
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...