+ Reply to Thread
Results 1 to 2 of 2

Copying and pasting data from different wbs

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Leeds
    MS-Off Ver
    Excel 2013
    Posts
    177

    Copying and pasting data from different wbs

    Hi all,
    Trying to resolve some old VBA I found that xladept helped me with.

    I am trying to copy and paste the ranges A2:G34 and paste in sets of 32 rows one after the other in the wb "trackerdatamerge.xlsm". There are 20 wbs:
    7aIt1,7aIt2,7aIt3,7aIt4,7aIt5,7bIt1,7bIt2,7bIt3,7bIt4,7bIt5,8aIt1,8aIt2,8aIt3,8aIt4,8aIt5,8bIt1,8bIt2,8bIt3,8bIt4,8bIt5

    Directories below are correct. The vba is opening (and closing) 7aIt1 (first wb) and copying/pasting the correct values, but it then doesn't open the next wb.

    If anyone could help I'd really appreciate it!

    Sub Level(): Dim P, U As String, A As Range, R As Range, i As Long: i = 1
    Dim wb As Workbook, sb As Workbook, ws As Worksheet, ss As Worksheet
    
    P = "T:\Common Area\ICT\Tracking\KS3\"
    U = Dir(P)
    
    Workbooks.Open Filename:="T:\Common Area\ICT\Tracking\KS3\" & "Trackerdatamerge.xlsm"
     
    Set sb = Workbooks("Trackerdatamerge.xlsm"): Set ss = sb.Worksheets("Sheet1")
    
    SetNextBook:
    If U = "Trackerdatamerge.xlsm" Then GoTo GetNextBook
    
    Workbooks.Open Filename:="T:\Common Area\ICT\Tracking\KS3\" & U
       
        Set wb = ActiveWorkbook
        Set ws = wb.Worksheets("Project1")
       
        Set A = ws.Range("A2:G33"): Set R = ss.Range("A" & i)
                                
                A.Copy
                R.PasteSpecial xlPasteValues
                i = i + 32
                
        wb.Close SaveChanges:=False
    GetNextBook:
    If i > 31 Then Exit Sub
    On Error GoTo EndSub
    U = Dir(): GoTo SetNextBook
    
    EndSub: End Sub

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Copying and pasting data from different wbs

    I just noticed this thread

    Get rid of:

    If i > 31 Then Exit Sub
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

+ 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. Replies: 1
    Last Post: 12-07-2013, 05:23 PM
  2. Finding data, copying and pasting
    By sentinela in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-02-2009, 10:09 PM
  3. Is there a macro for copying and pasting data..
    By Tibby in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2008, 09:40 AM
  4. Excel 2007 : Copying and Pasting with Filtered Data
    By lambed3 in forum Excel General
    Replies: 1
    Last Post: 10-23-2008, 05:19 PM
  5. Copying and Pasting Array Data.. etc
    By amedhussaini in forum Excel General
    Replies: 1
    Last Post: 02-20-2008, 05:27 PM

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