0

I’ve created a leave diary for my teams, but I want to create a button to allow to add new members. I have a template row stored on one sheet titled code on row 4 that I want to copy and insert in the next available line whilst keeping a break between the next line with data in on another sheet. Ie line 1:5 staff 6 is blank 7 is other info so when it inserts the new line with the data it will insert it into line 6 then create a blank row for line 7 and move all other rows down one. I want to keep the formulas and conditional format that has been coded into the template line I am copying. I keep getting an error on the first line of code? 

ThisWorkbook.sheets(“code”).row(4).copy
ThisWorkbook.sheets(“sheet3”).range(“A1”).end(xlDown).offset(1,0).entirerow.insert
Plutian
  • 2,276
  • 3
  • 14
  • 23
Guyver
  • 3
  • 2

1 Answers1

1

First of all, you should use proper " quotes instead of “, as they cannot be interpreted by VBA. Secondly, .Row( should be .Rows(. Thirdly I highly recommend reading this as right now even if your code works it might have unexpected results.

Sub functioncaller()
ThisWorkbook.Sheets("Sheet1").Rows(4).Copy
ThisWorkbook.Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow.Insert
End Sub
Plutian
  • 2,276
  • 3
  • 14
  • 23
  • Thank you for your quick response, i typed the code out on my phone hence the quotation marks. i hadn't noticed i was missing the "S" from Rows. i ran the code but it didn't insert the row to where i wanted, i had to set the offset to 18 but then every time i ran the code it always inserted the row in the same place. my issue is that none of the conditional formatting was copied and the formulas only worked if i inserted the row in the middle instead of the the end. does that make sense, i will try to create a screen shot – Guyver Nov 28 '19 at 16:06
  • I only modified the second part as I assumed you wanted to insert the row at the end (as most people do). If this doesn't work for you, feel free to change it back to your original version if that worked, as there is nothing wrong with the actual code. – Plutian Nov 28 '19 at 16:13
  • no your code worked perfect in the sense it went to the last line, but im trying to figure out how to upload a screen shot to show you. i have calculations on the bottom rows for everything above and between them a blank row, so where i insert the new it will be a few above the calculators but i want it to copy the conditional formatting as well so all the colours are correct and the calcs update with the newest line of data, sorry if that doesn't make much sense, im new to this site and im unsure how to upload a photo – Guyver Nov 28 '19 at 16:44
  • I recommend starting a new question with your updated issue, as there are plenty of people here who will be able to help you possibly sooner than I can (I will be unavailable until after the weekend). Also most commonly we upload our images to imgur, and link to them in our post. Hope this helps. – Plutian Nov 28 '19 at 17:38
  • Thank you for your help – Guyver Nov 28 '19 at 17:46