+ Reply to Thread
Results 1 to 6 of 6

VBA code to create a workbook and copy or move worksheets in to workbook through loop

  1. #1
    Registered User
    Join Date
    03-03-2015
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    6

    Unhappy VBA code to create a workbook and copy or move worksheets in to workbook through loop

    hi All,

    I am not an expert in VBA but knows some basics. I have a list of invoice numbers for a number of customers. I have a macro created to create an invoice format for each invoices and create and save new excels based on invoice numbers. I am looking for a macro code to loop through one customers invoices, and create all invoices in one workbook.

    The first time when a condition is met, I would like the macro to create a workbook and second time onwards it should create sheets in to that workbook.

    example: until range("A2") = " " I want the macro to run a loop. If the first time condition is not met i want to create a new workbook and copy information. and then onwards i want to have a sheet created in the same workbook and copy info.

    can I get some help please.

    Thank you
    Anish

  2. #2
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

    Re: VBA code to create a workbook and copy or move worksheets in to workbook through loop

    Hi

    can you share ur code so that i can add further customisation..

    Cheers!!

  3. #3
    Registered User
    Join Date
    03-03-2015
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    6

    Re: VBA code to create a workbook and copy or move worksheets in to workbook through loop

    hi - Thank you for your reply.

    Apologies for the late response. I was able to resolve that issue with below code.

    but I am sure there must be a better way to get a faster result. If you could help me please.

    Sub Procedure1()

    Dim cell As Range
    Dim curPath As String
    Dim lastrow As Long
    curPath = ActiveWorkbook.Path & "\"
    Dim wb As Workbook
    Dim nPath As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each cell In Range("DebtorCode1")
    [DebtorCode] = cell.Value
    Range("myList").AdvancedFilter Action:=xlFilterCopy, _
    criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False

    Set wb = Workbooks.Add(xlWBATWorksheet)

    Workbooks("Invoices -Debtorwise Lease").Activate

    Do Until Sheets("breaking-data").Range("AK8").Value = ""

    Range("BH8").Copy
    Sheets("MANUAL INV").Select
    Range("C9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("breaking-data").Select
    Range("BB8").Copy
    Sheets("MANUAL INV").Select
    Range("C10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("breaking-data").Select
    Range("AL8").Copy
    Sheets("MANUAL INV").Select
    Range("N9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("breaking-data").Select
    Range("AU8").Copy
    Sheets("MANUAL INV").Select
    Range("N25").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False


    Sheets("MANUAL INV").Range("C25") = "Penalty"

    If Range("N25") = 0 Then
    Range("C25").ClearContents
    Range("N25").ClearContents
    End If

    ThisWorkbook.Sheets("MANUAL INV").Copy Before:=wb.Sheets(1)
    ActiveSheet.Name = Range("N9")
    Workbooks("Invoices -Debtorwise Lease").Activate
    Sheets("breaking-data").Select
    Range("AK8:BP8").Select
    Selection.Delete Shift:=xlUp


    Loop

    nPath = "O:\Invoices\" & ThisWorkbook.Sheets("MANUAL INV").Range("M15").Value
    With wb
    .SaveAs Filename:=nPath
    wb.Close
    End With
    Sheets("breaking-data").Select
    Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents

    Next cell
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    End Sub

  4. #4
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

    Re: VBA code to create a workbook and copy or move worksheets in to workbook through loop

    pl share excel file with dummy data for better understanding of your code...

  5. #5
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

    Re: VBA code to create a workbook and copy or move worksheets in to workbook through loop

    You may try this code if this works!

    Please Login or Register  to view this content.
    Cheers!!
    Last edited by JBeaucaire; 06-28-2015 at 02:55 AM.

  6. #6
    Registered User
    Join Date
    03-03-2015
    Location
    dubai
    MS-Off Ver
    2010
    Posts
    6

    Re: VBA code to create a workbook and copy or move worksheets in to workbook through loop

    the code worked perfectly. much faster than the one I prepared ...thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Loop through worksheets to copy data to new workbook
    By livelyzd in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-23-2013, 04:44 AM
  2. Replies: 1
    Last Post: 08-31-2012, 09:52 PM
  3. Loop through range and copy to new workbook as seperate worksheets
    By thesasman2012 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2012, 10:25 AM
  4. Loop code for certain worksheets in workbook
    By longbow007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-18-2010, 01:05 AM
  5. Need to loop code for certain worksheets in workbook
    By longbow007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-28-2010, 08:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1