open file and update data?

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have to worksheets that have the same layout in two different files... I
want to open one to load the data into the current workbook... I get errors
on twbk = thisworkbook

Sub loadTickerFile()
Dim twbk As Workbook
twbk = ThisWorkbook
If Worksheets("Long Term Ticket").Range("D2").Value = "" Then
MsgBox "Please enter ticker in Range D2 and re-run macro."
GoTo ender:
End If
User = Environ("UserProfile")
file = User & "\" & "My Documents\Trade Tickets\Long Term Ticket " &
Worksheets("Long Term Ticket").Range("D2").Value
Workbooks.Open file
Dim wbk As Workbook
wbk = ActiveWorkbook
twbk.Sheets("Long Term Ticket").Range("e4:e14") = wbk.Sheets("Long Term
Ticket").Range("e4:e14")

twbk.Sheets("Long Term Ticket").Range("D20:D22") = wbk.Sheets("Long Term
Ticket").Range("D20:D22")

twbk.Sheets("Long Term Ticket").Range("D28:D37") = wbk.Sheets("Long Term
Ticket").Range("D28:D37")

twbk.Sheets("Long Term Ticket").Range("D40:D50") = wbk.Sheets("Long Term
Ticket").Range("D40:D50")
twbk.Sheets("Long Term Ticket").Range("h4:h50") = wbk.Sheets("Long Term
Ticket").Range("h4:h50")
wbk.Close savechanges:=False

ender:
Range("A2").Select
End Sub
 
John, just to add on it would be helpful if you review the help on Set
Statement. The Dim, Private, Public, ReDim, and Static statements only
declare a variable that refers to an object. No actual object is referred to
until you use the Set statement to assign a specific object.

If this post helps click Yes
 
Hi John

Have revised your code using worksheet and workbook object. Try and feedback

Sub loadTickerFile()

Dim ws1 As Worksheet, ws2 As Worksheet, wbk As Workbook
Dim strTicker As String, strFile As String, strProfile As String

Set ws1 = ThisWorkbook.Worksheets("Long Term Ticket")
strTicker = Trim(ws1.Range("D2"))

If strTicker = "" Then _
MsgBox "Please enter ticker in Range D2 and re-run macro.": Exit Sub

strProfile = Environ("UserProfile")
strFile = strProfile & "\" & _
"My Documents\Trade Tickets\Long Term Ticket " & strTicker

Set wbk = Workbooks.Open(strFile)
Set ws2 = wbk.Worksheets("Long Term Ticket")

ws1.Range("e4:e14") = ws2.Range("e4:e14").Value
ws1.Range("D20:D22") = ws2.Range("D20:D22").Value
ws1.Range("D28:D37") = ws2.Range("D28:D37").Value
ws1.Range("D40:D50") = ws2.Range("D40:D50").Value
ws1.Range("h4:h50") = ws2.Range("h4:h50").Value

Application.DisplayAlerts = False
wbk.Close Savechanges:=False
Application.DisplayAlerts = True
End Sub


If this post helps click Yes
 
Back
Top