UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Copy Worksheet Tab Names To Column A In Newly Created Worksheet, Office 2013    
 
   
bakersburg9
post Sep 10 2019, 12:48 PM
Post#1



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


I have a workbook with about 200 tabs, all with Manager's name for the name of each worksheet - In a new Summary tab I created, with "MANAGER_NAME" being in A1 (header), I need to copy the name of each tab - the next (first) tab to A2 on the summary tab, the next worksheet to the right's name to cell A3 on the summary tab, and so on - I also need to copy the value only from Cell D14, but I can use the macro recorder to do that, since lucky for me, all the values I need to copy are in the same spot on each worksheet

I googled how to get a LIST of each tab, but that's not what I need

CODE
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")


Any help would be greatly appreciated !!!!

Steve
Go to the top of the page
 
ADezii
post Sep 10 2019, 01:11 PM
Post#2



Posts: 2,678
Joined: 4-February 07
From: USA, Florida, Delray Beach


This very simple Macro will Copy every Worksheet Name in the Active Workbook to Cells A2, A3, A4, A5, etc. in a Worksheet named Summary. It will also Copy the Value in Cell D14 of each corresponding Worksheet to B2, B3, B4, B5, etc. of the Summary Worksheet. The Summary Worksheet will be excluded from this process.
CODE
Public Sub CopyWSName()
Dim ws As Excel.Worksheet
Dim ws2 As Excel.Worksheet
Dim intStartRow As Integer

intStartRow = 2     'Start Row on the Summary Sheet
Set ws2 = ActiveWorkbook.Worksheets("Summary")

For Each ws In ActiveWorkbook.Worksheets
  If ws.Name <> "Summary" Then
    ws2.Cells(intStartRow, "A") = ws.Name
    ws2.Cells(intStartRow, "B") = ws.Range("D14").Value
      intStartRow = intStartRow + 1
  End If
Next

Set ws = Nothing
Set ws2 = Nothing
End Sub

Go to the top of the page
 
bakersburg9
post Sep 10 2019, 01:56 PM
Post#3



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


This is awesome, but I forgot - I have to actually copy 6 values - and I wrote separate macros to go to each one - what is the code to go back to the worksheet you were just on ?

I have to copy the following values from the individual worksheets to the summary

B8 to col B on summary tab
E10 to col C on summary tab
E11 to col D on summary tab
E14 to col E on summary tab
E15 to col F on summary tab
E16 to col G on summary tab
E35 to col H on summary tab

or could your macro be rewritten to copy each one ? That would be awesome ! - like go to the first worksheet, and copy the name of that worksheet to cell A2 - then copy all 7 values to columns B, C, D, E, F, G and H, then go back to the 2nd tab, then go to the next worksheet and do the same thing ? that would be amazing !!!! at the 3rd tab, copy the name of the tab to A3, the individual values to columns B thru H, and repeat ?
Go to the top of the page
 
ADezii
post Sep 10 2019, 02:15 PM
Post#4



Posts: 2,678
Joined: 4-February 07
From: USA, Florida, Delray Beach


If I understand you correctly, try:
CODE
Public Sub CopyWSName()
Dim ws As Excel.Worksheet
Dim ws2 As Excel.Worksheet
Dim intStartRow As Integer

intStartRow = 2     'Start Row on the Summary Sheet
Set ws2 = ActiveWorkbook.Worksheets("Summary")

For Each ws In ActiveWorkbook.Worksheets
  If ws.Name <> "Summary" Then
    ws2.Cells(intStartRow, "A") = ws.Name
    ws2.Cells(intStartRow, "B") = ws.Range("B8").Value
    ws2.Cells(intStartRow, "C") = ws.Range("E10").Value
    ws2.Cells(intStartRow, "D") = ws.Range("E11").Value
    ws2.Cells(intStartRow, "E") = ws.Range("E14").Value
    ws2.Cells(intStartRow, "F") = ws.Range("E15").Value
    ws2.Cells(intStartRow, "G") = ws.Range("E16").Value
    ws2.Cells(intStartRow, "H") = ws.Range("E35").Value
      intStartRow = intStartRow + 1
  End If
Next

Set ws = Nothing
Set ws2 = Nothing
End Sub
Go to the top of the page
 
bakersburg9
post Sep 10 2019, 02:18 PM
Post#5



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


There's actually about 7 values from the individual worksheets I need to copy to the SUMMARY tab -

So just change
CODE
ws2.Cells(intStartRow, "B") = ws.Range("D14").Value


to
CODE
ws2.Cells(intStartRow, "B") = ws.Range("B8").Value
???
Then insert:

CODE
ws2.Cells(intStartRow, "C") = ws.Range("E10").Value

ws2.Cells(intStartRow, "D") = ws.Range("E11").Value
ws2.Cells(intStartRow, "E") = ws.Range("E14").Value

ws2.Cells(intStartRow, "F") = ws.Range("e15").Value

ws2.Cells(intStartRow, "G") = ws.Range("e16").Value

ws2.Cells(intStartRow, "H") = ws.Range(“35").Value
???

if so, can I just go to the last worksheet, then go to the right ? I've done these things separately, but never together - I've used code to go to the last worksheet I was on, and to go to the right, but never used them together

Thanks for all your help !!!

Steve

Go to the top of the page
 
bakersburg9
post Sep 10 2019, 02:20 PM
Post#6



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Oops - sorry you had to do all that - I did pretty much the same thing - but can it be looped, like I said in my other post, to go back to the previous (active) workbook, then go to the right and go through the process again ?
Go to the top of the page
 
ADezii
post Sep 10 2019, 02:30 PM
Post#7



Posts: 2,678
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif , Good Luck with your Project.
Go to the top of the page
 
bakersburg9
post Sep 11 2019, 09:39 AM
Post#8



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


ADezii,

That was the most amazing piece of code EVER !!!!

Thanks !!!! cool.gif

Steve
Go to the top of the page
 
ADezii
post Sep 11 2019, 09:49 AM
Post#9



Posts: 2,678
Joined: 4-February 07
From: USA, Florida, Delray Beach


I think I can assume that it worked out well for you, great! thumbup.gif
Go to the top of the page
 
bakersburg9
post Sep 25 2019, 11:46 AM
Post#10



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Adez,
This is the most amazing macro I've ever come up with - and you deserve pretty much all the credit, and I will notate that whereever possible - I wanted to share the final version, but it includes names - do you have any clever ideas on changing the names - do you think just taking out the first name from the project manager's individual worksheets would suffice ?

Thanks again !
Attached File(s)
Attached File  removeFirstName.png ( 16.37K )Number of downloads: 1
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th October 2019 - 12:23 PM