I am trying to write a macro that loops through a sort table, creates a copy of template for each financial year it finds then copies and pastes the data from the sort table into the appropriate cells on the corresponding worksheet.
I have the Sub where it loops through the data and creates a Copy of the worksheet for each financial year. My previous problem with that can be found at: VBA nested For Loop to copy and rename a worksheet based on a variable; not working
Now I a new problem, I have written the bit that loops through the Charge Types and returns the Variables I need in order to paste it to the correct Worksheet: Financial Year (Which is what I have named the worksheets), Financial Month which dictates the destination Row and Charge Type which decides the destination column and Cash which is the value I want to copy and paste.
The problem is I keep getting a Runtime 1004 Error: Application-defined or Object Defined Error.
I have tried various different methods:
Range(X).Value = Range (Y).Value
.Copy Destination:= X
and .PasteSpecial Paste:=xlPasteValues
All yield the same result. I suspect the problem is the way I'm declaring or setting the destination worksheet. But I can't see what I'm getting wrong.
Option Explicit
Public Function FY(Fdate As String) As String 'This function extract the Financial Year from a date in ##.##.#### format
Dim arDMY As Variant
arDMY = Split(Fdate, ".")
If arDMY(1) >= 5 Then
FY = CStr(arDMY(2) + 1)
Else
FY = CStr(arDMY(2))
End If
End Function
Public Function FM(Fdate As String) As String 'This function extracts the Financial Month from a date in ##.##.#### format
Dim arDMY As Variant
Dim TM As String
arDMY = Split(Fdate, ".")
If arDMY(1) >= 5 Then
TM = CStr(arDMY(1) - 4)
Else
TM = CStr(arDMY(1) + 8)
End If
If arDMY(0) >= 5 Then
FM = TM
Else
FM = TM - 1
End If
End Function
Sub Move_stuff()
Dim cell As Range
Dim Charge As Range
Dim Sort_Table As Worksheet
Dim C As Variant
Dim D As Variant
Dim Targ As Range
Dim Ddate As Range
Dim Charge_Amount As Range
Dim Targdate As String
Dim Cash As Double
Dim TM As Variant
Dim Pos As Variant
Dim Sheet_go As String
Dim Year As Variant
Dim Month As Variant
Set Charge = Range("Sorttable[Charge Type]")
Dim CType As Object
Set CType = CreateObject("Scripting.Dictionary")
CType.Add "Fos", 2
CType.Add "Foo", 9
CType.Add "Bar", "Cash"
'real dictionary contains various types that returns the number of the column I want the data pasted in.
Sheets("Sort_Table").Select 'select worksheet with data in sort table
For Each cell In Charge 'loop to find charge type
If Not IsEmpty(cell) Then
C = cell.Value
D = CType(C)
Set Targ = cell.Offset(, 3)
Set Ddate = cell.Offset(, 2)
Set Charge_Amount = cell.Offset(, 4)
Targdate = Targ.Value
EDP = Ddate.Value
Cash = Charge_Amount.Value
Pos = 16 - FM(Targdate)
'this gives me a row where I want data to be copied to. So with R1C1 referencing the range I want to copy to is .Range(Pos, D)
Sheet_go = FY(Targdate) ' This gives me the year which is what my destination worksheets have been named.
If D = "Cash" Then
MsgBox Cash & vbCrLf & EDP & vbCrLf & cell.Row
Else
MsgBox FY(Targdate) & vbCrLf & Pos & ", " & D & vbCrLf & Cash 'checks all variables are correct
cell.Offset(, 4).Copy 'this only works if I don't reference the Worksheetin the range.
Sheet_go.Select 'selects the destination table and this works as I can see the change in tab.
Worksheets(Sheet_go).Range(Pos, D).PasteSpecial xlPasteValues
'this doesn't work returns Runtime Error 1004: Application defined or Object defined error.
'Also the xlPasteValues are wrong and don't match the value in cell.Offset(, 4)
Worksheets("Sort_Table").Select
End If
End If
Next cell
End Sub
Range requires "A1" formula or two Cells(x,y) value.
If you use instead Cells(Pos,D) it works but D must have a valid identifier (edited).
If CType(C) returns an Empty value (no match) then the line fails.