Create a dynamic start and end in range to paste into

liznic

New Member
Joined
Mar 4, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top