Results 1 to 4 of 4

Thread: alternate method please

  1. #1

    Lightbulb alternate method please



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone,

    I have a code which copies specific columns certain number of times, Here is the code:

    Code:
    Sub Copy_Columns()
    Dim lastr As Double
    Dim cols As Double
        cols = Sheets("Sheet1").Range("V4").Value * 3       ' Determine how many columns to copy across
        With Sheets("Sheet2")
            lastr = .UsedRange.Rows.Count   ' Last used row in worksheet
            .Range("D1:F" & lastr).Select
            Selection.AutoFill Destination:=Range(Cells(1, 4), Cells(lastr, cols + 3)), Type:=xlFillDefault
        End With
    End Sub

    Can anyone help me with the second line(marked in red). I want the same code working without With as i am getting some errors when looping this code
    Last edited by Bob Phillips; 2012-12-04 at 01:52 PM. Reason: Added code tags

  2. #2
    What error are you getting, and where are you trying to copy to, which sheet.

  3. #3
    Hello Bob,

    Thank you for your reply. To be clear i have two codes:


    code 1: This code duplicates the sheet name" template" specific number of times and renames it automatically.

    Dim iLoop As Integer, iCount As Integer


    iCount = Sheets("Row").Range("V1").Value


    For iLoop = 1 To iCount
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Campaign_" & iLoop
    ActiveSheet.Range("C2").Value = Sheets("Row").Range("T" & iLoop + 3).Value
    Next

    Code2: This code copies the the columns DEF to (GHI, JKL,MNO,PQR,STU,VWX.......etc to specific number of times) in the sheet named Campaingn_1

    Sub test_test()
    Dim lastr As Double
    Dim cols As Double


    cols = Sheets("row").Range("V4").Value * 3 ' Determine how many columns to copy across
    With Sheets("campaingn_1")
    lastr = .UsedRange.Rows.Count ' Last used row in worksheet
    .Range("D1:F" & lastr).Select
    Selection.AutoFill Destination:=Range(Cells(1, 4), Cells(lastr, cols + 3)), Type:=xlFillDefault

    End With
    End Sub






    What i need is the second code should run in all the sheets created by the first code( i.e the duplications of the sheets templete). Please do help me.

  4. #4
    I don't see that answers my questions, and you keep starting new threads. Keep one question in one thread!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •