Have specified copy range using Range("I6", ActiveCell...but get an error when pasting

[Moley84]

Hi all,

I have specified the area that I wish to copy and upon F8 throughout the code, I can see that it is selecting the copy area correctly. I cannot work out how to specify what the paste area should be. The copy area will always change, hence the use of the ActiveCell.Offset feature. I'm at a loss as to how to specify the paste area when the copy area changes each time.

In my code below, the first three rows are working fine but upon the 4th row, I get the following error.

Once the paste has occurred, I would like for row 8 down, to change the entries in the pasted are to be changed to a 1. I don't know how to specify the pasted area for this, either (same area as the line above).

Any help appreciated,
Moley84

sourceSheet.Rows(6).Find(What:="Calculated Activity Cost", LookAt:=xlWhole).Activate
ActiveCell.Offset(0, -1).Activate
sourceSheet.Range("I6", ActiveCell).EntireColumn.SpecialCells(xlCellTypeVisible).Copy

'The above three lines are fine.  The next line is where I get the error

targetSheet.Range("h6" I DON'T KNOW WHAT TO SPECIFY HERE ).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
targetSheet.Range("h8"  I DON'T KNOW WHAT TO SPECIFY HERE).Replace What:="*", Replacement:=1

 

[jolivanes]

Can you explain in a concise manner what you want to achieve.
No need for code that does not do what you want it to do.

 

[Moley84]

Thank you for replying. Within the sourceSheet, I want to copy from I6 to last row and every column to the right of column I until the column before the Calculated Activity Cost entry in row 6. This is then pasted to the target sheet, starting at H6.

Then, once copied, all entries from column H and row 8 down and across said range are changed to the number 1.

I hope I have explained it better. Thank you.

 

[jolivanes]

Try on a copy of your original first and check to make sure it does what you want.
1, 2, 4 & 5: Change "sh1" and "sh2" on both lines to meaningful names
2 & 3: Change "Sheet1" and "Sheet2" To your actual source and target sheet names

Sub Start_Here()
Dim rng As Range, sh1 As Worksheet, sh2 As Worksheet    '<----- Change 1
Set sh1 = Worksheets("Sheet1")    '<----- Change 2
Set sh2 = Worksheets("Sheet2")    '<----- Change 3
Set rng = sh1.Range("I6").Resize(sh1.Cells(sh1.Rows.Count, 9).End(xlUp).Row - 5, sh1.Rows(6).Find("Calculated Activity Cost", , , 1).Column - 9)
sh2.Range("H6").Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value    '<----- Change 4
sh2.Range("H8").Resize(rng.Rows.Count - 2, rng.Columns.Count)..Value = 1
End Sub

 

[Moley84]

Hi jolivanes, thank you for replying and helping me out. It is probably my poor explanation but I don't need a 1 in every field but only where there is an entry. Here is an example of the sourceSheet (your sh1) and what I would like copied across and changed to a 1. [The first line of text, e.g. v-1, v1, v2 are to be copied across and remain as is. It's only from the row below that that need to be changed to 1s]. Thank you, much appreciated.

 

[Moley84]

Hi jolivanes, I've got it working. I altered your final line with this:

targetSheet.Range("H8").Resize(rng1.Rows.count - 1, rng1.Columns.count).Replace What:="*", Replacement:=1

Thank you for all your help,
Moley84

 

[jolivanes]

Good to see that you have it the way you need it.
Thanks for letting us know and good luck