Extracting a row from multiple sheets in an Excel Workbook 1

[navigator2011]

Hi guys,

I've got a simple one if someone can kindly assist.

I need to extract a row out of every sheet in an Excel file. Unfortunately i've got like 30sheets.

Does anyone have some vba code to do this?

Many thanks!

 

[PHV]

What have you tried so far and where in your code are you stuck ?
Hint: have a look at the Worksheets and Rows collections.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Is the row the same for each worksheet? In other words, is it a static row, or is the data in different places on different sheets?

If it's in the same row, then it'll be much easier.

What you've got to do is:
1. Create objects for the Workbook and Worksheets
2. Loop through the worksheets
3. With each worksheet, select the desired row and export/copy the data to wherever you're needing to send it...

So that brings me to this question: What are you wanting to do with the data? There are a couple of ways you can go about it and combine it if you're needing to combine it. Are you going to Access or some other application with it, or are you going to a new workbook/worksheet?

Depending upon how you want/need to do it, you may also need to set and get data from the clipboard. There is already code out there which you can paste in a module and then call from another procedure.

You'll need to tell us a lot more than you're telling us if you want to get a real answer..

 

[navigator2011]

Hi,

Thanks for assisting.

Yes the rows are in the exact same position in everysheet. I just need to extract the rows and dump them into another worksheet or into another Excel file.

From there on i can manipulate the data. If you have a piece of code that does this or something similar i can probably modify it accordingly.

Thanks again!

 

[kjv1611]

What is it you're solving for out of curiosity? Why was the data ever put in the workbook in this way to begin with? It sounds more like the process needs to be fixed than using a patch/duct-tape fix like this one.

Tell us what you're trying to do. I have a working example, but I need to know about your situation to see if we should go about it a little differently.

 

[SkipVought]

No VBA code required, using MS Query via Data > Get External Data.

faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

Maybe something like this

origbook$ = Excel.Workbooks.Application.ActiveWorkbook.name
for i = 1 to 30
book$ = choose (i,"Bill Spreadsheet.xls", "Fred Spreadsheet.xls" ...)  ' Include full directory path for all spreadsheets if not in same directory as working spreadsheet

Excel.Workbooks.Open

s$ = choose (i, "Sheet1", "Data", ...) 
Sheets(S$).Select     ' if Spreadsheets have data in different named sheets  -OR-

Sheets("Sheet1").Select  ' if all spreadsheets have data on the same named sheet

Rows(5).Copy  ' if data is contained in ROW 5

Windows(origbook$).Activate
Range("A"&i+20).paste             ' if you want the data to start in row 20
Windows(book$).Activate
Windows(book$).Close
next i

 

[kjv1611]

Well, here's what I had, if you want to go the VBA route. I tested it, and know it works:

Sub CopyToNewSheet()
    Dim wb As Workbook
    Dim wsTo As Worksheet
    Dim wsFrom As Worksheet
    Dim lngRow As Long 'Row in the "new worksheet"
    'do not need a variable for the "from" sheets, as that should be a static value
    'Assuming row 2 for the static worksheets, change if that's not correct.

    Set wb = ActiveWorkbook
    Set wsTo = wb.Sheets.Add(wb.Worksheets(1))
    wsTo.Name = "Combined"
    
    lngRow = 2 'Assuming we're starting at row 2 for the data
    
    lngRow = 2
    
    For Each wsFrom In wb.Worksheets
        If wsFrom.Name = "Combined" Then
        Else
            If Len(wsTo.Range("A1")) = 0 Then
                wsTo.Rows(1).Value = wsFrom.Rows(1).Value
            End If
            wsTo.Rows(lngRow).Value = wsFrom.Rows(2).Value
            lngRow = lngRow + 1
        End If
    Next wsFrom
    
    Set wsTo = Nothing
    Set wb = Nothing
End Sub

Give it a try, customize it to fit your needs, ask questions if need be, and let us know the result.

But if MS Query suits you better, that'd be an option as well. I need to use that more myself.

 

[kjv1611]

Man, I couldn't get MS Query to work on the test workbook I made up... one row for column headers, and one row of data... dummy data, but it's there.

I tried as just 2 rows - one header, one data, no go... I formatted as table... still no go..

Keep getting this message from MS Query:
"This data source contains no visible tables"

Also, I tried while open inside the same workbook as well as in a new workbook.

Is it just Monday-itis on a Wednesday?

 

[Andrzejek]

Did you read How Do I Get Great Answers To my Tek-Tips Questions ?

I need to extract a row out of every sheet

the rows are in the exact same position in everysheet

It will take several additinal posts from people to get out of you exactly what you want.

Like kjv1611 said: do yourself and others a favor and tell us exactly what you have and what are you trying to get.

Have fun.

---- Andy

 

[kjv1611]

Ah, on the MS Query bit... I forgot about the checking 'All tables' option.

I wonder if there's a way to change that to a default option for Excel workbooks only?

 

[SkipVought]

You only have to select that ONE TIME.

In the 15+ years that I have been using MS Query for accessing other workbooks, I only have to change that when I get a new version of MS Office (Excel)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

Okay. So I guess it's in Excel options then. I did it in Microsoft Query. And I think I did only do it once. It's been a while since the last time I tried much to mess with it - and since then, I've probably had a couple reinstalls of Windows or Office at least.

Anyway, I put together a UNION query that would do the same with MS Query as the VBA script does. It is nice and short and quick to build. I just had to copy the text out to Notepad and clean it up to make it easier to copy/paste. So here's what I put together:

SELECT ID, name, add, city, state, zip, phone, phone2
FROM `C:\Users\[highlight]<filepath>[/highlight]\Desktop\TestQuery.xlsx`.`Table1$` 
UNION
SELECT ID, name, add, city, state, zip, phone, phone2
FROM `C:\Users\[highlight]<filepath>[/highlight]\Desktop\TestQuery.xlsx`.`Table2$` 
UNION
SELECT ID, name, add, city, state, zip, phone, phone2
FROM `C:\Users\[highlight]<filepath>[/highlight]\Desktop\TestQuery.xlsx`.`Table3$` 
UNION
SELECT ID, name, add, city, state, zip, phone, phone2
FROM `C:\Users\[highlight]<filepath>[/highlight]\TestQuery.xlsx`.`Table4$`

The workbook, worksheets, tables, and data, are all just dummy data I tossed together. I mean it's just a bunch of nonsense. Though I named it as an address book (field names), I just stuck a bunch of numbers in there, b/c that was easiest to do quickly.

Anyway, I started out with same column headers in each sheet, and then in Row 2, I put one row of numbers. So from that I got this:
[TT]ID name add city state zip phone phone2
2.0 2.0 2.0 222.0 22.0 22
33.0 33.0 33.0 3.0 33.0 33 3
111.0 111.0 111.0 111.0 11.0
444.0 444.0 444.0 444.0 444.0 [/TT]

Well, that's the data results anyway.

Pretty nifty method.

Thanks to Skip (again) for getting me motivated. No, I didn't need to hear some famous person talking to motivate me, such as is going on at the Get Motivated shindig.

By the way, you'll notice that there's a straggler column or two of data beyond the rest... tha't b/c I just randomly filled in any # of cells in each sheet.. on same row.

When I copied/pasted the results from MS Query, it only gave the values, so I typed them above the returned data.

Sorry the pasted data didn't line up too well either, but I've already put too much time into tinkering with this as it is.

 

[kjv1611]

Skip,

I'd like to request that you add in a statement in the MS Query FAQ stating that you need to "show all tables" the first go round, b/c I'm sure others would forget it like I did. Well, I do forget things pretty easily.

 

[SkipVought]

Steve, thanx! DONE!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]