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!

 

[MARK858]

What you need for looping through the columns might be like the code below but can you explain what you want with the destination sheet. Are you pasting each column to a separate new sheet?
Btw if you want to add manual formatting on the board use the <rich/> icon at the top of the reply window

        For i = 1 To 28
            wsCopy.Range(Cells(1, i + 35), Cells(lCopyLastRow, i + 35)).Copy _
        Sheets(i).Range("G" & lDestLastRow)
            ActiveWorkbook.Sheets.Add AFTER:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        Next i

 

[zdodson]

Mark,

Thanks for the help, sir. Simply put, what I want to do is:

Step 1: Paste columns A-F from the origin workbook to a new workbook
Step 2: Paste column G from the origin workbook to a new workbook on that same sheet. For example, Sheet 1 (Columns A-F, G), Sheet 2 (Columns A-F, H), etc.
Step 3: Do this via for/next loop until I have the ~29 new sheets.

Hopefully, that clears it up

.

 

[MARK858]

Hopefully, that clears it up

Not really I am afraid, how does

Step 2: Paste column G from the origin workbook to a new workbook on that same sheet. For example, Sheet 1 (Columns A-F, G), Sheet 2 (Columns A-F, H), etc.

fit in with the original question below?

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)

 

[zdodson]

Mark,

My apologies for the confusion. Seems like I made things as clear as mud in my clarification. Please disregard the initial attempt at clarifying.

What I would like to do is run a for/next loop that copies and pastes columns A-F in each sheet that is created. Then, I would like to pull a specific column (starting in column AJ on the origin sheet, and pasting it in column G in the destination. Each time it iterates through the for/next loop, it pulls the new column and pastes it in the sheet.

Sheet 1
-Origin: Columns A-F, Columns AJ
-Destination: Columns A-F, Columns G

Sheet 2
-Origin: Columns A-F, Columns AK
-Destination: Columns A-F, Columns G

etc. until we conclude with:

Sheet 29
-Origin: Columns A-F, Columns BM
-Destination: Columns A-F, Columns G

Than you for your patience.

 

[MARK858]

Ok that is clearer. Just one question so I am clear....

pasting it in column G in the destination.

Is it pasting in the same (new) sheet each time or is it pasting in a new sheet each loop, just in the original question it looks like it pastes in a new sheet on each loop (can't see why you would need the last row if this is the case).

 

[zdodson]

Mark,

Pasting in to a new sheet on each loop. For context, I am attempting to create separate training hit lists.

 

[MARK858]

Okay then I don't see the reason for lDestLastRow as it is a blank workbook, the untested code below pastes in G2. Let me know any issues and how close it is as it is totally untested.
Replace

    For I = 1 To 28

        wsCopy.Range("A1:F" & lCopyLastRow).Copy _
            Sheets(I).Range("A" & lDestLastRow)
   
       [wsCopy.Range("AJ1:AJ" & lCopyLastRow).Copy _
            Sheets(I).Range("G" & lDestLastRow)
   
        ActiveWorkbook.Sheets.Add AFTER:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
   
    Next I

with

    For i = 1 To 30
        ActiveWorkbook.Sheets.Add AFTER:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        wsCopy.Range("A1:F" & lCopyLastRow).Copy Sheets(i).Range("A2")
        wsCopy.Range(Cells(1, i + 35), Cells(lCopyLastRow, i + 35)).Copy _
        Sheets(i).Range("G2")
    Next i

 

[zdodson]

Got some time to test the recommendation for the code. It produced a run-time error '1004': Method 'Range' of object'_Worksheet' failed. I'll keep working the bolt on solving it; but, in the interim, do you know why it would be doing this if i is defined as a long variable (dim i as long)?

 

[zdodson]

Update: for the first part of your solution:

    For i = 1 To 30
        wsDest.Activate
        ActiveWorkbook.Sheets.Add AFTER:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        wsCopy.Range("A1:F" & lCopyLastRow).Copy _
            Sheets(i).Range("A2")

This works beautifully. This is exactly what I needed to occur. The problem I am still running in to is getting it to pull the second half of the hitlist (i.e. the column that holds individual hitlist information), and getting it to paste in the new sheets I am making through the code. That's the only part of it that is not working.