Hi there,
I have written this script to update a spreadsheet from another spreadsheet, but I think the code is a little messy and could be tidied up if I was able to use the variables the way I indended.
Sub Upload()
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Documents\DestinationSheet.xlsx"
Dim rSource As Range
Dim rDestination As Range
Windows("Timesheet.xlsm").Activate
Sheets("Monthly_Totals").Activate
Set rSource = Range("B11:N18")
rSource.Select
rSource.Copy
Windows("DestinationSheet.xlsx").Activate
Sheets("P15").Activate
Set rDestination = Range("B31")
rDestination.Select
rDestination.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Windows("DestinationSheet.xlsx").Activate
ActiveWindow.Close
Application.ScreenUpdating = True
ActiveWindow.WindowState = xlMaximized
Worksheets(1).Activate
Range("A1").Select
Application.CutCopyMode = False
valKill:
Set rSource = Nothing
Set rDestination = Nothing
Exit Sub
End Sub
Basically, I would ideally like to use the variable of rSource and rDestination to refer to the specific ranges on a specific sheet on another workbook. Is there a better way to do this please.
Kind regards
Rob
Bookmarks