Vba to transfer specific data from one work book to another

Hardhat

New Member
Joined
Jul 28, 2017
Messages
23
I am struggling with a small pit of code. I am placing a button on a worksheet, that when used it will transfer specific data from the workbook it is in to a secondary workbook if a condition is met. I have done this before with no difficulty and I have compared my code to workbooks that work fine. The condition statement works fine, Opening the secondary workbook works fine,

But the data transfer give me Run time error '438' Object does not support this property or method. I need some fresh eyes on this. any and all help will be greatly appreciated!!!! I will be adding a loop to the if statement so looks for any empty cell in L and reports the corresponding row information to the secondary form.
Code:
'Sub CopyToViewingWB()

If Sheet1.Range("L3") = "" Then
Workbooks.Open Filename:="M:\HSS Shared Services\Shared Services Team\Chuck T HSS\Recruiting\Req_veiwing_Form.xlsx" 'Location of workbooK
    Application.Visible = True 'change to hide Workbook while data transfers
  
    Workbooks("Req_veiwing_Form.xlsx").Sheet1.Range("A2").Value = Sheet1.Range("A3").Value
    Workbooks("Req_veiwing_Form.xlsx").Sheet1.Range("B2").Value = Sheet1.Range("B3").Value
    Workbooks("Req_veiwing_Form.xlsx").Sheet1.Range("C2").Value = Sheet1.Range("C3").Value
    Workbooks("Req_veiwing_Form.xlsx").Sheet1.Range("D2").Value = Sheet1.Range("D3").Value
    Workbooks("Req_veiwing_Form.xlsx").Sheet1.Range("E2").Value = Sheet1.Range("E3").Value
    Workbooks("Req_veiwing_Form.xlsx").Sheet1.Range("F2").Value = Sheet1.Range("F3").Value
    Workbooks("Req_veiwing_Form.xlsx").Sheet1.Range("G2").Value = Sheet1.Range("G3").Value
    Workbooks("Req_veiwing_Form.xlsx").Sheet1.Range("H2").Value = Sheet1.Range("H3").Value
    Workbooks("Req_veiwing_Form.xlsx").Sheet1.Range("I2").Value = Sheet1.Range("I3").Value
    Workbooks("Req_veiwing_Form.xlsx").Sheet1.Range("J2").Value = Sheet1.Range("J3").Value
    Workbooks("Req_veiwing_Form.xlsx").Sheet1.Range("K2").Value = Sheet1.Range("K3").Value
    
End If




End Sub
 
Last edited:

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
You cannot use sheet codenames in that manner on another workbook, you need to change it to use the sheet name, you can also do all the copying in one go
Code:
Workbooks.Open Filename:="M:\HSS Shared Services\Shared Services Team\Chuck T HSS\Recruiting\Req_veiwing_Form.xlsx" 'Location of workbooK
    Application.Visible = True 'change to hide Workbook while data transfers
  
    Workbooks("Req_veiwing_Form.xlsx").Sheets("sheet1").Range("A2:K2").Value = Sheet1.Range("A3:K3").Value
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,228
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top