HELP! How to properly copy excel data from one workbook to another

[edkenang]

I always get either automation error or run-time error. I will appreciate anyone's help. I can't figure out how to solve the line,

wb.Worksheets("Sheet1").Range("A" & i).Value = wb2.Worksheets("tc1").Range("A" & i).Value

The whole code can be seen below:




Option Explicit


Public Sub Button1_Click()
Dim wb As Workbook, wb2 As Workbook
Dim ws As Worksheet
Dim vFile As Variant


'Set source workbook
Set wb = ActiveWorkbook
'Open the target workbook
vFile = Application.GetOpenFilename("Excel-files,*.xlsx", _
1, "Select One File To Open", , False)
'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile


Set wb2 = ActiveWorkbook

Dim lngWriteRow As Long
lngWriteRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Dim lastna As String
lastna = "A" + Str(lngWriteRow)
lngWriteRow = Replace(lngWriteRow, " ", "")

Dim i As Integer
i = 2

Do


wb.Worksheets("Sheet1").Range("A" & i).Value = wb2.Worksheets("tc1").Range("A" & i).Value
ActiveWorkbook.Close savechanges:=True
i = i + 1
While i < lngWriteRow
Wend
Loop

End Sub





Thank you!!!

 

[AlphaFrog]

Do you want to copy to or from the workbook that is opened (wb2) ?

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] Button1_Click()
    [COLOR=darkblue]Dim[/COLOR] wb2 [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] ws     [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] vFile  [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] lngWriteRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=green]'Set Destination workbook[/COLOR]
    [COLOR=darkblue]Set[/COLOR] ws = ActiveWorkbook.Worksheets("Sheet1")
    
    [COLOR=green]'Open the Source workbook[/COLOR]
    vFile = Application.GetOpenFilename("Excel-files,*.xlsx", _
             1, "Select One File To Open", , [COLOR=darkblue]False[/COLOR])
    [COLOR=green]'if the user didn't select a file, exit sub[/COLOR]
    [COLOR=darkblue]If[/COLOR] TypeName(vFile) = "Boolean" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wb2 = Workbooks.Open(vFile)
    
    [COLOR=green]'Copy from wb2[/COLOR]
    lngWriteRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    ws.Range("A2:A" & lngWriteRow).Value = ActiveSheet.Range("A2:A" & lngWriteRow).Value
    
[COLOR=green]'    'Copy to wb2[/COLOR]
[COLOR=green]'    lngWriteRow = ws.Range("A" & Rows.Count).End(xlUp).Row[/COLOR]
[COLOR=green]'    ActiveSheet.Range("A2:A" & lngWriteRow).Value = ws.Range("A2:A" & lngWriteRow).Value[/COLOR]
    
    [COLOR=green]'Close wb2[/COLOR]
    [COLOR=green]'wb2.Close SaveChanges:=False[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[My Aswer Is This]

Please explain what range you want to copy and where do you want this copied to.

 

[edkenang]

Using excel vba, I want to copy the first column of "tc1" sheet of another workbook the user will select, then copy it in my existing opened workbook.

 

[edkenang]

Thank you very much AlphaFrog! it worked!