how to resolve vba code with copy from another file

Question

In one file I have data (Zeszyt.xlsm - Sheet1) and in the other an empty file (Sheet2) with the same headers and fill in button. However, when I press the button. There is no mistake but nothing complements. Could you help me ?

Private Sub CommandButton2_Click()

Dim wb As Workbook
ThisWorkbook.Worksheets("Sheet1").Rows(12).Copy
Selection.Copy

Set wb = Workbooks.Open("C:\Users\admin\Desktop\TEST\Zeszyt2.xlsm")
wb.Worksheets("Sheet2").Activate

lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow + 1, 1).Select

ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close savehanges = True

Set wb = Nothing

ThisWorkbook.Worksheets("Sheet1").Activate
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select

Application.CutCopyMode = False

End Sub

In general you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. Try: `ThisWorkbook.Worksheets("input_4").Rows(12).Copy` — cybernetic.nomad, Sep 14 '21 at 18:55

Ike · Answer 1 · 2021-09-14T19:46:41.813

There is no need to select or copy/paste.

First of all I would propose to put all parameters like workbook names etc. as constants to the header of the module. By that it is much easier to fix renamings etc.

By having a generic copyRangeValues-routine you can re-use this sub for other copy-actions as well:

Option Explicit

'config source
Private Const wsSourceName As String = "Sheet1"
Private Const rowToCopy As Long = 12    'is this really always row 12????

Private Const wbTargetName As String = "C:\Users\admin\Desktop\TEST\Zeszyt2.xlsm"
Private Const wsTargetName As String = "Sheet2"



Private Sub CommandButton2_Click()

'First step: prepare your source range
Dim wbSource As Workbook
Set wbSource = ThisWorkbook

Dim wsSource As Worksheet
Set wsSource = wbSource.Worksheets(wsSourceName)

Dim rgSource As Range
Set rgSource = wsSource.Rows(rowToCopy)


'second step: prepare your top left target cell
Dim wbTarget As Workbook
Set wbTarget = Workbooks.Open(wbTargetName)

Dim wsTarget As Worksheet
Set wsTarget = wbTarget.Worksheets(wsTargetName)

Dim lastRow As Long
lastRow = wsTarget.UsedRange.Rows.Count

Dim rgTargetCell As Range
Set rgTargetCell = wsTarget.Cells(lastRow + 1, 1)


'third step: copy range - use generic routine
copyRangeValues rgSource, rgTargetCell

'fourth step: close target workbook
wbTarget.Close saveChanges:=True

End Sub


'Put this in a general module
Public Sub copyRangeValues(rgSource As Range, rgTargetCell As Range)
'generic routine to copy one range to another
'rgTargetCell = top left corner of target range

Dim rgTarget As Range
'resize rgTarget according to dimensions of rgSource
With rgSource
    Set rgTarget = rgTargetCell.Resize(.Rows.Count, .Columns.Count)
End With


'write values from rgSource to rgTarget - no copy/paste necessary!!!
'formats are not copied - only values
rgTarget.Value = rgSource.Value

End Sub

Thanks a lot, could you tell me what i have to put befor option explicit. I tried run your code but i got "cant execute code in break Mode" for this private Const rowToCopy here no its started below headers lika Row 2. Not 12. My fault — Przemek Dabek, Sep 14 '21 at 19:39
Ah - sorry: I changed the first subs name to CommandButton2_Click. So you have to copy that code to your current sub, copy the const values below option explicit to the top of the worksheets module where CommandButton2_Click resides. And copy copyRangeValues-Routine to a module — Ike, Sep 14 '21 at 19:49
Ohh ok! To be sure, your code was edited. So i can copy and test. And the last question. If from zeszyt i would like copy all position from A:D row1 should i change here Private Const rowToCopy As Long to 1? As from first Row? — Przemek Dabek, Sep 14 '21 at 20:07
private const rowToCopy is just an example - you can change it to any value. On the other hand you can adjust rgSource to your needs based on other criteria — Ike, Sep 14 '21 at 20:25

score 0 · Answer 2 · answered Sep 15 '21 at 08:52

Copy Row To Another File

The code will run slower if you use Activate and Select. but not if you use variables.

Option Explicit

Private Sub CommandButton2_Click()
 
    Const swsName As String = "Sheet1"
    Const sRow As Long = 12
    
    Const dFilePath As String _
        = "C:\Users\admin\Desktop\TEST\Zeszyt2.xlsm"
    Const dwsName As String = "Sheet2"
    
    Dim swb As Workbook: Set swb = ThisWorkbook
    Dim sws As Worksheet: Set sws = swb.Worksheets(swsName)
    Dim srg As Range: Set srg = sws.Rows(sRow)
    
    Application.ScreenUpdating = False
    
    Dim dwb As Workbook: Set dwb = Workbooks.Open(dFilePath)
    Dim dws As Worksheet: Set dws = dwb.Worksheets(dwsName)
    Dim dCell As Range
    Set dCell = dws.Cells(dws.Rows.Count, "A").End(xlUp).Offset(1)
    
    srg.Copy Destination:=dCell
    
    dwb.Close SaveChanges:=True
 
    Application.ScreenUpdating = True
 
    MsgBox "Done.", vbInformation, "Append Row"

End Sub

The macro works, but does not save anything, leaves the fields blank. From the file he wants to take the data from A2 to H2 down to the end. Does it matter that my table does not contain all the columns as in the source file? For example, between the price mob column I have price freq mob — Przemek Dabek, Sep 15 '21 at 10:14

how to resolve vba code with copy from another file

2 Answers2

Copy Row To Another File

Linked