R
ryguy7272
Upload Data from Sheet1 on Local Machine to Sheet1 on LAN
I am trying to modify the Ron De Bruin code that I found here:
http://www.rondebruin.nl/copy7.htm
Basically, I am trying to upload data from a range ("A1:B4") in Sheet1 on my
computer to a file saved on a LAN. I am trying to use the code below, but it
needs to be modified to SEND the data to the file on the LAN, not RECEIVE the
data from the file on the LAN.
I am guessing that the Sub GetRange is ordered incorrectly, but I can’t
figure out how to straighten it out. If someone sees the error please let
me know. Also, let me know if you need any clarification on anything.
Sub File_In_Network_Folder()
Application.ScreenUpdating = False
On Error Resume Next
'Call the macro GetRange
GetRange Sheets("Sheet1").Range("A1"), _
"\\fsrv3\public\Sales Operations\Ryan", _
"Destination.xls", _
"Sheet1", _
Range("A1:B4")
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
Sub GetRange(SourceRange As String, _
FilePath As String, _
FileName As String, _
SheetName As String, _
DestRange As Range)
Dim Start
'Go to the destination range
Application.Goto DestRange
'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)
'Add formula links to the closed file
With DestRange
..FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange
'Make values from the formulas
..Copy
..PasteSpecial xlPasteValues
..Cells(1).Select
Application.CutCopyMode = False
End With
End Sub
Regards,
Ryan---
I am trying to modify the Ron De Bruin code that I found here:
http://www.rondebruin.nl/copy7.htm
Basically, I am trying to upload data from a range ("A1:B4") in Sheet1 on my
computer to a file saved on a LAN. I am trying to use the code below, but it
needs to be modified to SEND the data to the file on the LAN, not RECEIVE the
data from the file on the LAN.
I am guessing that the Sub GetRange is ordered incorrectly, but I can’t
figure out how to straighten it out. If someone sees the error please let
me know. Also, let me know if you need any clarification on anything.
Sub File_In_Network_Folder()
Application.ScreenUpdating = False
On Error Resume Next
'Call the macro GetRange
GetRange Sheets("Sheet1").Range("A1"), _
"\\fsrv3\public\Sales Operations\Ryan", _
"Destination.xls", _
"Sheet1", _
Range("A1:B4")
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
Sub GetRange(SourceRange As String, _
FilePath As String, _
FileName As String, _
SheetName As String, _
DestRange As Range)
Dim Start
'Go to the destination range
Application.Goto DestRange
'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)
'Add formula links to the closed file
With DestRange
..FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange
'Make values from the formulas
..Copy
..PasteSpecial xlPasteValues
..Cells(1).Select
Application.CutCopyMode = False
End With
End Sub
Regards,
Ryan---