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.

 

[offthelip]

You haven't specified what or where the name is you are looking for so I assumed it was in cell A1 of the active sheet.

Sub test()
Name2Find = Cells(1, 1)
For i = 1 To ActiveWorkbook.Worksheets.Count
 If Worksheets(i).Name = Name2Find Then
  Worksheets(i).Select
  MsgBox (" we have found " & Name2Find)
  Exit For
 End If
Next i




End Sub

 

[IrvDizzle]

You haven't specified what or where the name is you are looking for so I assumed it was in cell A1 of the active sheet.

Sub test()
Name2Find = Cells(1, 1)
For i = 1 To ActiveWorkbook.Worksheets.Count
 If Worksheets(i).Name = Name2Find Then
  Worksheets(i).Select
  MsgBox (" we have found " & Name2Find)
  Exit For
 End If
Next i




End Sub

thanks for the code. It is not A1 where I am copying the data from. The cell that I start to copy from is below a uniquely named cell (ITD). So once I identify the correct tab, I need to find the cell below "ITD". then copy a range from that cell.

 

[DanteAmor]

Hi @IrvDizzle, welcome to the forum!

I imagine that in the "Check Tab" sheet you have something like this.

Check Tab
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:89.35px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Sheets</td><td >named Cell</td><td >named ranges</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >example1</td><td >hello</td><td >cells1</td><td >cells2</td><td >cells3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >example2</td><td >day</td><td >cells21</td><td >cells22</td><td >cells23</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >example3</td><td >world</td><td >cells31</td><td >cells32</td><td >cells33</td></tr></table>

Using the first record:
1. Find the "Example1" sheet
2. Search for the "Hello" cell and insert 4 columns to the right of the cell.
3. Copy the ranges "Cells1", "Cells2" and "Cells3" to the right of the "Hello" cell.
4. Then the macro runs from cell A2 to cell A4.

Sub copypaste()
  Dim sh As Worksheet, c As Range
  Set sh = Sheets("Check Tab")
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If Evaluate("ISREF('" & c & "'!A1)") Then
      Sheets(c.Value).Range(c.Offset(, 1).Value).Offset(, 1).Resize(1, 4).EntireColumn.Insert Shift:=xlToRight
      Range(c.Offset(, 2).Value).Copy Sheets(c.Value).Range(c.Offset(, 1).Value).Offset(, 1)
      Range(c.Offset(, 3).Value).Copy Sheets(c.Value).Range(c.Offset(, 1).Value).Offset(, 2)
      Range(c.Offset(, 4).Value).Copy Sheets(c.Value).Range(c.Offset(, 1).Value).Offset(, 3)
      Range(c.Offset(, 2).Value).ClearContents
      Range(c.Offset(, 3).Value).ClearContents
      Range(c.Offset(, 4).Value).ClearContents
    End If
  Next
End Sub

If it is not something similar to the above, then you would have to explain in more detail what you have and what you want as your objective.

 

[IrvDizzle]

Hi @IrvDizzle, welcome to the forum!

I imagine that in the "Check Tab" sheet you have something like this.

Check Tab

	A	B	C	D	E
1	Sheets	named Cell	named ranges
2	example1	hello	cells1	cells2	cells3
3	example2	day	cells21	cells22	cells23
4	example3	world	cells31	cells32	cells33

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 76.04px;"><col style="width: 76.04px;"><col style="width: 89.35px;"><col style="width: 76.04px;"><col style="width: 76.04px;"></colgroup><tbody>
</tbody>

Using the first record:
1. Find the "Example1" sheet
2. Search for the "Hello" cell and insert 4 columns to the right of the cell.
3. Copy the ranges "Cells1", "Cells2" and "Cells3" to the right of the "Hello" cell.
4. Then the macro runs from cell A2 to cell A4.

Sub copypaste()
  Dim sh As Worksheet, c As Range
  Set sh = Sheets("Check Tab")
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
    If Evaluate("ISREF('" & c & "'!A1)") Then
      Sheets(c.Value).Range(c.Offset(, 1).Value).Offset(, 1).Resize(1, 4).EntireColumn.Insert Shift:=xlToRight
      Range(c.Offset(, 2).Value).Copy Sheets(c.Value).Range(c.Offset(, 1).Value).Offset(, 1)
      Range(c.Offset(, 3).Value).Copy Sheets(c.Value).Range(c.Offset(, 1).Value).Offset(, 2)
      Range(c.Offset(, 4).Value).Copy Sheets(c.Value).Range(c.Offset(, 1).Value).Offset(, 3)
      Range(c.Offset(, 2).Value).ClearContents
      Range(c.Offset(, 3).Value).ClearContents
      Range(c.Offset(, 4).Value).ClearContents
    End If
  Next
End Sub

If it is not something similar to the above, then you would have to explain in more detail what you have and what you want as your objective.

thanks for the response, Dante. That is pretty close to what I need.

If I can type it out logically:

Find tab that = Deal1
Find the first cell that = ITD in Deal1 and move down one row (this cell should = cash, I designate as startcell1)
Begin copy from startcell1 down 21 rows and then to the right 4 columns (copyrange1)
Move 9 columns to the right from (startcell1)
Insert 4 columns to the right and paste as values
Move back to startcell1
Move down 25 rows (cell should = cash, I designate as startcell2)
Copy from startcell2 down 19 rows and across 4 columns (copyrange2)
Move 9 columns to right from (startcell2)
Paste as values
Move back to startcell2
Move down 23 rows (cell should = cash, I designate as startcell3)
Copy from startcell3 down 17 rows and across 4 columns (copyrange3)
Move 9 columns to the right from (startcell3)
Paste as values
Delete copyrange1, copyrange2, and copyrange3
Move to next tab and loop the above steps until tab = "TEMPLATE", when next tab = "TEMPLATE", move to tab that = "RFW" … then end

I hope this makes sense.

 

[IrvDizzle]

Actually scratch the above … I just realised that I may delete the headings … let me try again

Find tab that = Deal1
Find the first cell that = ITD in Deal1 and move down two rows (designate as startcell1)
Begin copy from (startcell1) down 20 rows and then to the right 4 columns (copyrange1)
Move 9 columns to the right from (startcell1)
Insert 4 columns to the right and paste as values
Move back to startcell1
Move down 24 rows (I designate as startcell2)
Copy from (startcell2) down 18 rows and across 4 columns (copyrange2)
Move 9 columns to right from (startcell2)
Paste as values
Move back to (startcell2)
Move down 22 rows (I designate as startcell3)
Copy from (startcell3) down 16 rows and across 4 columns (copyrange3)
Move 9 columns to the right from (startcell3)
Paste as values
Delete copyrange1, copyrange2, and copyrange3
Move to next tab and loop the above steps until tab = "TEMPLATE", when next tab = "TEMPLATE", move to tab that = "RFW" Â… then end

 

[DanteAmor]

I think I am confused.
You could upload an example and in the sheets you explain with comments what you need.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[IrvDizzle]

I unfortunately cannot upload it because it is company property and there are some non-shareable numbers on there.

let me explain the process.

the process I am trying to automate is this: every month I get a spreadsheet … this spreadsheet as ~80 tabs. I focus on 63 of them. The first tab is named "Deal1" and it goes to "DealN" (right now N = 63). I usually have to scroll past multiple tabs before I find "Deal1". For each of those 63 tabs I have to scroll to the data from two months ago, insert 4 columns to the left of it, and cut / paste the previous months data in to the space I have created. The space that I insert 4 columns could start at CA or at CB. I can never be sure. It would be CA to CD or CB to CE. the data I am copying from is ether BO to BR or BP to BS, again I can never be sure until I go to the tab. But I always start at the cell below "CASH". Irrespective of what the column is, I am always copying 3 sets of data. So if the previous month's data I am copying starts at BO16, then my range is BO16:BR34, BO40:BR5, and BO63:78 but I never know until I get in to the tab to check. BO15, BO39, and BO62, the cells above where I start to copy are all labelled "CASH". Similarly, I do not know if I am inserting columns from CA to CD or CB to CE, but once I know where to insert my columns I insert 4 and copy the three sets of data. I do this for all 63 tabs until I see "TEMPLATE" then I go to tab "RFW" to make sure that my "CHECKSUM" cell = 0. If that equals zero then I'm good.

does this help?

 

[DanteAmor]

I did not ask for your work file, with 3 sample sheets with dummy data is enough.

But let's start with the basics and move on.

1)
You said:
"Find the first cell that = ITD in Deal1 and move down two rows (designate as startcell1)"

What is ITD?
Assuming I already found the "Deal1" sheet, how to find that first cell?

2)
Assuming I understand how to get to the cell "ITD".
Do you want to copy data, from which sheet, from which cells? and where do you want to paste, on which sheet and in which cells.


In your explanations, always write sheet-cells-origin and sheet-cells-destination.

 

[IrvDizzle]

I did not ask for your work file, with 3 sample sheets with dummy data is enough.

But let's start with the basics and move on.

1)
You said:
"Find the first cell that = ITD in Deal1 and move down two rows (designate as startcell1)"

What is ITD?
Assuming I already found the "Deal1" sheet, how to find that first cell?

2)
Assuming I understand how to get to the cell "ITD".
Do you want to copy data, from which sheet, from which cells? and where do you want to paste, on which sheet and in which cells.


In your explanations, always write sheet-cells-origin and sheet-cells-destination.

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.