Copying data from External File

[Borg]

Hi,

I am trying to auto-populate a test file with various data from an
external file that the user selects. So far, it's been shifting the
data to the wrong cells on the test file. I'm a relative VBA beginner,
and was wondering what I'm doing wrong. Here is an example of what I'm
trying to do:

*************************************************
Sub AutoPopulateTest()

MsgBox "IN THE NEXT DIALOG BOX, FIND THE FILE" & (Chr(13)) & (Chr(13))
& _
"DOUBLE-CLICK ON IT TO" & (Chr(13)) & (Chr(13)) & _
"AUTO-POPULATE THE TEST SHEET.", vbInformation

TESTFILE = ActiveWorkbook.Name

'Open the Excel File that was selected
OldFile = Application.GetOpenFilename("Excel Workbook (*.xls), *.xls")

If OldFile <> False Then
Workbooks.Open Filename:=OldFile
End If

TESTFILE = ActiveWorkbook.Name

Windows(OldFile).Activate
Sheets("Sheet 3").Select
Range("BJ38").Select
Selection.Copy
Windows(TESTFILE).Activate
Sheets("Sheet 1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C20").Select

Windows(OldFile).Activate
Sheets("Sheet 4").Select
Range("BL38").Select
Selection.Copy
Windows(TESTFILE).Activate
Sheets("Sheet 1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C21").Select

End Sub

Thanks in advance.

 

[Guest]

in this code
Windows(TESTFILE).Activate
Sheets("Sheet 1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C20").Select

you never specify where you want the data pasted. You use whatever the
existing selection is. Select where you want to paste.

 

[Borg]

I see, after you explained it.

Thanks.

 

[Guest]

Here is the same thing you have just cleaned up a little. I assume that you
want this pasted in C20 and C21 on the newly opened file from BJ38 and BL38
of the file running the code...

Sub AutoPopulateTest()

MsgBox "IN THE NEXT DIALOG BOX, FIND THE FILE" & (Chr(13)) & (Chr(13))
& _
"DOUBLE-CLICK ON IT TO" & (Chr(13)) & (Chr(13)) & _
"AUTO-POPULATE THE TEST SHEET.", vbInformation

'Open the Excel File that was selected
OldFile = Application.GetOpenFilename("Excel Workbook (*.xls), *.xls")

If OldFile <> False Then
Workbooks.Open Filename:=OldFile
End If

Thisworkbook.Sheets("Sheet 3").Range("BJ38", "BL38").Copy
Workbooks(OldFile).Sheets("Sheet 1").Range("C20").PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats
Thisworkbook.Select
application.cutcopymode = false

End Sub

 

[Borg]

Thanks guys,

Very appreciative of all the help. :

 

[Guest]

I assume that you
want this pasted in C20 and C21 on the newly opened file from BJ38 and BL38

for me, this copies BJ30 to C20, BK38 to D20, BL38 to E20

Another look perhaps?

 

[Guest]

Absoluetly correct Tom. It is still mighty early in the morning here. How
about this...

Sub AutoPopulateTest()

MsgBox "IN THE NEXT DIALOG BOX, FIND THE FILE" & (Chr(13)) & (Chr(13))
& _
"DOUBLE-CLICK ON IT TO" & (Chr(13)) & (Chr(13)) & _
"AUTO-POPULATE THE TEST SHEET.", vbInformation

'Open the Excel File that was selected
OldFile = Application.GetOpenFilename("Excel Workbook (*.xls), *.xls")

If OldFile <> False Then
Workbooks.Open Filename:=OldFile
End If

Thisworkbook.Sheets("Sheet 3").Range("BJ38, BL38").Copy
Workbooks(OldFile).Sheets("Sheet 1").Range("C20").PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Transpose:=True
Thisworkbook.Select
application.cutcopymode = false

End Sub

 

[Guest]

Excellent

 

[Guest]

I should keep my code to myself until the coffee has hit my blood stream...