How to Copying and paste value with v from another file

How to copy and paste value with v from another file

Requirement:

The user has this code they want to copy values from selected range from another excel file (sheet named "b") to active work book ( named "a").

Its a good code but there is a problem.

The user wants it to this job in range a1:g300 and does not want it to find first empty cell ?

Is it possible?

Here is the code :

Code:

Sub baazkhaanMoein_Click()

'Last cell in column
Dim WS As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long


Set WS = Worksheets("a")
With WS
    Set LastCell = .Cells(.Rows.Count, "C").End(xlUp)
    LastCellRowNumber = LastCell.Row + 1
End With


Dim wb As Workbook, wb2 As Workbook
Dim vFile As Variant


'Set source workbook
Set wb = ActiveWorkbook


'Open the target workbook
vFile = Application.GetOpenFilename("Excel-files,*.xlsx", _
    1, "Select One File To Open", , False)


'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile


'Set selectedworkbook
Set wb2 = ActiveWorkbook


'Select cells to copy
wb2.Worksheets("b").Range("b1:c10").Select
Selection.Copy


'Go back to original workbook you want to paste into
wb.Activate


'Paste starting at the last empty row
wb.Worksheets("a").Range("C" & LastCellRowNumber).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True


'Close and save the workbook you copied from
wb2.Save
wb2.Close


End Sub

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148294-copying-and-paste-value-with-v-from-another-file

Solution:

You can adapt following code to your specific situation ...

Code:

Sub baazkhaanMoein_Click()
     
    Dim wb As Workbook, wb2 As Workbook
    Dim vFile As Variant
     
     'Set source workbook
    Set wb = ActiveWorkbook
     
     'Open the target workbook
    vFile = Application.GetOpenFilename("Excel-files,*.xlsx", _
    1, "Select One File To Open", , False)
     
     'if the user didn't select a file, exit sub
    If TypeName(vFile) = "Boolean" Then Exit Sub
    Workbooks.Open vFile
     
     'Set selectedworkbook
    Set wb2 = ActiveWorkbook
     
     'Select Source Range to copy
    wb2.Worksheets("b").Range("A1:G300").Copy
     
     'Go back to original workbook you want to paste into
    wb.Activate
     
     'Paste Values ... starting at Cell A1
    wb.Worksheets("a").Range("A1").PasteSpecial Paste:=xlPasteValues
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
     
     'Close and save the workbook you copied from
    wb2.Save
    wb2.Close
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Carim.