I'm fairly new to VBA.
I have a piece of code which copied all rows that have numeric data in Column K from one spreadsheet to another. This will be run on multiple spreadsheets, which have difference exercise names and job numbers.
The exercise as job number only appear in one cell on the original sheet, hene why this is in a seperate line of code to the If statement checking if the values are numerical.
I have the rows with numbers go in first. Then I add the exercise and job number into columns S and R. My initial code works fine except the range is given as .range("R2:2" & lastrow).Value so while it does copy in what I want it to copy it always starts pasting from R2, so all the rows that were Exercise 1 are now Exercise 2. This will happen every time I copy anything in.
I then tried defining firstrow and lastrow, but I get error1004.
This is my original code which overwrites:
'Import job number and exercise
Dim jobnumber As String
Dim exercise As String
jobnumber = ActiveSheet.Range("S3").Value
exercise = ActiveSheet.Range("B4").Value
lastrow = Workbooks("GQE Asana Import").Sheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row
Workbooks("GQE Asana Import").Sheets("Sheet1").Range("R2:R" & lastrow).Value = jobnumber
Workbooks("GQE Asana Import").Sheets("Sheet1").Range("S2:S" & lastrow).Value = exercise
This is the code where I tried to make the start range i.e. "R2:R" dynamic which gives the 1004 error
'Import job number and exercise
Dim jobnumber As String
Dim exercise As String
Dim firstrowjn As Integer
Dim firstrowex As Integer
jobnumber = ActiveSheet.Range("S3").Value
exercise = ActiveSheet.Range("B4").Value
lastrow = Workbooks("GQE Asana Import").Sheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row
firstrowjn = Workbooks("GQE Asana Import").Sheets("Sheet1").Cells(Rows.Count, "R").End(xlUp).Row + 1
firstrowex = Workbooks("GQE Asana Import").Sheets("Sheet1").Cells(Rows.Count, "S").End(xlUp).Row + 1
Workbooks("GQE Asana Import").Sheets("Sheet1").Range(firstrowjn & lastrow).Value = jobnumber
Workbooks("GQE Asana Import").Sheets("Sheet1").Range(firstrowex & lastrow).Value = exercise
I've stepped through it and firstrowjn etc all pick up the right row numbers. It just seems to break at the point of adding the cell value.
I have tried using ranges at the set keyword instead of integers, I have tried cells() instead of range() but while this doesn't error it also doesn't change the values. I have tried Range(Cells...) but it doesn't like it being defined that way either. I tried a for each loop but that takes considerably longer to complete and again I can't get round the issue of defining my ranges dynamically.
How do I make the starting range - "R2:R" dynamic like the last row is?
Notes: last row is set further back in the code, and is an integer. I use column K because it has the first lot of data in so that is how many time the job no/exercise need to be in Columns R and S. The macro will be run out of each sheet with the data as the number will vary each month, which is why I use activesheet.
I think that covers everything. Thanks in advance for your help!
I have a piece of code which copied all rows that have numeric data in Column K from one spreadsheet to another. This will be run on multiple spreadsheets, which have difference exercise names and job numbers.
The exercise as job number only appear in one cell on the original sheet, hene why this is in a seperate line of code to the If statement checking if the values are numerical.
I have the rows with numbers go in first. Then I add the exercise and job number into columns S and R. My initial code works fine except the range is given as .range("R2:2" & lastrow).Value so while it does copy in what I want it to copy it always starts pasting from R2, so all the rows that were Exercise 1 are now Exercise 2. This will happen every time I copy anything in.
I then tried defining firstrow and lastrow, but I get error1004.
This is my original code which overwrites:
'Import job number and exercise
Dim jobnumber As String
Dim exercise As String
jobnumber = ActiveSheet.Range("S3").Value
exercise = ActiveSheet.Range("B4").Value
lastrow = Workbooks("GQE Asana Import").Sheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row
Workbooks("GQE Asana Import").Sheets("Sheet1").Range("R2:R" & lastrow).Value = jobnumber
Workbooks("GQE Asana Import").Sheets("Sheet1").Range("S2:S" & lastrow).Value = exercise
This is the code where I tried to make the start range i.e. "R2:R" dynamic which gives the 1004 error
'Import job number and exercise
Dim jobnumber As String
Dim exercise As String
Dim firstrowjn As Integer
Dim firstrowex As Integer
jobnumber = ActiveSheet.Range("S3").Value
exercise = ActiveSheet.Range("B4").Value
lastrow = Workbooks("GQE Asana Import").Sheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row
firstrowjn = Workbooks("GQE Asana Import").Sheets("Sheet1").Cells(Rows.Count, "R").End(xlUp).Row + 1
firstrowex = Workbooks("GQE Asana Import").Sheets("Sheet1").Cells(Rows.Count, "S").End(xlUp).Row + 1
Workbooks("GQE Asana Import").Sheets("Sheet1").Range(firstrowjn & lastrow).Value = jobnumber
Workbooks("GQE Asana Import").Sheets("Sheet1").Range(firstrowex & lastrow).Value = exercise
I've stepped through it and firstrowjn etc all pick up the right row numbers. It just seems to break at the point of adding the cell value.
I have tried using ranges at the set keyword instead of integers, I have tried cells() instead of range() but while this doesn't error it also doesn't change the values. I have tried Range(Cells...) but it doesn't like it being defined that way either. I tried a for each loop but that takes considerably longer to complete and again I can't get round the issue of defining my ranges dynamically.
How do I make the starting range - "R2:R" dynamic like the last row is?
Notes: last row is set further back in the code, and is an integer. I use column K because it has the first lot of data in so that is how many time the job no/exercise need to be in Columns R and S. The macro will be run out of each sheet with the data as the number will vary each month, which is why I use activesheet.
I think that covers everything. Thanks in advance for your help!
