Macro with multiple tabs / copy and paste

[IrvDizzle]

Good Day all,

long time / first time

I've been on Google the previous day looking at compiling a series of macros to do the following (I will break up in to unique tasks):
#1 : scroll through a workbook with 70+ tabs and find a uniquely named tab (that will change spreadsheet to spreadsheet)
#2 : scroll to a uniquely named cell (that will not always be the same cell location) in each uniquely named tab to insert 4 columns (it will always be 4 columns), cut and paste 3 uniquely sized ranges in to inserted columns, and delete the original data in the named ranges
#3 : scroll through a series of uniquely named tabs and repeat the above task until the end of the required tabs
#4 : navigate to the "Check Tab" to ensue that the check = 0

I found the macro for #2 and #4 , I'm just struggling w/ finding #1 and #3 , and then how to make it all work together?

any help would be awesome. I'll keep searching in the meantime.

thank you.

 

[DanteAmor]

1) ITD is the text in the cell. I find it by "CTRL+F" and then typing "ITD"

2) After I find this cell, I insert 4 columns 12 columns to the right. then I cut / paste-as-values information from (origin) DEALN-CA16:CD34; DEALN-CA40:CD55; DEALN-CA63:CA78 to (destination) DEALN-CE16:CH34; DEALN-CE40:CH55; DEALN-CE63:CH78

after that, I cut/paste-as-values information from (origin) DEALN-BO16:BR34; DEALN-BO4:BR55; DEALN-BO63:BR78 to (destination) DEALN-CA16:CD34; DEALN-CA40:CD55; DEALN-CA63:CA78

a few points:

I never know if that first cell is BO or BP. If ITD starts at BP13, then the above information moves over one column.

pls let me know if this is clear.

Following your example, assuming that the "ITD" data is in cell "BP13", what do I do?
I advance 12 columns and insert 4 columns or I insert 4 columns and then advance 12 columns?
Sorry but it's not clear to me.



Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
-----------------------


Returning to your example.
First, If the cell is in BP13 and I insert 4 columns to its right, does that mean that the content of BQ will now be in BU?
Second, you want to copy from BO to BR, but the BQ and BR cells are empty because I just inserted columns.

 

[DanteAmor]

I share my file, in the sheet "Del1" you explain step by step what you need.

On the Deal1-Final sheet, put the resulting data, explaining how you arrived at that data. That way I can see how the origin is and how you want the result.

https://www.dropbox.com/s/w1cc4i34x7niubg/IRV 1.xlsx?dl=0

 

[IrvDizzle]

Following your example, assuming that the "ITD" data is in cell "BP13", what do I do?
I advance 12 columns and insert 4 columns or I insert 4 columns and then advance 12 columns?
Sorry but it's not clear to me.



Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
-----------------------


Returning to your example.
First, If the cell is in BP13 and I insert 4 columns to its right, does that mean that the content of BQ will now be in BU?
Second, you want to copy from BO to BR, but the BQ and BR cells are empty because I just inserted columns.

I do appreciate you constantly coming back to help and I will endeavour to explain as best as I can.

I wonder if the example is too complex to explain via messaging and I should just get on YouTube and find stuff. The problem that I'm having is that I'm never sure where the starting cell is to copy or what the range is. Maybe you can help me with that? Instead of explaining the process maybe you can point me in the direction of where to find macros that dont use set cells for their basis.
Are there macros to:
1. find a uniquely named tab, loop through tabs until I get to another uniquely named tab and then stop (in this case, find tab "DEALN", run cut / paste process on loop for all tabs that = "DEALN")
2. find a cell by using CTRL + F, then manually moving down 2 rows, designating that cell as a start point to create three named ranges to copy and paste
3. Ive already found a macro to insert rows, but how to I tell that macro to insert rows when I wont know where the rows need to be inserted?

let me leave it there and see what you say.

thank you again, Dante for putting up with me and my request.

 

[DanteAmor]

With points 1 and 2 I have no problems. That is very simple.
I attached a macro sample for you to run on your file and see how it works.

Sub test1()
  Dim i As Long, f As Range
  i = 1 '
  If Evaluate("ISREF('" & "Deal" & i & "'!A1)") Then
    Set f = Sheets("Deal" & i).Cells.Find("ITD", , xlValues, xlWhole)
    If Not f Is Nothing Then
      Sheets("Deal" & i).Select
      f.Select
      MsgBox "Hey, I found it", vbExclamation
    Else
      MsgBox "Does not exists 'ITD' word", vbCritical
    End If
  End If
End Sub

Then I put the above in a loop to work with all sheets.


But point 3 and so on I do not understand anything, that's why I sent you a file so that there you can put the steps of copying, inserting, deleting, etc., etc., etc.

 

[IrvDizzle]

I share my file, in the sheet "Del1" you explain step by step what you need.

On the Deal1-Final sheet, put the resulting data, explaining how you arrived at that data. That way I can see how the origin is and how you want the result.

https://www.dropbox.com/s/w1cc4i34x7niubg/IRV 1.xlsx?dl=0

Hi Dante,

sorry for taking so long. I had to make some changes so I uploaded a new file.

https://www.dropbox.com/s/226j1kkx8imvwi8/IRV1_v2.xlsx?dl=0

let me know if you can access

Irvin

 

[DanteAmor]

Don't worry, I'm still here with you until we solve it.


You tried the macro of post #14 .

I check your file and let you know any questions.

 

[DanteAmor]

Is the cell with "ITD" always combined in 4 cells?

 

[DanteAmor]

Ok, try the following

To test with 3 sheets

Sub test1()
  Dim i As Long, f As Range, sh As Worksheet, cStart As Range
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False


  For i = 1 To 3
    If Evaluate("ISREF('" & "Deal" & i & "'!A1)") Then
      Set sh = Sheets("Deal" & i)
      Set f = sh.Cells.Find("ITD", , xlValues, xlWhole)
      If Not f Is Nothing Then
        f.Offset(, 13).Resize(1, 4).EntireColumn.Insert Shift:=xlToRight
        f.Offset(2, 13).Resize(20, 4).Value = f.Offset(2, 9).Resize(20, 4).Value
        f.Offset(2, 9).Resize(20, 4).Value = f.Offset(2, 0).Resize(20, 4).Value
        f.Offset(2, 0).Resize(20, 4).Value = ""
        
        f.Offset(27, 13).Resize(18, 4).Value = f.Offset(27, 9).Resize(18, 4).Value
        f.Offset(27, 9).Resize(18, 4).Value = f.Offset(27, 0).Resize(18, 4).Value
        f.Offset(27, 0).Resize(18, 4).Value = ""
        
        f.Offset(50, 13).Resize(16, 4).Value = f.Offset(50, 9).Resize(16, 4).Value
        f.Offset(50, 9).Resize(16, 4).Value = f.Offset(50, 0).Resize(16, 4).Value
        f.Offset(50, 0).Resize(16, 4).Value = ""
      Else
        MsgBox "Does not exists 'ITD' word", vbCritical
      End If
    End If
  Next
End Sub

If you want to do more tests, first run this macro:

Sub recreate()
  Dim i As Long, sh As Worksheet
  Set sh = Sheets("temp")
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  On Error Resume Next
  For i = 1 To 3
    Sheets("Deal" & i).Delete
    sh.Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Deal" & i
  Next
End Sub

Test with the v3 file:
https://www.dropbox.com/s/68k9byekmt4xbfz/IRV1_v3.xlsm?dl=0