I'm having trouble with something that seems very basic. My experience with vba is essentially nothing. I have a spreadsheet containing data imported from some csv files using a script I made. The script reads in the data then creates a master spreadsheet or overwrites the previous master spreadsheet. I would like to move this data one column over in the same script. The range of the data that needs to be moved changes every time the spreadsheet is updated as more data is added. I have included the following at the end of my code to do this, but I cannot get it to work.
Set myRange = Range(Cells(1, 1).Address(), Cells(lastRow, lastColumn).Address())
Range("myRange").Offset(,1)
What am I doing wrong?
Edit: Here is what I have now. I keep running into 'Run-time error 1004' on the 14th line.
Dim myRange As Range
Dim DefPath As String
Dim lastRow As Long
Dim lastColumn As Long
Dim lRow As Long
Dim lCol As Long
DefPath = Application.DefaultFilePath
Workbooks.Open Filename:=DefPath & "\SEM Master File"
lRow = Cells(Rows.Count, 1).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set myRange = Range(Cells(1, 1), Cells(lastRow, lastColumn))
myRange.Offset(, lastColumn).Value = myRange.Value
Second Edit: Here is what I have so far. It compiles and runs, but instead of moving everything over one column as I expected it to it makes a copy of the columns and puts it in the columns next to the data already in the spreadsheet.
Sub Offset_Data()
Dim myRange As Range
Dim DefPath As String
Dim lastRow As Long
Dim lastColumn As Long
DefPath = Application.DefaultFilePath
Workbooks.Open ("SEM Master File.xlsx")
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Set myRange = Range(Cells(1, 1), Cells(lastRow, lastColumn))
myRange.Offset(, lastColumn).Value = myRange.Value
End Sub
