Macro imports data into cols A,B,C. Would like the data in D,E,F
 

I have a macro that gathers information from the second row of other excel
sheets and puts that information all into one worksheet. It inserts that
information into columns A,B, and C of a new worksheet sheet.

For example:

Folder: C:/test contains numerious excel files that hold infromation in the
first two columns.

When I run the macro, A new sheet is created with the infromation from the
columns populated into the first 3 columns. I would like it populted into
columns D,E, and F.

This is what appears on the created worksheet:
A B C
1 File1 2007 $1234
2 File2 2007 $5246
3 File3 2007 $2456
4 File4 2007 $299

The Macro Code:

Sub RDB_Merge_Data()
Get_File_Names _
MyPath:="C:\test", _
Subfolders:=False, _
ExtStr:="*.xl*"

If fnum = 0 Then Exit Sub

Get_Data _
FileNameInA:=True, _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
SourceRng:="A2:b1", _
StartCell:=""
End Sub

Macro imports data into cols A,B,C. Would like the data in D,E,F
 

BZeyger,

You didn't declare R as a variable (maybe others too).

hth,

Doug

"BZeyger" wrote in message
...
I have a macro that gathers information from the second row of other excel
sheets and puts that information all into one worksheet. It inserts that
information into columns A,B, and C of a new worksheet sheet.

For example:

Folder: C:/test contains numerious excel files that hold infromation in
the
first two columns.

When I run the macro, A new sheet is created with the infromation from the
columns populated into the first 3 columns. I would like it populted into
columns D,E, and F.

This is what appears on the created worksheet:
A B C
1 File1 2007 $1234
2 File2 2007 $5246
3 File3 2007 $2456
4 File4 2007 $299

The Macro Code:

Sub RDB_Merge_Data()
Get_File_Names _
MyPath:="C:\test", _
Subfolders:=False, _
ExtStr:="*.xl*"

If fnum = 0 Then Exit Sub

Get_Data _
FileNameInA:=True, _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
SourceRng:="A2:b1", _
StartCell:=""
End Sub

Macro imports data into cols A,B,C. Would like the data in D,E,F
 

BZeyger,

Sorry I posted the answer to another post!

Doug

"BZeyger" wrote in message
...
I have a macro that gathers information from the second row of other excel
sheets and puts that information all into one worksheet. It inserts that
information into columns A,B, and C of a new worksheet sheet.

For example:

Folder: C:/test contains numerious excel files that hold infromation in
the
first two columns.

When I run the macro, A new sheet is created with the infromation from the
columns populated into the first 3 columns. I would like it populted into
columns D,E, and F.

This is what appears on the created worksheet:
A B C
1 File1 2007 $1234
2 File2 2007 $5246
3 File3 2007 $2456
4 File4 2007 $299

The Macro Code:

Sub RDB_Merge_Data()
Get_File_Names _
MyPath:="C:\test", _
Subfolders:=False, _
ExtStr:="*.xl*"

If fnum = 0 Then Exit Sub

Get_Data _
FileNameInA:=True, _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
SourceRng:="A2:b1", _
StartCell:=""
End Sub

Macro imports data into cols A,B,C. Would like the data in D,E,F
 

You didn't post Ron's code that did the real work Get_Data from:
http://www.rondebruin.nl/files/mergecode.txt

It looks like you're passing True to the FileNameInA parm in this statement:
Get_Data _
FileNameInA:=True, _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
SourceRng:="A2:b1", _
StartCell:=""


Which is used in this portion of Ron's code:

'Set the destination cell
If FileNameInA = True Then
Set destrange = BaseWks.Range("B" & rnum)
With SourceRange
BaseWks.Cells(rnum, "A"). _
Resize(.Rows.Count).Value = MyFiles(fnum)
End With
Else
Set destrange = BaseWks.Range("A" & rnum)
End If

It looks like you could change this (UNTESTED!!!):

If FileNameInA = True Then
Set destrange = BaseWks.Range("B" & rnum)
With SourceRange
BaseWks.Cells(rnum, "A"). _
Resize(.Rows.Count).Value = MyFiles(fnum)
End With
Else

to

If FileNameInA = True Then
Set destrange = BaseWks.Range("E" & rnum)
With SourceRange
BaseWks.Cells(rnum, "D"). _
Resize(.Rows.Count).Value = MyFiles(fnum)
End With
Else




BZeyger wrote:

I have a macro that gathers information from the second row of other excel
sheets and puts that information all into one worksheet. It inserts that
information into columns A,B, and C of a new worksheet sheet.

For example:

Folder: C:/test contains numerious excel files that hold infromation in the
first two columns.

When I run the macro, A new sheet is created with the infromation from the
columns populated into the first 3 columns. I would like it populted into
columns D,E, and F.

This is what appears on the created worksheet:
A B C
1 File1 2007 $1234
2 File2 2007 $5246
3 File3 2007 $2456
4 File4 2007 $299

The Macro Code:

Sub RDB_Merge_Data()
Get_File_Names _
MyPath:="C:\test", _
Subfolders:=False, _
ExtStr:="*.xl*"

If fnum = 0 Then Exit Sub

Get_Data _
FileNameInA:=True, _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
SourceRng:="A2:b1", _
StartCell:=""
End Sub

--

Dave Peterson