Syntax Help Defining range when row is variable

[SueKi]

Hi,

I wrote the below code to copy results from a worksheet where I model data, to one for storing the results. It works where I'm copying an individual cell to an individual cell, but when I try to copy of a range of data to another range, I can't find a syntax on the target range that works. The below effort gives me an error, looking for a list separator or ) where I have a colon

Sub CollectData()
'
'

'
    
Dim greige As String
Dim TRow As Long
 
'Hold greige number to find where to paste
greige = Worksheets("Model").Range("B3").Value
   
'Find what row the greige is on in "Summary Data Run Rates" for pasting
TRow = Application.WorksheetFunction.Match(greige, Worksheets("Summary Data Run Rates").Range("c1:c200"), 0)

'Make sure you dind't forget to add type and notes

  Dim answer As Integer
  answer = MsgBox("Did you add item type and notes?", vbQuestion + vbYesNo)
 
  If answer = vbNo Then Exit Sub

'Copy / Paste to bring results to Summary tab

Worksheets("Summary Data Run Rates").Range("BU" & TRow) = Worksheets("Model").Range("H9").Value 'No Std Dev used
Worksheets("Summary Data Run Rates").Range("BV" & TRow) = Worksheets("Model").Range("E10").Value 'Steady Running Looms
Worksheets("Summary Data Run Rates").Range("BW" & TRow) = Worksheets("Model").Range("E11").Value 'Add Flex Looms
Worksheets("Summary Data Run Rates").Range("BX" & TRow) = Worksheets("Model").Range("H10").Value 'Added Weeks Demand
Worksheets("Summary Data Run Rates").Range("BY" & TRow) = Worksheets("Model").Range("H8").Value 'Max Inventory
Worksheets("Summary Data Run Rates").Range("BZ" & TRow) = Worksheets("Model").Range("N7").Value 'Weeks not served
Worksheets("Summary Data Run Rates").Range("CA" & TRow) = Worksheets("Model").Range("N8").Value 'Weeks OT
Worksheets("Summary Data Run Rates").Range("CB" & TRow) = Worksheets("Model").Range("N9").Value 'Shut Down Weeks
Worksheets("Summary Data Run Rates").Range("Cc" & TRow) = Worksheets("Model").Range("N10").Value '#Weeks Flex Looms On
Worksheets("Summary Data Run Rates").Range("CD" & TRow) = Worksheets("Model").Range("N11").Value 'Max Pallets
Worksheets("Summary Data Run Rates").Range("CE" & TRow) = Worksheets("Model").Range("N12").Value 'Average Pallets
Worksheets("Summary Data Run Rates").Range("CF" & TRow) = Worksheets("Model").Range("J1").Value 'Item Type
Worksheets("Summary Data Run Rates").Range("CG" & TRow) = Worksheets("Model").Range("J2").Value 'Note

Application.CutCopyMode = False


'Part 2

Dim TRow2 As Long

'Find what row the greige is on in "Summary Data Run Rates" for pasting
TRow2 = Application.WorksheetFunction.Match(greige, Worksheets("Model Inventory Results").Range("a1:a200"), 0)

Worksheets("Summary Data Run Rates").Range("B" & TRow2:"BA" & TRow2) = Worksheets("Model").Range("D22:BC22").Value     'Need proper syntax on Range("B" & TRow2:"BA" & TRow2)

End Sub

 

[Joe4]

Change this:

Range("B" & TRow2:"BA" & TRow2)

to this:

Range("B" & TRow2 & ":BA" & TRow2)

(note how the "literal" parts are between quotes and the variables are not).

 

[SueKi]

Thanks, Joe4! That works perfectly and I really appreciate the explanation as well. My company has an IT department that thinks we should run the business by exporting out of the expensive ERP system and manipulating in Excel everyday, so I see a lot of VBA in my future and am trying to learn. Thanks!

 

[Joe4]

You are welcome.
Glad I was able to help.