Consulting

Results 1 to 15 of 15

Thread: VBA Loop on Overly Coded Macro

  1. 03-07-2018, 10:09 AM #1
    Weber
    Weber is offline
    VBAX Regular
    Joined
    Mar 2018
    Posts
    9
    Location

    VBA Loop on Overly Coded Macro

    The Form that was created made the copy and paste macro much larger than I wanted to be. I am pulling data from a data set and completing a form. Images Attached. I have a code completing the form and saving it with a unique name but now Im not sure how to create a loop to have it move down the rows. Any assistance would be greatly appreciated.

    Sub AutoContests

 Range("A2").Select    Selection.Copy
    Sheets("Promo Order Form").Select
    Range("$C$13:$D$13").Select
    ActiveSheet.Paste
    Sheets("Data Entry").Select
    Range("B2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Promo Order Form").Select
    Range("$C$18:$D$18").Select
    ActiveSheet.Paste
    Sheets("Data Entry").Select
    Range("C2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Promo Order Form").Select
    Range("$C$14:$D$14").Select
    ActiveSheet.Paste
    Sheets("Data Entry").Select
    Range("D2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Promo Order Form").Select
    Range("$C$16:$D$16").Select
    ActiveSheet.Paste
    Sheets("Data Entry").Select
    Range("F2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Promo Order Form").Select
    Range("$D$33").Select
    ActiveSheet.Paste
    Sheets("Data Entry").Select
    Range("G2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Promo Order Form").Select
    Range("$C$33").Select
    ActiveSheet.Paste
    Sheets("Data Entry").Select
    Range("I2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Promo Order Form").Select
    Range("$C$12:$D$12").Select
    ActiveSheet.Paste
    Sheets("Data Entry").Select
    Range("J2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Promo Order Form").Select
    Range("$B$33").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False


End Sub

    Sub SaveReport()
    Dim fileName As String
    
    fileName = Range("A2").Value & " " & Range("G2").Value & " " & Range("F2").Value
    
    ActiveWorkbook.SaveAs fileName:="I:\Marketing\Trade Team\Contests" & fileName, FileFormat:=xlOpenXMLStrictWorkbook
End Sub
    Again thank you for your help.
    Attached Images Attached Images
    Last edited by SamT; 03-07-2018 at 10:22 AM.
    Reply With Quote Reply With Quote
  2. 03-07-2018, 10:22 AM #2
    SamT
    SamT is offline
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    If you are going to learn to use VBA Code, the first step is to record Macros and clean them up. Your self.

    Example1: 
    Range("A2").Select    Selection.Copy
    Sheets("Promo Order Form").Select
    Range("$C$13:$D$13").Select
    ActiveSheet.Paste
    Cleans up to 
    Range("A2").Copy Sheets("Promo Order Form")Range("$C$13:$D$13")
Application.CutCopyMode = False
    Example2: 
    Sheets("Data Entry").Select
    Range("D2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Promo Order Form").Select
    Range("$C$16:$D$16").Select
    ActiveSheet.Paste
    Cleans up to: 
    'Note Line Continuation Marker " _"
Sheets("Data Entry").Range("D2").Copy _
        Sheets("Promo Order Form")Range("$C$16:$D$16")
Application.CutCopyMode = False
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ
    Reply With Quote Reply With Quote
  3. 03-07-2018, 10:24 AM #3
    Weber
    Weber is offline
    VBAX Regular
    Joined
    Mar 2018
    Posts
    9
    Location
    Ill get this cleaned up. First day using macros/vbas just off googling things. Thank you for showing me.
    Reply With Quote Reply With Quote
  4. 03-07-2018, 10:42 AM #4
    Weber
    Weber is offline
    VBAX Regular
    Joined
    Mar 2018
    Posts
    9
    Location
    On the first

    Range("A2").Copy Sheets("Promo Order Form")Range("$C$13:$D$13")
Application.CutCopyMode = False
    Its giving me a compile error expected end of statement on the second range

    Why would this happen?
    Last edited by Weber; 03-07-2018 at 11:01 AM.
    Reply With Quote Reply With Quote
  5. 03-07-2018, 11:18 AM #5
    SamT
    SamT is offline
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Missing the Dot after Sheets("Promo Order Form")

    See if this fixes it. The Keyword "Destination:=" is optional 
    Range("A2").Copy Destination:=Sheets("Promo Order Form").Range("$C$13:$D$13")
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ
    Reply With Quote Reply With Quote
  6. 03-07-2018, 11:23 AM #6
    Weber
    Weber is offline
    VBAX Regular
    Joined
    Mar 2018
    Posts
    9
    Location
    SamT

    This fixed it. Again thank you for the help. I will get this all cleaned up and will keep studying up on macro cleaning.
    Reply With Quote Reply With Quote
  7. 03-07-2018, 01:00 PM #7
    Weber
    Weber is offline
    VBAX Regular
    Joined
    Mar 2018
    Posts
    9
    Location
    OK so this has been cleaned to

    Sub AutoContests()


    Sheets("Data Entry").Range("A2").Copy _
        Sheets("Promo Order Form").Range("$C$13:$D$13")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("B2").Copy _
        Sheets("Promo Order Form").Range("$C$18:$D$18")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("C2").Copy _
        Sheets("Promo Order Form").Range("$C$14:$D$14")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("D2").Copy _
        Sheets("Promo Order Form").Range("$C$16:$D$16")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("F2").Copy _
        Sheets("Promo Order Form").Range("$D$33")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("G2").Copy _
        Sheets("Promo Order Form").Range("$C$33")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("I2").Copy _
        Sheets("Promo Order Form").Range("$C$12:$D$12")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("J2").Copy _
        Sheets("Promo Order Form").Range("$B$33")
            Application.CutCopyMode = False


End Sub
    And


    Sub SaveReport()
    Dim fileName As String
    
    fileName = Range("A2").Value & " " & Range("G2").Value & " " & Range("F2").Value
    
    ActiveWorkbook.SaveAs fileName:="I:\Marketing\ASWO_Marketing\Trade Team\Contests\" & fileName, FileFormat:=xlOpenXMLStrictWorkbook
End Sub

    would I just use an offet(1,0) in a loop to pull and save the correct information? Do the $ override the offset?
    Reply With Quote Reply With Quote
  8. 03-07-2018, 01:51 PM #8
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,724
    Location
    Possible next step


    Option Explicit
Sub AutoContests()
    Dim wsPromo As Worksheet
    
    Set wsPromo = Sheets("Promo Order Form")
    
    With Sheets("Data Entry")
        .Range("A2").Copy wsPromo.Range("$C$13:$D$13")
        .Range("B2").Copy wsPromo.Range("$C$18:$D$18")
        .Range("C2").Copy wsPromo.Range("$C$14:$D$14")
        .Range("D2").Copy wsPromo.Range("$C$16:$D$16")
        .Range("F2").Copy wsPromo.Range("$D$33")
        .Range("G2").Copy wsPromo.Range("$C$33")
        .Range("I2").Copy wsPromo.Range("$C$12:$D$12")
        .Range("J2").Copy wsPromo.Range("$B$33")
    End With
        
    Application.CutCopyMode = False
End Sub
    would I just use an offet(1,0) in a loop to pull and save the correct information? Do the $ override the offset?
    1. Since there does not seem to be any pattern to the destination cells, using .Offset in a loop would probably not gain anything except more complexity

    2. The $ has to do with Absolute and Relative Addresses (check help for details)
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
  9. 03-07-2018, 02:19 PM #9
    Weber
    Weber is offline
    VBAX Regular
    Joined
    Mar 2018
    Posts
    9
    Location
    Paul,

    Thanks for your response. The Destination cells will stay the same as they are the form locations. I just need to change the copy location in the loop. I know this is really confusing. Im really new at this and really appreciate the help.

    Im aware that $ references absolute values, I was unaware of having that there would make it so if it was offset to (1,0) it would just change the copy location not the destination location.

    Thank you for your help.
    Reply With Quote Reply With Quote
  10. 03-07-2018, 02:46 PM #10
    SamT
    SamT is offline
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Absolute Cell Addresses do not affect .Offset

    Since all destinations cannot be put in a loop, don't loop thru the sources.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ
    Reply With Quote Reply With Quote
  11. 03-07-2018, 03:05 PM #11
    Weber
    Weber is offline
    VBAX Regular
    Joined
    Mar 2018
    Posts
    9
    Location
    Do you see any way to do this?

    What if I added delete row 2 at the end of the first sub? I store the data in another location anyway.
    Reply With Quote Reply With Quote
  12. 03-07-2018, 03:17 PM #12
    SamT
    SamT is offline
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Do you see any way to do this?
    Do what?

    What if I added delete row 2 at the end of the first sub? I store the data in another location anyway.
    Hunh!? What!?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ
    Reply With Quote Reply With Quote
  13. 03-07-2018, 03:31 PM #13
    Weber
    Weber is offline
    VBAX Regular
    Joined
    Mar 2018
    Posts
    9
    Location
    To take the data from "Data Entry" fill out the form in "Promo Order Form" Save the form and then start on the next row in "Data Entry". I have to fill out around 1000 forms and im just trying to automate it.
    Reply With Quote Reply With Quote
  14. 03-07-2018, 05:29 PM #14
    Weber
    Weber is offline
    VBAX Regular
    Joined
    Mar 2018
    Posts
    9
    Location
    Ok heres my Idea. 

    Sub AutoContests()


   Sheets("Data Entry").Range("A2").Copy _
        Sheets("Promo Order Form").Range("$C$13:$D$13")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("B2").Copy _
        Sheets("Promo Order Form").Range("$C$18:$D$18")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("C2").Copy _
        Sheets("Promo Order Form").Range("$C$14:$D$14")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("D2").Copy _
        Sheets("Promo Order Form").Range("$C$16:$D$16")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("F2").Copy _
        Sheets("Promo Order Form").Range("$D$33")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("G2").Copy _
        Sheets("Promo Order Form").Range("$C$33")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("I2").Copy _
        Sheets("Promo Order Form").Range("$C$12:$D$12")
            Application.CutCopyMode = False
    'Line Break
    Sheets("Data Entry").Range("J2").Copy _
        Sheets("Promo Order Form").Range("$B$33")
            Application.CutCopyMode = False
            
    Dim fileName As String
    
    fileName = Range("A2").Value & " " & Range("G2").Value & " " & Range("F2").Value
    
    ActiveWorkbook.SaveAs fileName:="I:\Marketing\ASWO_Marketing\Trade Team\Contests\" & fileName, FileFormat:=xlOpenXMLStrictWorkbook
    
    Sheets("Data Entry").Rows(2).Delete


End Sub
    How would I loop this until there is no data in row 2? I pull the information and paste it in form a google sheet anyway. I also probably need to change

    FileFormat:=xlOpenXMLStrictWorkbook

    to a format that wont ask me about not saving the Macros.

    Let me know what yall think.
    Reply With Quote Reply With Quote
  15. 03-08-2018, 06:43 AM #15
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,724
    Location
    1. Looping rows 2 - N on DataEntry is easy

    2. You put data into Promo Order Form into rows 13, 18, 14, 16, 33, and 12

    3. I'm guessing that your unstated objective is to use the Data Entry list to fill out a template Promo Order Form worksheet and then save that worksheet as a separate file with a certain name


    Option Explicit

Sub AutoContent()
    Dim wsPromo As Worksheet
    Dim sFileName As String
    Dim i As Long
    Dim wbMaster As Workbook, wbPromo As Workbook
    
    Set wbMaster = ThisWorkbook
    Set wsPromo = Sheets("Promo Order Form")
    
    Application.ScreenUpdating = False
    
    With Sheets("Data Entry")
        For i = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
            .Cells(i, 1).Copy wsPromo.Range("$C$13:$D$13")
            .Cells(i, 2).Copy wsPromo.Range("$C$18:$D$18")
            .Cells(i, 3).Copy wsPromo.Range("$C$14:$D$14")
            .Cells(i, 4).Copy wsPromo.Range("$C$16:$D$16")
            .Cells(i, 6).Copy wsPromo.Range("$D$33")
            .Cells(i, 7).Copy wsPromo.Range("$C$33")
            .Cells(i, 9).Copy wsPromo.Range("$C$12:$D$12")
            .Cells(i, 10).Copy wsPromo.Range("$B$33")
    
            Application.CutCopyMode = False
    
            sFileName = .Cells(i, 1).Value & " " & .Cells(i, 7).Value & " " & .Cells(i, 6).Value
            
            wsPromo.Copy
    
            Set wbPromo = ActiveWorkbook
            wbPromo.SaveAs fileName:=sFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    
            ActiveWindow.Close
        Next i
    End With
        
    Application.ScreenUpdating = True
        
End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Reply With Quote Reply With Quote
« Previous Thread | Next 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
  •  

Forum Rules