#1
|
|||
|
|||
Need Help With a Macro
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Thank you both for your postings. Working great! Much appreciated.
|
#5
|
|||
|
|||
You are welcome.
Hello NoSparks ! How have you been ? |
#6
|
|||
|
|||
Query Ansered
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 Last edited by Pecoflyer; 05-12-2018 at 12:15 AM. Reason: Added code tags |
#7
|
||||
|
||||
@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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
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 |