Using a macro to import from another spreadsheet
 

I have a spreadsheet which needs to have some data extracted from it
the data manipulated and then the whole thing exported as a text file.

I have no problem with the second and third part of this but I a
struggling with the first part:

How does one use a macro to import columns from another spreadsheet
The problem is I don't want all of the columns, only some and no
necessarily in the order in which they are in the original sheet
something like this. I will write this in English so that I hope
explain what I want:

Dim startRow as integer
StartRow = 6
For rows = StartRow to 65536

If Column A is empty then finish

Move to First blank row of output sheet

Import Row X, Column A from Input Sheet
Move to Next Column
Import Row X, Column D from Input Sheet
Move to Next Column
Import Row X, Column B from input sheet
(etc....)

Repeat until all rows are done

The problem is I don't know how to import data from one workbook t
another when you don't know how many rows there are

--
zaphod200
-----------------------------------------------------------------------
zaphod2003's Profile: http://www.excelforum.com/member.php...fo&userid=3436
View this thread: http://www.excelforum.com/showthread.php?threadid=54139

Using a macro to import from another spreadsheet
 

etc kind of leaves a lot to guessing, but....

And you'll be getting data from worksheets. I used fWks as the From Worksheet
and tWks as the To Worksheet, so you'll have both of those workbooks open and
you'll need to change the code to point at the correct worksheet in each of
those workbooks:

Option Explicit
Sub testme01()

Dim fWks As Worksheet
Dim tWks As Worksheet
Dim iRow As Long
Dim LastRow As Long
Dim NextRow As Long

Set fWks = Workbooks("book2.xls").Worksheets("sheet2")
Set tWks = Workbooks("book1.xls").Worksheets("sheet1")

With tWks
'find last used row in column A and add 1
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

With fWks
'find the last used row in column A
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = 6 To LastRow
tWks.Cells(NextRow, "A").Value = .Cells(iRow, "A").Value
tWks.Cells(NextRow, "B").Value = .Cells(iRow, "D").Value
tWks.Cells(NextRow, "C").Value = .Cells(iRow, "B").Value
'keep going
'get ready to import to the next row
NextRow = NextRow + 1
Next iRow
End With

End Sub



zaphod2003 wrote:

I have a spreadsheet which needs to have some data extracted from it,
the data manipulated and then the whole thing exported as a text file.

I have no problem with the second and third part of this but I am
struggling with the first part:

How does one use a macro to import columns from another spreadsheet.
The problem is I don't want all of the columns, only some and not
necessarily in the order in which they are in the original sheet,
something like this. I will write this in English so that I hope I
explain what I want:

Dim startRow as integer
StartRow = 6
For rows = StartRow to 65536

If Column A is empty then finish

Move to First blank row of output sheet

Import Row X, Column A from Input Sheet
Move to Next Column
Import Row X, Column D from Input Sheet
Move to Next Column
Import Row X, Column B from input sheet
(etc....)

Repeat until all rows are done

The problem is I don't know how to import data from one workbook to
another when you don't know how many rows there are.

--
zaphod2003
------------------------------------------------------------------------
zaphod2003's Profile: http://www.excelforum.com/member.php...o&userid=34362
View this thread: http://www.excelforum.com/showthread...hreadid=541399

--

Dave Peterson