Copy Errors

[KK1966]

hi,

I don?t know which parts of below code have an inputting wrong, the objective is wanna have the external WB copy a range in my active sheets base on rows count towards.

Someone can help me check and modifying
Thanks all Experts,


im home As Worksheet
Dim Filename As String
Dim Wb As Workbook
Dim Range As Variant



With Application.FileDialog(msoFileDialogOpen)

Set home = ActiveWorkbook.ActiveSheet
.AllowMultiSelect = False
If .Show = -1 Then

FName = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If FName <> False Then
Set Wb = Workbooks.Open(FName)
Set Tgt = home.Cells(Rows.Count, "a").End(xlUp).Offset(1)

home.Range("F", Rows.Count).Value = Wb.Worksheets("Sheet1").Range("J13").Value

Wb.Close (False)

End If
End If
End With
End Sub

[GTO]

Hi there,

Sorry to have a question first, but I am unable to find
'.FileDialog(msoFileDialogOpen)' - either the command or constant :-(

What version of Excel are you using? (I am currently on 2000)

Mark

[Demosthine]

Good Evening.

After receiving a call from GTO regarding your use of msoFileDialogOpen and neither of us having used it, I started looking into your problem. It would appear you are displaying a File Open dialog twice.

Through reading MSDN (http://msdn.microsoft.com/) (Microsoft Developers Network) regarding the use of msoFileDialogOpen (http://msdn.microsoft.com/en-us/library/aa159478(office.10).aspx), I found that this seems to be more of a legacy method found in previous versions of Office. For compatibility reasons, Microsoft always ensures that there are a half-zillion different ways to complete any given task. That being said, you do not need to run your code on Lines 8, 11, and 12.

The .FileDialog method enables you to take a very generic Dialog Box and customize it to your needs. By default, this dialog will allow you to select and file of any file type and doesn't take any action upon a user selecting the file.


The next issue that I have come up with is that once you open the selected file, your ranges may or may not be valid. It would appear that are trying to select all of your used cells on the Home Workbook's ActiveSheet and copy it to the user-defined workbook. Have you tried copying the entire sheet rather than just the data?


wb.Worksheets(1).Copy home.worksheets(1)


If you would rather copy just the data, i.e. you want the external data to start at a certain Row in your active workbook, you can define your tgt Range using the property UsedRange. This will define a range for all cells that have any data in them. When you actually copy the cells, you'll also to remember that not all Workbooks will have a "Sheet1".



Public Sub CopyTest()
Dim home As Worksheet
Dim Filename As String
Dim Wb As Workbook
Dim Range As Variant
Dim tgt As Range

Set home = ActiveWorkbook.ActiveSheet

FName = Application.GetOpenFilename("Excel Files (*.xls), *.xls", MultiSelect:=False)
If FName <> False Then
Set Wb = Workbooks.Open(FName)
Set tgt = home.UsedRange

tgt.Copy Wb.Worksheets(1).Range("A1")
Wb.Close (False)
End If
End Sub


I'd also like to point out that when you close your external workbook, you are not saving the data, therefore your copied data will be lost. If I have the direction of the transfer wrong (from workbook containing code to user-defined workbook) backwards, just change the .Copy method to reference it correctly.

If this isn't what you wanted, let me know.
Scott

[KK1966]

Dear Demosthine and all

Thanks all to have learning me a much, I has using the Excel 2003 versions,
Your code is worked, but I have too many file open (which is same WS same location to copying) so I wanna have the rows count by ?col A? the vaule than have col F? paste to go,

That?s would saving more time to my job,

Do any I dea to modify your code to have paste count by base COL A values,

[Demosthine]

I'm sorry, but I'm not not sure I understand your "Col A vs Col F" reference. I'm assuming you mean that you want to copy the data based on the number of cells used in Column A. If that is the case, substitute my:


Set tgt = home.UsedRange


with


Set tgt = Range("$A$1:" & _
home.Range("A" & home.Rows.Count).End(xlUp).End(xlToRight).Address)


What this will do is it will start at the very last cell in Column A (A65536) and move up to the last cell it encounters that is not empty. Then, it will move from that cell all the way to the right to the last cell it encounters that is not empty.

Scott

[KK1966]

Scott

Thanks so much & I get it work

:yes