Multiple worksheet and certain cells

[MacroTC]

I have about 90 worksheets in a workbook that I want to copy 2 certain cells. The cell is different in each workbook depending on what the data is on each workbook. I know which row the cell I want to copy but not the column. Bascially I want to take the last cell in row 23 that has content in it to determine the cell I need to copy on row 17 and 19 for each one of the worksheet.

I'm tyring to create a macro that I can use so I don't need go to each worksheet manually.

 

[rallcorn]

OK - no one is biting, so I'll jump in. I'm pretty much a VBA novice, so I try solving things to learn (but am usually too embarrassed to post my code because someone will rip-it apart!)

Assumptions for my code:
1) 1st worksheet in your file is the summary sheet onto which the copied data will reside.
2) All remaining sheets will be included in loop
3) Summary Sheet has heading in Row 1 columns A, B, C
Each worksheet's name will be returned to Summary Sheet ColA beginning row 2
Each worksheet's row 17 data will be returned to Summary Sheet ColB beginning row 2
Each worksheet's row 19data will be returned to Summary Sheet ColB beginning row 2

Sub FindCopy()
Dim Col As Long, s As Long, i As Long, LR As Long
 
Application.ScreenUpdating = False
 
'Loop throught sheets beginning 2nd sheet seen in Excel Window
For s = 2 To Worksheets.Count
    'Find last column in which there is a value in Row 23
    Col = Sheets(s).Cells(23, Columns.Count).End(xlToLeft).Column
    
    'Copy the Row 17 value for the last column found
    Sheets(s).Cells(17, Col).Copy
    Sheets(1).Select
    
    'Define last row on Summary Sheet
    LR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    Range("B" & LR + 1).Select
    ActiveSheet.Paste
    
    'Copy the Row 19 value for the last column found
    Sheets(s).Cells(19, Col).Copy
    Sheets(1).Select
    Range("C" & LR + 1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    'Populate the Summary Sheet with the name of the worksheet
    'corresponding to the values shown
    Sheets(1).Range("A" & LR + 1).Value = Sheets(s).Name
 
Next s
Application.ScreenUpdating = True
End Sub

 

[rallcorn]

Should be: Each worksheet's row 19 data will be returned to Summary Sheet ColC beginning row 2

 

[MacroTC]

Thanks Rallcorn, it was really helpful

 

[MacroTC]

The worksheet I was working on wouln't let me save the maco. How can I save the macro on a seperate workbook but direct me back to the file to grab the data?

 

[rallcorn]

sorry - but I'm not following your question. You don't want the extracted data to be in the same workbook as the original worksheets you're looping thru?

What version of Excel are you on? If Excel 2007 or higher, you'll have to save the file as .xlsm (Excel Macro-Enabled Workbook) instead of .xlsx

 

[MacroTC]

I'm on excel 2010, thanks it worked.

 

[MacroTC]

If all of the different worksheet tabs are now in a different file, how can I change the macro so it loops through that file instead of the current excel file?