0

I created a workbook that has a template and register.
The workbook creates a request for spending and saves it as a sheet in a new macro enabled workbook.
It then saves the details on the register tab notifies relevant staff by email.
Authorisation is then recorded in the individual files.

I would like to 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.

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 get

run-time error 1004

with the debugger suggesting that Set VariableRange = sht2.Range("H" & PasteRow) is the cause.

Community
  • 1
  • 1
Jolene
  • 11
  • 5

2 Answers2

1

You could replace the last 4 lines with this one

VariableRange.Value2 = rng1.Value2
Spectral Instance
  • 1,971
  • 2
  • 3
  • 6
  • It falls over before that, sorry I should have put that in the question, might have helped! I am getting Run-time error 1004 with the debugger indicating that the issue is with `Set VariableRange = sht2.Range("H" & PasteRow)` I will amend the question, that's what I get for posting just before lunch! – Jolene Apr 25 '22 at 12:38
  • Using the Locals Window, what is the value of PasteRow when the error occurs? (also since PasteRow is going to be a number (I imagine) maybe you should declare it as a Long, rather than a String) – Spectral Instance Apr 25 '22 at 12:40
1

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
Jolene
  • 11
  • 5
  • Try to [avoid `Activate` and `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/10717999#10717999) – T.M. Apr 25 '22 at 17:58
  • @T.M. What would be your alternatives? Always happy to learn! I think I only used Activate. – Jolene Apr 27 '22 at 05:21
  • Having fully qualified your range references, what's the sense to activate sht1 only to make it visible for a moment and to make it disappear when activating sht2? - See a related topic [Copy filtered fields from one sheet to another without activating](https://stackoverflow.com/questions/64820921/copy-filtered-fields-from-one-sheet-to-another-without-activating/64822016#64822016); – T.M. Apr 27 '22 at 08:37