VBA Copy & Paste Special

[UNTEngineer]

Hello,

After many searches through your forum and on Google, Ive given up and having to resort to help on the forum.

Im trying to copy a range of text from my main sheet and paste it to subsequent sheets - that part works fine.

Next Im trying to copy the formatting and paste only the formatting to those sheets. This part keeps throwing errors, and I have no idea why.

n = 0
For i = 8 To 210
If Cells(i, 12).Value = PM1 Then
n = n + 1
nn = 8 + (n - 1)
For j = 2 To 25
Worksheets(PM1).Cells(nn, j).Value = Cells(i, j).Value

Worksheets(Main).Range(Cells(i,j)).Copy
Worksheets(PM1).Range(Cells(i,j)).PasteSpecial xlPasteFormats

I tried using a regular range rather than selecting Cells; that didnt work. I tried using the Cells.Cell Format to copy just the format, and that didnt work. Basically, Im trying to copy specific cells based on that if statement, and I need the format of those cells to go with the text.

 

[sheetspread]

Did you try putting quotes around the sheet names?

You don't need the range if you have cells()

everything else looks ok but how are you concluding the loops?

 

[UNTEngineer]

Did you try putting quotes around the sheet names?

You don't need the range if you have cells()

everything else looks ok but how are you concluding the loops?

I got rid of the strings in the sheet names in case Project Managers leave, so I made it easier to modify the code.

Dim PM1 As String
Dim PM2 As String
Dim PM3 As String
Dim PM4 As String
Dim PM5 As String
Dim Main As String

PM1 = Range("AR10") 
PM2 = Range("AR11") 
PM3 = Range("AR8") 
PM4 = Range("AR9")
PM5 = Range("AR13") 
Main = "Project Milestones"

Worksheets(PM1).Range("A8:AM106").Clear 'Clear the tabs
Worksheets(PM2).Range("A8:AM106").Clear
Worksheets(PM3).Range("A8:AM106").Clear
Worksheets(PM4).Range("A8:AM106").Clear

Loops are concluding as below:

Next j
End If
Next i

On Error GoTo 0
Exit Sub

End Sub

 

[sheetspread]

You have to refer to each sheet name (either outright or as a variable) with ranges if your code is in a module and not a sheet, e.g.:

PM1 = sheets("whateverthesheetnameis").Range("AR10")

and you might need a .value property also, though maybe not.

These are little things I change when debugging, hard to know for sure from just looking at the code.

If you have multiple action lines after an If you might also need an Else before the end if.

 

[UNTEngineer]

You have to refer to each sheet name (either outright or as a variable) with ranges if your code is in a module and not a sheet, e.g.:

PM1 = sheets("whateverthesheetnameis").Range("AR10")

and you might need a .value property also, though maybe not.

These are little things I change when debugging, hard to know for sure from just looking at the code.

If you have multiple action lines after an If you might also need an Else before the end if.

Adding that didnt help. Im still getting the error: PasteSpecial method of Range class failed.

 

[sheetspread]

If you pick a fixed cell reference without variables and try it do you get anything?

 

[UNTEngineer]

If you pick a fixed cell reference without variables and try it do you get anything?

Same problem. The variables arent causing the error, its the way the copy/paste special is happening.

Im trying to copy format from the mastersheet...I cant believe its really this difficult. I even tried following this page, but with no luck.

 

[Michael M]

To paste special you will need to activate the Destination sheet before pasting !!

 

[UNTEngineer]

To paste special you will need to activate the Destination sheet before pasting !!

But if Im pasting special to 5 tabs, that means I have to activate each one and then paste special, correct?

 

[UNTEngineer]

But if Im pasting special to 5 tabs, that means I have to activate each one and then paste special, correct?

When i try the code below, I get an error with another bit of my code, so Im baffled.

With ActiveSheet    Worksheets(MainTab).Range("A8:AA50").Copy
    Worksheets(PM1).Activate
    Worksheets(PM1).Range("A8:AA50").PasteSpecial xlPasteFormats
End With