Copying a specific range into another excel workbook

Question

I am looking to export a specific range of data from one workbook to a master workbook. I have already figured out how to overall copy from one to another but I'd like to modify my existing coding. Currently, the macro is taking all of row 2 from the workbook and copying it into this master file which is working great, however I am looking to do some more things in the master file so I need just columns A2:HD2 to copy and paste into the master sheet. Below is what we are using, can anyone help me figure out how to just get A2:HD2 and not all of row 2 into my master sheet?

Dim LN, Match As Integer
Dim wb As Workbook
Dim Name As String
Name = "Master sheet path here"

Application.ScreenUpdating = False

Sheets("LADB Bulk Upload").Select
LN = Range("A2").Value



Rows("2:2").Select
Selection.Copy

Set wb = Workbooks.Open(Filename:=Name)
If IsError(Application.Match(LN, ActiveSheet.Range("A:A"), 0)) Then

    Range("A100000").End(xlUp).Select
    ActiveCell.Offset(1, 0).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Else

    Match = Application.Match(LN, wb.Sheets("Sheet1").Range("A:A"), 0)

    Cells(Match, 1).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End If

Application.CutCopyMode = False

ActiveWorkbook.Save
ActiveWorkbook.Close

Application.ScreenUpdating = True

You can change `Rows("2:2").Select` to `Range("A2:HD2").Select`. However, I'd recommend updating your code to try and get rid of `Select` altogether. — dwirony, May 01 '18 at 13:37

score 0 · Answer 1 · answered May 01 '18 at 13:37

0

Replace

Rows("2:2").Select
Selection.Copy

With

Range("A2:HD2").Copy

Ideally you should work with ranges instead of using Select. You'll find plenty of information about that elsewhere. That said, if the code works, and isn't especially slow, it hardly matters.

answered May 01 '18 at 13:37

CFO

288
1
11

To read on how to avoid select (Which is best practice in coding in VBA) you can visit this question: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Luuklag May 01 '18 at 13:43

paul bica · Answer 2 · 2018-05-01T16:37:44.183

This code is refactored to copy only the A2:HD2 range, and without using Select

Option Explicit

Public Sub CopyA2HD2()
    Dim mainWb As Workbook, mainWs As Worksheet, mainLr As Long, mainCol As Range
    Dim thisWs As Worksheet, findTxt As String, foundCell As Variant

    Set thisWs = ThisWorkbook.Worksheets("LADB Bulk Upload")    'Current file

    Application.ScreenUpdating = False
    On Error Resume Next 'Expected errors: File not found, and Sheet Name not found
    Set mainWb = Workbooks.Open(Filename:="Master sheet path here")

    If Err.Number = 0 Then    'If master file is found, and open, continue
        Set mainWs = mainWb.Worksheets("Sheet1")
        If Err.Number > 0 Then Exit Sub    'If "Sheet1" in master file is not found exit
        mainLr = mainWs.Cells(mainWs.Rows.Count, "A").End(xlUp).Row 'Last row in master
        Set mainCol = mainWs.Range(mainWs.Cells(1, "A"), mainWs.Cells(mainLr, "A"))

        findTxt = thisWs.Range("A2").Value
        foundCell = Application.Match(findTxt, mainCol, 0) 'Search column A in master

        If Not IsError(foundCell) Then                     'If text was found in master
            Set foundCell = mainWs.Cells(foundCell, "A")   'Copy A2:HD2 to same row
        Else
            Set foundCell = mainWs.Cells(mainLr + 1, "A")  'Else, copy A2:HD2 to last row
        End If

        thisWs.Range("A2:HD2").Copy
        foundCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
        foundCell.Select
        Application.CutCopyMode = False
        mainWb.Close SaveChanges:=True
    End If
    Application.ScreenUpdating = True
End Sub

A few notes about your code

As already mentioned, avoid using Select and Activate if possible
Use Option Explicit at the top of every module, so the compiler can catch missing variables
Don't use reserved keywords as variable names to avoid shadowing the built-in objects
- Words like Name, Match, etc
Use Long variable type instead of Integer
- According to MSDN VBA silently converts all Integers to Longs
Always refer to ranges explicitly: Rows("2:2") implicitly uses the ActiveSheet
- It takes a lot of care and maintenance work to make sure that the expected sheet is active
Code indentation and proper vertical white-space will help visualize structure and flow clearer

Copying a specific range into another excel workbook

2 Answers2