vba to copy data from open workbook to a closed workbook

[Deepas]

hello i fairly new to VBA, request your help for the following.

i need to copy data from cells "m99 to m115" located in sheet1 of "stocks data" excel file and transpose it to the last empty row in sheet1 of "application list" excel file (closed workbook).

 

[Deepas]

hello i fairly new to VBA, request your help for the following.

i need to copy data from cells "m99 to m115" located in sheet1 of "stocks data" excel file and transpose it to the last empty row in sheet1 of "application list" excel file (closed workbook).

can some one help me with code for the same? Thankyou in advance

 

[Joe4]

Try this. Just change the file path of your workbook to match your directory layout:

Sub MyCopy()

    Dim srcWB As Workbook
    Dim dstWB As Workbook
    Dim nr As Long
   
    Application.ScreenUpdating = False
   
'   Capture current workbook as source workbook
    Set srcWB = ActiveWorkbook
   
'   Open other file
    Workbooks.Open "C:\Temp\Application List.xlsx"
   
'   Capture destination workbook
    Set dstWB = ActiveWorkbook
   
'   Find first available row
    nr = dstWB.Sheets("Sheet1").Cells(rows.Count, "A").End(xlUp).Row + 1
   
'   Copy data from source workbook
    srcWB.Activate
    Sheets("Stocks Data").Activate
    Range("M99:M115").Copy
    dstWB.Activate
    Sheets("Sheet1").Activate
    Range("A" & nr).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
   
'   Save workbook & close
    dstWB.Save
    dstWB.Close
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"
   
End Sub

 

[Deepas]

Try this. Just change the file path of your workbook to match your directory layout:

Sub MyCopy()

    Dim srcWB As Workbook
    Dim dstWB As Workbook
    Dim nr As Long
  
    Application.ScreenUpdating = False
  
'   Capture current workbook as source workbook
    Set srcWB = ActiveWorkbook
  
'   Open other file
    Workbooks.Open "C:\Temp\Application List.xlsx"
  
'   Capture destination workbook
    Set dstWB = ActiveWorkbook
  
'   Find first available row
    nr = dstWB.Sheets("Sheet1").Cells(rows.Count, "A").End(xlUp).Row + 1
  
'   Copy data from source workbook
    srcWB.Activate
    Sheets("Stocks Data").Activate
    Range("M99:M115").Copy
    dstWB.Activate
    Sheets("Sheet1").Activate
    Range("A" & nr).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
  
'   Save workbook & close
    dstWB.Save
    dstWB.Close
  
    Application.ScreenUpdating = True
  
    MsgBox "Macro complete!"
  
End Sub

Thankyou so much, but i have a dialog box that opens up, which asks if i need to update the external links. I need to manually click Update, and then continue.. how can we tune the macro to automatically select Update option when the dialog box opens up.

 

[Joe4]

Try this. Just change the file path of your workbook to match your directory layout:

Thankyou so much, but i have a dialog box that opens up, which asks if i need to update the external links. I need to manually click Update, and then continue.. how can we tune the macro to automatically select Update option when the dialog box opens up.

That is a whole different question that should be posted to a new thread.