Copying a column from one workbook to another

Moonbeam111

New Member
Joined
Sep 24, 2018
Messages
4
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.

Code:
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. :confused:
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,651
Try changing this:

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

To
Code:
[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

New Member
Joined
Sep 24, 2018
Messages
4
Thanks for the reply but I'm still getting the same error message and its still pointing at the same line.
 

Moonbeam111

New Member
Joined
Sep 24, 2018
Messages
4
Oops sorry I accidentally posted while pressing enter. I meant to say this.

Code:
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.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,416
Messages
5,601,550
Members
414,458
Latest member
mekhallet salah

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top