Hello,

I am trying to copy and column J from an odd number (starting from 05) sheet
to even number sheet:

For example:

copy Column J of 05-Test (Sheet name) and paste column J of 06-Test (Sheet
name)

so on for 07-Test to 08-Test, Up to 25-Test to 26-Test.


Thanks

Sub CopyColJ()

For Each sht In Sheets

If UCase(Right(Trim(sht.Name), 5)) = "-TEST" Then
ShtNum = Val(sht.Name)
'test if sheet number is odd
If ShtNum Mod 2 = 1 Then
NextShtName = Format(ShtNum + 1, "#00") & _
"-Test"
sht.Columns("J").Copy _
Destination:=Sheets(NextShtName).Columns("J")
End If
End If

Next sht


End Sub

"bioyyy" wrote:

Hello,

I am trying to copy and column J from an odd number (starting from 05) sheet
to even number sheet:

For example:

copy Column J of 05-Test (Sheet name) and paste column J of 06-Test (Sheet
name)

so on for 07-Test to 08-Test, Up to 25-Test to 26-Test.


Thanks

Try the macro
'Note: No error checking
Sub copyToAlternateSheets()
Dim i As Integer
Dim ws As Worksheet
Dim srcName, destName As String

For i = 5 To 13 Step 2
If i < 10 Then
srcName = "0" & i & "-Test"
destName = "0" & (i + 1) & "-Test"
Else
srcName = i & "-Test"
destName = (i + 1) & "-Test"
End If
If i = 9 Then
srcName = "0" & i & "-Test"
destName = (i + 1) & "-Test"
End If
Set ws = Worksheets(srcName)
If (ws.Name < "") Then
Worksheets(srcName).Activate
ActiveSheet.Columns("J:J").Copy
Worksheets(destName).Activate
ActiveSheet.Range("J1").Select
ActiveSheet.Paste
End If
Next i
End Sub


"bioyyy" wrote:

Hello,

I am trying to copy and column J from an odd number (starting from 05) sheet
to even number sheet:

For example:

copy Column J of 05-Test (Sheet name) and paste column J of 06-Test (Sheet
name)

so on for 07-Test to 08-Test, Up to 25-Test to 26-Test.


Thanks

Joel and Sheelo:

Thanks for your BIG help! But I made one big mistake in my question.

The sheet names are not the same as I wrote Test. Actually, each sheet has
different names (i.e. 05-ABC, 06-Test, 07-adge, 08-iesdf, .....For example).
I hope you can help me to fix it. Thanks again,



"Sheeloo" wrote:

Try the macro
'Note: No error checking
Sub copyToAlternateSheets()
Dim i As Integer
Dim ws As Worksheet
Dim srcName, destName As String

For i = 5 To 13 Step 2
If i < 10 Then
srcName = "0" & i & "-Test"
destName = "0" & (i + 1) & "-Test"
Else
srcName = i & "-Test"
destName = (i + 1) & "-Test"
End If
If i = 9 Then
srcName = "0" & i & "-Test"
destName = (i + 1) & "-Test"
End If
Set ws = Worksheets(srcName)
If (ws.Name < "") Then
Worksheets(srcName).Activate
ActiveSheet.Columns("J:J").Copy
Worksheets(destName).Activate
ActiveSheet.Range("J1").Select
ActiveSheet.Paste
End If
Next i
End Sub


"bioyyy" wrote:

Hello,

I am trying to copy and column J from an odd number (starting from 05) sheet
to even number sheet:

For example:

copy Column J of 05-Test (Sheet name) and paste column J of 06-Test (Sheet
name)

so on for 07-Test to 08-Test, Up to 25-Test to 26-Test.


Thanks

Try
Sub copyToAlternateSheets()
Dim i As Integer
Dim ws, dest As Worksheet
Dim srcName, destName As String
Dim wksNumber
For Each ws In Worksheets
wksNumber = CInt(Left(ws.Name, 2))
If (wksNumber Mod 2) = 1 Then
srcName = ws.Name
For Each dest In Worksheets
If CInt(Left(dest.Name, 2)) = "0" & (wksNumber + 1) Then
destName = dest.Name
Exit For
End If
Next dest
Worksheets(srcName).Activate
ActiveSheet.Columns("J:J").Copy
Worksheets(destName).Activate
ActiveSheet.Range("J1").Select
ActiveSheet.Paste
End If
Next ws
End Sub

"bioyyy" wrote:

Joel and Sheelo:

Thanks for your BIG help! But I made one big mistake in my question.

The sheet names are not the same as I wrote Test. Actually, each sheet has
different names (i.e. 05-ABC, 06-Test, 07-adge, 08-iesdf, .....For example).
I hope you can help me to fix it. Thanks again,