-3

Can someone please help?

What is the shortest excel-vba code to copy all of column B (starting a cell B2) from Book1.xls to Column B (starting at cell B2) in Book2?

I will be copying multiple files Book1, Book2, and Book3. Book2 and Book3 need to be pasted in the blank space (lastrow +1) in column B. 

Sub CopyWorkbooks1()

    Workbooks.Open "C:\test\RESOURCE\Book1.xls"
    Range("B2:E15").Copy
    Workbooks.Open "C:\test\PUBLISH\PubFile.xls"
    Range("B2").Select
    ActiveSheet.Paste

    'activate, copy, and, paste G and H -- F and G
    Workbooks("book1.xls").Activate
    Range("G2:H15").Copy
    'activate and paste to PubFile
    Workbooks("pubfile.xls").Activate
    Range("F2:G15").Select
    ActiveSheet.Paste

End Sub

Sub LastRowMacro1()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each cell In ws.Columns(2).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell

End Sub

Your help is greatly appreciated.

0m3r
  • 12,286
  • 15
  • 35
  • 71
PBlas
  • 1
  • Hi, what have you tried so far? – Marcucciboy2 Oct 24 '18 at 13:37
  • Welcome to SO. Please provide with what you have tried so far. SO is to assist with errors or problems in code that you experiencing. – Jaques Oct 24 '18 at 13:50
  • 2
    [edit] your question with the code, please take it out of the comments. it's nothing but jibberish there. – Scott Holtzman Oct 24 '18 at 14:19
  • 1
    take a look at [how to avoid using select/activate in vba](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), it's not a good idea to bank on the activation to always work properly – Marcucciboy2 Oct 24 '18 at 14:24
  • 1
    What's your question? "Please help"...with what? – BruceWayne Oct 24 '18 at 14:28
  • Apologies for the jibberish. This is my first time using the site. Thanks to all for the comments and assistance. I will be trying the code below. – PBlas Oct 24 '18 at 16:32

1 Answers1

0

The answer to your question is... After you open your workbooks, then use this line of code. Note: Change the workbook names and worksheet names, and ranges as needed.

Workbooks("Book1").Sheets(1).Range("B2", Cells(Rows.Count, "B").End(xlUp)).Copy Workbooks("Book2").Sheets(1).Range("B2")
GMalc
  • 2,608
  • 1
  • 9
  • 16