Copying a column from one workbook to another

[Moonbeam111]

Hi all. Long time lurker here. Usually I can get my excel question answered by searching the forums but I'm having a bit of a problem with this one, despite its simplicity.

I have two workbooks open, I want to transfer what is in cell H9:H50 in my first open workbook to H9:H50 in the second workbook (preferably pasting only values).

I have this code in Visual Basic, which I found somewhere on these forums, but modified the names and cells to my needs.

Sub Button2_Click()
    Dim wb1 As Workbook
    Dim wb2 As Workbook




    Set wb1 = ActiveWorkbook


    'Copy what you want from workbook 1.
    wb1.Worksheets("Test").Range("H9:H50").Copy 'Change worksheet




    'Open workbook 2
    Set wb2 = Workbooks.Open("C:\Users\Matt\Desktop\Excel Folder\Excel Workbook 2.xlsm")




    'Paste to worksheet in workbook2:
    Application.DisplayAlerts = False
    wb2.Sheets("Sheet1").Range("H9:H50").PasteSpecial
    Application.CutCopyMode = False
    Range("A1").Select




    'Close workbook
    wb2.Close savechanges:=True
    Application.DisplayAlerts = True


End Sub

But everytime I try to run the macro I get Error: 57121, Application defined or object defined error. When I click debug it highlights this line in yellow --> wb2.Sheets("Sheet1").Range("H9:H150").PasteSpecial.

Now, I don't know if this has a problem to do with Excel or the VB code. Any help here.

 

[mrshl9898]

Try changing this:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="class: xl63, width: 64"]  wb2.Sheets("Sheet1").Range("H9:H50").PasteSpecial
[/TD]
[/TR]
</tbody>[/TABLE]

To

[TABLE="width: 64"]
 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="class: xl65, width: 64"]wb2.Sheets("Sheet1").Range("H9:H50").Select[/TD]
 [/TR]
 [TR]
  [TD="class: xl65"]Selection.PasteSpecial  xlpastevalues[/TD]
[/TR]
</tbody>[/TABLE]

 

[Moonbeam111]

Thanks for the reply but I'm still getting the same error message and its still pointing at the same line.

 

[Moonbeam111]

I managed to get it working with this code for anyone interested.

Dim wbk as Workbook

 

[Moonbeam111]

Oops sorry I accidentally posted while pressing enter. I meant to say this.

Dim wbk as Workbook

strFirstFile = "Source of File.xlsm"
strSecondFile = "Source of File.xlsm"

Range("H4:H50").copy

Set wbk = Workbooks.open(strSecondFile)
With wbk.Sheets("Sheet1")
Range("H4:H50").Paste Special xlPasteValuesAndNumberFormats

End With
End Sub

But I have a question, how can I copy over without having to open the workbook everytime? I.E. Just copy the ranges over because I always have these two workbooks open simultaneously anyway.