Copy Data from a column on a sheet and paste to a specific spot on another sheet

[Cranman2127]

Hello,

I'm trying to figure out how to copy the data in column I (9) from one worksheet 'fitment' starting in row 2. And pasting any data found up until the first blank in column I (9) into cell A30 on a worksheet called 'Sheet1'.

The data starts on "Fitment" as a formula, the first block of code is to run the forumula and copy the values into column I (9).

'Define Worksheets
Dim sh1 As Worksheet
Set sh1 = Worksheets("Sheet1")
Dim ft As Worksheet
Set ft = Worksheets("Fitment")




'Run Concat Formula
ft.Select
Columns(7).Select
Selection.value = Selection.FormulaR1C1

'Copy Concat Value and Paste
ft.Select
Columns(7).Select
Selection.Copy
ft.Select
Columns(9).Select
Selection.PasteSpecial Paste:=xlPasteValues

Any Help would be greatly appreciated.
Thank you!

 

[Cranman2127]

It looks to only be copying cell G2 to I2 none of the other values from col G are being copied over.

 

[Fluff]

What was the value of UsdRws?

 

[Cranman2127]

I'm sorry but i'm not sure how to figure that out.

 

[Fluff]

If you add the message box as shown, what does it say?

With Sheets("fitment")
   UsdRws = .Range("G" & Rows.Count).End(xlUp).Row
   [COLOR=#0000ff]MsgBox UsdRws[/COLOR]
   .Range("I2:I" & UsdRws).Value = .Range("G2:G" & UsdRws).Value
   Sheets("Sheet1").Range("A30").Resize(UsdRws - 1).Value = .Range("I2:I" & UsdRws).Value
End With

 

[Cranman2127]

Message box shows a value of 1

 

[Fluff]

In that case there is no data below row 1 in col G on the Fitment sheet.