For/Next Loop Iteration Based on Specific Columns

[zdodson]

All,

Below is the code I am working with

Sub ExtractBST()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim Current As Worksheet
Dim I As Long

Application.ScreenUpdating = False

'set variables for copy and destination worksheets
Set wsCopy = Workbooks("TRAINING.xlsm").Worksheets("Marines")
Set wsDest = Workbooks.Add.Worksheets(1)

'1. FIND LAST ROW IN THE COPY RANGE BASED ON DATA IN COLUMN B
    lCopyLastRow = wsCopy.Cells(Rows.Count, "B").End(xlUp).Row

'2. FIND LAST ROW IN THE DESTINATION RANGE BASED ON DATA IN COLUMN A
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row

'3. COPY & PASTE DATA

    For I = 1 To 28

        wsCopy.Range("A1:F" & lCopyLastRow).Copy _
            Sheets(I).Range("A" & lDestLastRow)
       
       [B][COLOR=rgb(226, 80, 65)] wsCopy.Range("AJ1:AJ" & lCopyLastRow).Copy _[/COLOR][/B]
            Sheets(I).Range("G" & lDestLastRow)
       
        ActiveWorkbook.Sheets.Add AFTER:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
       
    Next I
   
'4. FOR/NEXT LOOP TO FORMAT THE REPORT

    For Each Current In Worksheets
        Current.Activate
        Call wsFormatBST

    Next

    Worksheets(1).Select
   
Application.ScreenUpdating = True

For this portion of the code, my origin column begins in column AJ. I want to iterate from column AJ through column BM, copying and pasting the data from the origin (wsCopy) to its destination (wsDest).

wsCopy.Range("AJ1:AJ" & lCopyLastRow).Copy _[/B]
            Sheets(I).Range("G" & lDestLastRow)

Any help would be greatly appreciated. Thank you in advance!

 

[Fluff]

Try

wsCopy.Range(wsCopy.Cells(1, i + 35), wsCopy.Cells(lCopyLastRow, i + 35)).Copy _
        Sheets(i).Range("G2")

 

[zdodson]

Try

wsCopy.Range(wsCopy.Cells(1, i + 35), wsCopy.Cells(lCopyLastRow, i + 35)).Copy _
        Sheets(i).Range("G2")

You're a rock star. Works perfectly. Consider this one solved.

 

[Fluff]

Glad we could help & thanks for the feedback