Using a macro to import from another spreadsheet

[zaphod2003]

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.

[Dave Peterson]

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