I´m new and starting to make baby steps in VBA. I want to make a macro that opens CSV files and that asks for a range to be selected from the first file (I need to select a column vector and that range will be the same along the macro), extracts the data from that column vector and paste it as a row vector (transposes the data) in the original active workbook. I have tried lots of things but I think I´m missing some knowledge. I think maybe I need to make an array inside an array cause A(i)
has more than one element, it is itself an array. This is what I wrote:
Option Explicit
Option Base 1
Sub x()
Dim FileNames() As Variant, nw As Integer
Dim i As Integer, A() As Variant
Dim tWB As Workbook, aWB As Workbook
Set tWB = ThisWorkbook
Dim UserRange As Range
FileNames = Application.GetOpenFilename("CSV Files (*.csv*),*.csv*", , , , True)
nw = UBound(FileNames)
Application.ScreenUpdating = False
ReDim A(nw) As Variant
Set UserRange = Application.InputBox("Select range", "Range Selection", , , , , , 8)
For i = 1 To nw
Workbooks.Open FileNames(i)
Set aWB = ActiveWorkbook
A(i) = aWB.Sheets(1).Range("UserRange")
tWB.Activate
tWB.Sheets(1).Range.Cells(i, 1) = WorksheetFunction.Transpose(A)
aWB.Close SaveChanges:=False
Next i
End Sub
Thank you, I really appreciate your help
Your project falls into 4 parts which, technically speaking, require 4 questions which I shall not answer here because in this forum one thread is supposed to deal with one question only.
Range
object is tied to one worksheet. You can't specify the "same" range in each sheet. You also have no worksheet open at the time of calling the InputBox. It's possible to specify the same range address for each sheet but you can't get that from the InputBox. I suspect you should enter a column ID (like "C") or a column number (like 3) or, most likely, a column caption (like "APPL") and let the next section look for that column in each open file.Application.ScreenUpdating = False
to never see the open file. You don't need toActivate
anything. In fact, you don't need the variableaWB
since you work with theActiveWorkbook
. But consider to specify a worksheet. Your loop is good but it isn't finished. Make it run, Debug.Print-ing the name of each workbook / worksheet before clolsing it so that you have something that you can ask questions about if needed.Arr = UserRange.Value
(whereArr
is a Variant - notA()
), thentWB.Workheets(1).Cells(1, 1).Value = Application.Transpose(Arr)
. Here the row isn't specified. You probably need to specify the first blank cell under column A, which is a subject in this fourth question.