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.

 

[Akuini]

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([COLOR=#b22222]nn[/COLOR], j).Value = Cells(i, j).Value

Worksheets(Main).Range(Cells(i,j)).Copy
Worksheets(PM1).Range(Cells([COLOR=#b22222]i[/COLOR],j)).PasteSpecial xlPasteFormats

When you 'paste' the value you use 'Cells(nn, j)', but when you paste the format you use 'Cells(i, j)', is that different cell?

 

[sheetspread]

I would try to write a sample sub that just copies from one sheet and pastes to another, if you can get that then work from there since you're sure the problem isn't with the variables, etc.

 

[UNTEngineer]

I would try to write a sample sub that just copies from one sheet and pastes to another, if you can get that then work from there since you're sure the problem isn't with the variables, etc.

Like I said before, Ive tried that, and it still doesnt work. Its not the variables.

 

[UNTEngineer]

I tried using Activate, and still no luck. Now it Flags the line where I clear the tab.

Private Sub Copy_Sub()

Dim i As Integer
Dim j As Integer
Dim n As Integer
Dim nn As Integer

Dim Sub1 As String
Dim Main As String

Main = "Project Milestones"

Sub1 = Worksheets(Main).Range("AV10") 

Worksheets(Sub1).Range("A8:AA150").Clear

n = 0
For i = 8 To 210
If Cells(i, 7).Value = Sub1 Then
n = n + 1
nn = 8 + (n - 1)
For j = 2 To 25
Worksheets(Sub1).Cells(nn, j).Value = Cells(i, j).Value
Worksheets(Main).Range("A8:AA50").Copy
Worksheets(Sub1).Activate
Worksheets(Sub1).Range("A8:AA50").PasteSpecial xlPasteFormats
Worksheets(Main).Activate

Next j
End If
Next i

 

[sheetspread]

Does this help:

Copying Format of one excel sheet to another excel worksheet using VBA - Stack Overflow


?

 

[UNTEngineer]

Does this help:

Copying Format of one excel sheet to another excel worksheet using VBA - Stack Overflow
?

It might if I was trying to copy and paste format for whole or partial sheets...but Im trying to copy and paste format for only the selected rows and columns per my code loop. If I can do something like:

Worksheets(Main).Cells(i, j).Copy
Worksheets(Sub1).Activate
Worksheets(Sub1).Range(nn, j).PasteSpecial xlPasteFormats
Worksheets(Main).Activate<code></code>

This would be preferable, but as before, this doesnt work. It throws an error: Subscript out of Range.

 

[Michael M]

that's because you don't have a sheet OR variable called Main
Main in your code is simply a text string variable for another string

try this
also I'd try to avoid....
Using the word SUB in the title OR as a variable, it gets too confusing for debugging

Private Sub Copy_Sub()

Dim i As Integer, j As Integer, n As Integer
Dim nn As Integer, Sub1 As String
Sub1 = Worksheets("Project Milestones").Range("AV10").Value
Worksheets("Project Milestones").Range("A8:AA150").Clear
n = 0
For i = 8 To 210
If Cells(i, 7).Value = Sub1 Then
n = n + 1
nn = 8 + (n - 1)
For j = 2 To 25
Worksheets(Sub1).Cells(nn, j).Value = Cells(i, j).Value
Worksheets("Project Milestones").Range("A8:AA50").Copy
Worksheets(Sub1).Activate
Worksheets(Sub1).Range("A8:AA50").PasteSpecial xlPasteFormats
Worksheets("Project Milestones").Activate

Next j
End If
Next i
End Sub

 

[UNTEngineer]

Id probably change the variable as SubCon...it stands for Subcontractor - Sub is just easier.

Also, Id probably change the

Main As String

to

Main As Worksheet = sheet("Project Milestones")

Still nobody has hit the issue on the head - Copy and PasteSpecial is not working...trying to clean up my code isnt going to make this other bit of code work.

 

[Michael M]

seems we've tried a number of solutions, to no avail !!
Can you upload the workbook to dropbox or similar and someone may be able to take a more serious look at it..!

 

[UNTEngineer]

seems we've tried a number of solutions, to no avail !!
Can you upload the workbook to dropbox or similar and someone may be able to take a more serious look at it..!

Uh, I will try to. At the moment the code is embedded in my project schedule so I cant just share the whole workbook. Its populated with highly proprietary info. If I get the chance to create a new empty workbook, I'll do it.