Results 1 to 12 of 12

VBA - Data not transferring when running macro

Threaded View

  1. #1
    Registered User
    Join Date
    09-10-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    8

    VBA - Data not transferring when running macro

    Hi guys,

    I'm currently running the following:

    Sub TransferDates()
        Dim wbSource As Workbook
        Dim wbDest As Workbook
        Dim ws As Worksheet
        Dim dtStart As Date
        Dim dtEnd As Date
    
        
        Set wbDest = Workbooks("End of year.xlsx")
    
        Set wbSource = ThisWorkbook
    
        
        dtStart = InputBox("Start date? (dd-mm-yyyy)")
        dtEnd = InputBox("End date? (dd-mm-yyyy)")
    
     
        If dtStart = 0 Or dtEnd = 0 Then Exit Sub
    
        Application.ScreenUpdating = False
        For Each ws In wbSource.Worksheets
    
            With ws
                With .Range(.Cells(3, "A"), .Cells.SpecialCells(xlCellTypeLastCell))
    
                  
                    .AutoFilter
                    .AutoFilter field:=15, Criteria1:=">=" & dtStart, Operator:=xlAnd, Criteria2:="<=" & dtEnd
    
                   
                    .Range("A4:A5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("A2")
                    .Range("B4:B5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("B2")
                    .Range("O4:O5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("C2")
                    .Range("P4:P5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("D2")
                    .Range("S4:S5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("E2")
                    .Range("T4:T5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("F2")
    
                    
                    .AutoFilter
                End With
            End With
        Next ws
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    
    End Sub
    However, the data isn't transferring to the new workbook and I can't quite figure out why.

    Any help is greatly appreciated!
    Last edited by aeronaught; 06-26-2018 at 09:35 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. range matching and data transferring macro running slowly
    By bqheng in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2017, 09:49 PM
  2. Transferring data using a macro
    By deano3141 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-13-2014, 09:05 AM
  3. Replies: 1
    Last Post: 02-05-2014, 03:43 PM
  4. Need a macro for transferring data based on unit value to corresponding tab
    By doubl3d80 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-17-2012, 03:45 PM
  5. Macro for transferring/copying data from one tab to another
    By AJJ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-08-2012, 02:29 PM
  6. Replies: 2
    Last Post: 06-29-2011, 03:35 AM
  7. Transferring numbers to keep a running total.
    By evenings in forum Excel General
    Replies: 1
    Last Post: 04-21-2008, 11:12 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