Extract Data from a single column from multiple sheets.

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
LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul NeralichBusiness OwnerCommented:
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
0
FordraidersAuthor Commented:
paul, no actually.. Just one column of data.
Thanks
fordraiders
0
Paul NeralichBusiness OwnerCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
PERFECT SIR !
Thank you very much for the help !!

fordraiders
0
FordraidersAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.