Paste to specified row in another workbook-CodePudding

I have created a workbook that has a template and register, the workbook creates a request for spend which saves this request as just this sheet in a new workbook (macro enabled of course), and then saves the details on the register tab notifies relevant staff by email, the authorisation is then recorded in the individual files - so far so good as far as my vba skills go! But what I would like it to do is to also add the authorisation to the register in specific locations, but row will be dependent on the request number (found in cell C8 in sht1), although the column will always be the same.

I have tried this but I know it is utter garbage!

Dim wb1 As Workbook
Dim sht1 As Worksheet
Dim rng1 As Range
Dim wb2 As Workbook
Dim sht2 As Worksheet
Dim PasteRow As String
Dim VariableRange As Range
Set wb1 = ThisWorkbook
Set sht1 = wb1.Worksheets("Sheet1")
Set wb2 = Workbooks.Open("C:\\mytemplate.xlsm") (obviously this is a full file path in my actual macro)
Set rng1 = sht1.Range("D19")
Set sht2 = wb2.Worksheets("CAPEX list")
PasteRow = sht1.Range("C8").Value
Set VariableRange = sht2.Range("H" & PasteRow)
sht1.Activate
rng1.Copy
sht2.Activate
VariableRange.PasteSpecial Paste:=xlPasteValues

I am getting a run-time error 1004 with the debugger suggesting that Set VariableRange = sht2.Range("H" & PasteRow) is the cause of the problem.

any help very gratefully recieved this one driving me crazy!

CodePudding user response：

You could replace the last 4 lines with this one

VariableRange.Value2 = rng1.Value2

CodePudding user response：

I don't know why I didn't think of using offset sooner! In case someone else ever has the same issue (not massively likely but you never know!) this is the code that I have now written and it seems to be working perfectly!

Dim wb1 As Workbook
Dim sht1 As Worksheet
Dim rng1 As Range
Dim wb2 As Workbook
Dim sht2 As Worksheet
Dim MyRow As Long
Dim PasteRange As Range
Set wb1 = ThisWorkbook
Set sht1 = wb1.Worksheets("Sheet1")
Set wb2 = Workbooks.Open("myfile.xlsm")
Set rng1 = sht1.Range("D19:E19")
Set sht2 = wb2.Worksheets("CAPEX list")
MyRow = sht1.Range("C9").Value
Set PasteRange = sht2.Range("H2").Offset(MyRow, 0)
sht1.Activate
rng1.Copy
sht2.Activate
PasteRange.PasteSpecial Paste:=xlPasteValues
wb2.Save
wb1.Save