Extract Data from a single column from multiple sheets.

Fordraiders
Fordraiders used Ask the Experts™
on
Excel 2010
windows 10

What I  have:
I have a workbook with several sheets.
I need the information in ColumnA  (with a Column Name)

What I need:
I need to take the data from Column A(from all sheets) and put it in a text file.

Thanks
fordraiders
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Well, this is a start. It will send the values in Column A for each sheet in your workbook to a text file. However, I suspect you probably prefer a .csv with sheet1.A:A in column A, sheet2.A:A in column B, and so on, yeah?

Sub Write_column_A_data_from_all_sheets_to_textfile()
Dim rng As Range
Dim s
Dim LastRow As Long
Dim myFile As String
Dim SNarray
ReDim SNarray(1 To Sheets.Count)

myFile = "your path and filename.txt"
Open myFile For Append As #1

For s = 1 To Sheets.Count
   SNarray(s) = ThisWorkbook.Sheets(s).Name

   With Sheets(SNarray(s))
       LastRow = Range("A" & .Rows.Count).End(xlUp).Row
   End With
 
   Set rng = Range("A1:A" & LastRow)

   Write #1, (SNarray(s))

   For Each cell In rng
      Write #1, cell.Value
   Next cell

Next s

Close #1

End Sub

Author

Commented:
paul, no actually.. Just one column of data.
Thanks
fordraiders
Commented:
Fordraiders,

This should produce a single-column.csv with all values from column A from each sheet in your workbook.

Let me know if this works:


Sub Write_column_A_data_from_all_sheets_to_textfile()
Dim rng As Range
Dim s
Dim LastRow As Long
Dim myFile As String
Dim SNarray
ReDim SNarray(1 To Sheets.Count)

myFile = "your path and filename.csv"
Open myFile For Append As #1

For s = 1 To Sheets.Count
   SNarray(s) = ThisWorkbook.Sheets(s).Name

   With Sheets(SNarray(s))
       LastRow = Range("A" & .Rows.Count).End(xlUp).Row
   End With
 
   Set rng = Range("A1:A" & LastRow)

   For Each cell In rng
      Write #1, cell.Value
   Next cell

Next s

Close #1

End Sub

Author

Commented:
PERFECT SIR !
Thank you very much for the help !!

fordraiders

Author

Commented:
Paul, just an fyi.
I had add  2 lines
 to get the sheets to pull in the data correctly.



Sub Write_column_A_data_from_all_sheets_to_textfile()
Dim rng As Range
Dim s
Dim LastRow As Long
Dim myFile As String
Dim SNarray
ReDim SNarray(1 To Sheets.Count)

myFile = "C:\Users\xxxxx\Desktop\ALL_WITH_DISCD_State.csv"
Open myFile For Append As #1

For s = 1 To 13
'For s = 1 To Sheets.Count
    's = 1
   SNarray(s) = ThisWorkbook.Sheets(s).Name

   With Sheets(SNarray(s))
       Sheets(SNarray(s)).Select    ''  <-------------------------------------   HAD TO ADD THIS LINE
       LastRow = Range("A" & .Rows.Count).End(xlUp).Row
   End With
 
   Set rng = Range("A1:A" & LastRow)

   For Each cell In rng
      Write #1, cell.Value
   Next cell

LastRow = 0   '  <  ---------  HAD TO ADD THIS LINE

Next s

Close #1

MsgBox "DONE"

End Sub

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial