VBA to Copy from one workbook to another workbook

[gleamng]

Good day everyone
I need help on how to copy from a workbook to another workbook. For instance i want to copy from column D5 to last row from sheet named INDEX from workbook General.xlsm to column G7 downward, sheet named"Pearl" in workbook named "summary.xlsm".

Thank you for your help always and have a great day all.

 

[Alex Blakenburg]

Give this a try on a copy of your worbook.
It assumes you have both workbooks open.

I have the "copy values" line as active but have provided a commented out section if you want to copy values and formats.
For the latter comment out the sumRng.Resize(genRng.Rows.Count).Value = genRng.Value and uncomment the 4 lines in the Copy values and formats section.

Sub CopyData()

    Dim wbSum As Workbook, wbGen As Workbook
    Dim shtSum As Worksheet, shtGen As Worksheet
    Dim sumLastRow As Long, genLastRow As Long
    Dim sumRng As Range, genRng As Range
    
    Application.ScreenUpdating = False
    
    Set wbSum = Workbooks("summary.xlsm")
    Set wbGen = Workbooks("General.xlsm")
    
    Set shtSum = wbSum.Worksheets("Pearl")
    Set sumRng = shtSum.Range("G7")
 
    Set shtGen = wbGen.Worksheets("INDEX")
    With shtGen
        genLastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        If genLastRow < 5 Then
            MsgBox "No data to copy in Column D"
            Exit Sub
        End If
        Set genRng = .Range(.Cells(5, "D"), .Cells(genLastRow, "D"))
    End With
    
    ' Copy values only
    sumRng.Resize(genRng.Rows.Count).Value = genRng.Value
    
    ' --- Alternative copy & paste method ---
    ' Copy values and formats
'    genRng.Copy
'    sumRng.PasteSpecial Paste:=xlPasteValues
'    sumRng.PasteSpecial Paste:=xlPasteFormats
'    Application.CutCopyMode = False
    ' --- end copy paste ---
    
    sumRng.EntireColumn.AutoFit

    Application.ScreenUpdating = True
End Sub

 

[gleamng]

Thanks a million, it worked perfectly as wanted. God bless you richly.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.