VBA Read, write records to one sheet. Query

[dan007004]

Hi,

My VBA code below is used to open workbooks named (x), read values and then close the workbook. However I need to modify to read multiple sheets within the workbook.

I have tried some options i.e.


Workbooks(2).Activate 'change active book back to the one we're reading
Sheets(1).Select
Cells.Select
Sheets(2).Select
Cells.Select

This doesn;t work, also tried the array Sheets(array(1,2,3 Etc

Any other options?

thanks
dan




Sub translist()
'clear all cells on the transactions worksheet
Sheets("Transactions").Select
Cells.Select
Selection.Clear

'find all workbooks in the directory
Dim tcfilename, tcpath
Dim Wkb As Workbook

Outrow = 1

'write headers in row 1
Sheets("Transactions").Cells(Outrow, 1).Value = "1"
Sheets("Transactions").Cells(Outrow, 2).Value = "2"
Sheets("Transactions").Cells(Outrow, 3).Value = "3"
Sheets("Transactions").Cells(Outrow, 4).Value = "4"
Sheets("Transactions").Cells(Outrow, 5).Value = "5"
Sheets("Transactions").Cells(Outrow, 6).Value = "6"
Sheets("Transactions").Cells(Outrow, 7).Value = "7"
Sheets("Transactions").Cells(Outrow, 8).Value = "8"
Sheets("Transactions").Cells(Outrow, 9).Value = "9"
Sheets("Transactions").Cells(Outrow, 10).Value = "10"
Sheets("Transactions").Cells(Outrow, 11).Value = "11"
Sheets("Transactions").Cells(Outrow, 12).Value = "12"
Sheets("Transactions").Cells(Outrow, 13).Value = "13"
Sheets("Transactions").Cells(Outrow, 14).Value = "14"
Sheets("Transactions").Cells(Outrow, 15).Value = "15"
Sheets("Transactions").Cells(Outrow, 16).Value = "16"
Sheets("Transactions").Cells(Outrow, 17).Value = "17"
Sheets("Transactions").Cells(Outrow, 18).Value = "18"
Sheets("Transactions").Cells(Outrow, 19).Value = "19"
Sheets("Transactions").Cells(Outrow, 20).Value = "20"
Sheets("Transactions").Cells(Outrow, 21).Value = "21"




Outrow = Outrow + 1

tcpath = CurDir
srchfilename = tcpath & "\" & "TC* bank statement v2.xls" 'renamed to version 2 due to version control
tcfilename2 = Dir(srchfilename)

tcfilename3 = tcpath & "\" & tcfilename2

Do Until tcfilename2 = ""
Workbooks.Open Filename:=tcfilename3, UpdateLinks:=0
Workbooks(1).Activate 'transactions list
'*****************************************************************************************
'Sub Translist2()
'from recorded macro
'Workbooks.Open Filename:= _
' "C:\Documents and Settings\c21816\My Documents\Flexaccount Dev Project\UAT\test data\TC003 bank statement v1.xls" _
' , UpdateLinks:=0
' ActiveWindow.Close
' end of recorded macro



'create list of transactions for all test cases in 1 list


'clear all cells on the transactions worksheet
'Sheets("Transactions").Select
' Cells.Select
' Selection.Clear

'add titles to first row

'set first workbook active

'Sheets("Transactions").Cells(outrow, 1).Value = "Testcase"
'Sheets("Transactions").Cells(outrow, 2).Value = "Ac"
'Sheets("Transactions").Cells(outrow, 3).Value = "Date"
'Sheets("Transactions").Cells(outrow, 4).Value = "Am"
'Sheets("Transactions").Cells(outrow, 5).Value = "Type"
ProcSheet = ActiveSheet.Name
inrow = 10
'outrow = outrow + 1

Workbooks(2).Activate 'change active book back to the one we're reading
Sheets(1).Select
Cells.Select


Range("A65536").Select
Selection.End(xlUp).Select
lastrow = ActiveCell.Row
ProcSheet = ActiveSheet.Name
Do Until inrow > lastrow

intran = Cells(inrow, 3).Value

'Transaction codes selected from column 3 of the statement sheet
'Debit = 1
'Credit = 2

Select Case intran
Case "1"
outputtran = 1
Case "2"
outputtran = 2
Case "3"
outputtran = 1
Case "4"
outputtran = 1
Case "5"
outputtran = 1





End Select

If outputtran = 1 Then
Workbooks(1).Sheets("Transactions").Cells(Outrow, 1).Value = Cells(3, 1).Value
Workbooks(1).Sheets("Transactions").Cells(Outrow, 2).Value = Cells(2, 1).Value Workbooks(1).Sheets("Transactions").Cells(Outrow, 3).Value = Cells(2, 2).Value
Workbooks(1).Sheets("Transactions").Cells(Outrow, 4).Value = Cells(inrow, 1).Value
Workbooks(1).Sheets("Transactions").Cells(Outrow, 5).Value = Cells(inrow, 6).Value
Workbooks(1).Sheets("Transactions").Cells(Outrow, 6).Value = Cells(inrow, 3).Value
Workbooks(1).Sheets("Transactions").Cells(Outrow, 7).Value = Cells(inrow, 4).Value
Workbooks(1).Sheets("Transactions").Cells(Outrow, 10).Value = Cells(inrow, 5).Value
Workbooks(1).Sheets("Transactions").Cells(Outrow, 16).Value = Cells(inrow, 10).Value
Workbooks(1).Sheets("Transactions").Cells(Outrow, 17).Value = Cells(inrow, 11).Value
Workbooks(1).Sheets("Transactions").Cells(Outrow, 18).Value = Cells(2, 3).Value


cardtype = Workbooks(2).Sheets(1).Cells(inrow, 9).Value
Workbooks(1).Sheets("Transactions").Cells(Outrow, 8).Value = "<" & CStr(Application.WorksheetFunction.VLookup(Workbooks(2).Sheets(1).Cells(inrow, 9).Value, Workbooks(2).Sheets(1).Range("A5:J12"), 3))

[arlu1201]

Please put your code in code tags before the moderators ask you to do so. Then we can proceed with helping you.