Need Help With a Macro

rsrasc · #1 05-05-2018, 03:12 PM

Hi all,

I have a payroll file with an average of 45 tabs. The tabs are named as followed:

Page 1
....
Page 50

In any given tab, there may be under Column Y, the letter G and the associated dollar value in column Z.

I would like with a macro, be able to move (maybe cut and Paste) the information one row up (same position-Column Y and Column Z) and then be able to delete the entire row where the letter G and their corresponding value was before.

For example, in Page 20 in this file, in cell Y20, there is a letter G, and in cell Z20, there is a dollar value in the amount of $225.00. With a macro, I would like move the information one row up (to row 19), and after this I would like the macro to delete the entire row 20.

For example, in Page 30 in this file, in cell Y24, there is a letter G, and in cell Z24, there is a dollar value in the amount of $324.00. With a macro, I would like move the information one row up (to row 23), and after this I would like the macro to delete the entire row 24.

Hope this is not confusing.

Well, thank you in advance for your assistant and cooperation.

Regards,
rsrac

Logit · #2 05-05-2018, 06:26 PM

Code:

Option Explicit

Sub Button1_Click()
    Dim x As Long
    Sheet1.Select

    Application.ScreenUpdating = False
    For x = 2 To ThisWorkbook.Sheets.Count
        If Sheets(x).Name <> "Sheet1" Then
            Sheets(x).Select
            If Range("Y20").Value = "G" Then  'searches for term G
                Range("Y19:Z19").Value = Range("Y20:Z20").Value
                Range("Y20").EntireRow.Delete
            End If
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

NoSparks · #3 05-05-2018, 09:31 PM

My offering...

Code:

Sub testing()
Dim ws As Worksheet, fndRng As Range
For Each ws In ThisWorkbook.Sheets
    With ws.Range("Y:Y")
        Set fndRng = .Find(What:="G", LookIn:=xlValues, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
        If Not fndRng Is Nothing Then
            fndRng.Offset(-1).Resize(, 2).Value = fndRng.Resize(, 2).Value
            fndRng.EntireRow.Delete
        End If
    End With
Next ws
End Sub

rsrasc · #4 05-06-2018, 03:15 AM

Thank you both for your postings. Working great! Much appreciated.

Logit · #5 05-06-2018, 08:18 AM

You are welcome.

Hello NoSparks ! How have you been ?

Shashi Kant · #6 05-10-2018, 05:01 AM

Code:

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim i As Integer
    For Each ws In Worksheets
        ws.Select
        For i = 1 To ws.Cells(Rows.Count, "Y").End(xlUp).Row
            If ws.Cells(i, "Y").Value = "G" And ws.Cells(i, "Z") > 1 Then
                ws.Range(ws.Cells(i - 1, "Y"), ws.Cells(i - 1, "Z")).Value = _
                ws.Range(ws.Cells(i, "Y"), ws.Cells(i, "Z")).Value
                ws.Cells(i, "Y").Select
                Selection.EntireRow.Delete Shift:=xlUp
            End If
        Next i
    Next ws
End Sub

Pecoflyer · #7 05-12-2018, 12:15 AM

@Shasi
Hi and welcome
please always wrap code with code tags ( click Go advanced - select code - click #)
I did it for you on this thread. Please do the same in your other posts
Thx

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Footnote extraction macro [Why is this macro so slow? / anyway to make it faster?]	Le_Blanc	Word VBA	10	03-22-2021 11:38 AM
Spell check macro within macro button field doesn't work in one document	samuelle	Word VBA	0	07-20-2016 02:27 AM
Macro Question: Need help making a macro to highlight the first word in every sentence	LadyAna	Word	1	12-06-2014 10:39 PM
Macro Needed to bold specific lines and Macro to turn into CSV	anewteacher	Word VBA	1	05-28-2014 03:59 PM
custom icon, undo/redo for macro, permanent macro	Rapier	Excel	0	08-05-2013 06:30 AM