Posts by Zeanah

Part of the code from the entire codes that copy the value of the cell in column B that in my case is B1 into word document is limited for what I need.

What I need from the code below is to how to change B1 to the entire column of B to based in the bookmark (Revenue1) in word document instead of B1 only. Any code that can be added to B1 to make the code works? I tried to change that range from ("B1:B") but not work as well.

Any Help please?

    With objWord.ActiveDocument
        .Bookmarks("Revenue1").Range.Text = ws.Range("B1").Value

    End With

Hi Helpers,

My issue is that Cells in Column D have a match in Colomn A . What I need is to find the code that can arrange Col.D according to Col.A but also the rest of the used entire row inline with arranged cell in column D without changing column B and C... Refer to the attachment for the original sheet and the result.

Thanks in advance

gijsmo, I changed the code with your code above but the problem is the same.... Attached is the example when you run "Overpaid L_Grant Exists" vba for your view

Appreciates your reply

The "Yes" and "No" Button MsgBox are both calling my given Sub "Overpaid_L_Grant" which means the Exit Sub is not working for "No". How can I click on the "No" button to exit Sub and "Yes" to just Call the Sub if the Sheets already exists?

Thanks

Sub Overpaid_LGrant_Exist()



Dim ws As Worksheet

Dim answer As Integer

On Error Resume Next

Set ws = Worksheets("Overpaid_LGrant")

On Error GoTo 0

If Not ws Is Nothing Then

MsgBox "Overpaid_LGrant Already Exists, Do you want to process it again?", vbYesNo + vbInformation

If answer = vbYes Then Call Overpaid_L_grant

If answer = vbNo Then Exit Sub

End If



End Sub

Thanks Roy,

attached is the example of what happened in my case:

The code above or below was in Thisworkbook of the VBA. when you run the macro under the command button in the "Main" sheet, There will be an error that I thought caused by the code below.

Thank you so much and waiting for your solution

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not Sh Is Worksheets("Main") Then
Application.EnableEvents = False
Worksheets("Main").Move before:=Sh
Sh.Activate
Application.EnableEvents = True
End If
End Sub

The code below is making the sheet (in my case is "Main") to be visible and placed near the sheet you currently select. The Problem is that the code can interfere in the running of other macro. I noticed that when the macro run and comes to select particular sheet and then copy cells from that sheet to another, the debug window will pop up as the below code was interfere by cancelling the copy as it always activate or select "Main" sheet or tab before you select the sheet and paste cells to the sheet. Can you make the code below possible not to interrupt in the running of other macro like in my case above?

The code is:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not Sh Is Worksheets("Main") Then
Application.EnableEvents = False
Worksheets("Main").Move before:=Sh
Sh.Activate
Application.EnableEvents = True
End If
End Sub

Thanks in advance

Hi helpers,

In my attached excel Book1.xlsx , I need to find vba that can make my result in "result" sheet. The question is that how can I select only the first empty cells in column A which in my case is row 1 and 2 and delete the entire rows as given in the result sheet?? Can anyone help in giving the code please?

Thank you so much

Hi helpers

I had found the way to find the last row and autofill But the problem I don't have the vba code to find the last columns and autofill.
In my attach xls I had a template and vba that start by entering the countifs formular in B2 (sheet1) and autofill down to the last row B15. What I need is how then to add the code in this macro to auto select B2:B15 and then autofill to the last columnL of date?? your reply is very much appreciated.

Sub test()


    Sheets("Sheet1").Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIFS(Sheet2!C1,Sheet1!R1C,Sheet2!C2,Sheet1!RC1)"
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("B2").AutoFill Destination:=Range("B2:B" & lastrow)
End Sub

Hi helpers

I run the macro below in the attached xls for sheet1 but always fail. I know that the code line below [bottomB = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Row] that has a problem when debug but I dont know how to fix it to give the same results of the same entire macro I wan. If I run the same macro with over 3000 row data (in sheet2) it works so I assume that this fail in many data row.

Can anyone help in this???

Thanks in advance

Sub test()


       Columns("A:A").Select
   Selection.SpecialCells(xlCellTypeBlanks).Select
   Selection.EntireRow.Delete
   
        Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Dim bottomB As Integer
    Dim x As Integer
    bottomB = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Row
     
    Dim c As Range
    For Each c In Sheets("sheet1").Range("B1:B" & bottomB)
        If c.Value Like "*January*" Or c.Value Like "*February*" Or c.Value Like "*March*" Or c.Value Like "*April*" Or c.Value Like "*May*" Or c.Value Like "*June*" Or c.Value Like "*July*" Or c.Value Like "*August*" Or c.Value Like "*September*" Or c.Value Like "*October*" Or c.Value Like "*November*" Or c.Value Like "*December*" Then
            c.Copy Worksheets("sheet1").Range("A" & c.Row)
        End If
    Next c
End Sub

Re: copy cell if containing the value to the previous column but the same row

Thanks Karim... it works perfectly

Hi Helpers

I am new to vba but I know that it is very helpful to do heavy data in excel. My question is that I want to find value contains in column B and if found I would copy them to column A in the same row. For example if the value in B4 contains my search value i.e "*CASH*" then copy that to A4 and the same goes to the rest. I have a macro given in the attached .xls but if I run this in sheet1 the result will be like as given in "macro test result" sheet without giving the one required like as shown in "result needed" sheet. can anyone help on this code?? your answer is very much appreciated.

Thanks in advance

I want to copy the cell from Column D that contains "F/nightly" and "Dirt and Paste to column B on the same sheets. can anyone help to give code or macro??

your help is very appreciated

Thanks in advance

Hi helpers?

In the macro below I insert new column A and then copy C1 which is the date and then paste in A1:A2 and then autofill to the last column of A. The question is how to change the range or to autofill with macro if my data is extended?

Thanks in advance

Sub Macro1()
'
' Macro1 Macro
'

'
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C1").Select
Selection.Copy
Range("A1:A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A1:A19")
Range("A1:A19").Select
End Sub

I want to run an autofill numbering with macro that no blank rows in excel 2016 but if I run the autofill numbering it always stick to range (A1:A102) of my earlier data. How can the auto numbering range change to accomodate different data??

Thanks

Sub Macro1()
'
' Macro1 Macro
'

'
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
ActiveCell.FormulaR1C1 = "2"
Range("A1:A2").Select
Selection.AutoFill Destination:=Range("A1:A102")
Range("A1:A102").Select
End Sub

Hi, in the attachment I try to record the macro given in Macro 1 on the original sheet to become the second sheet (marcro result needed) but when I copy the original to the last sheet and run and the different result was given.

please can anyone help in this??

thanks