Results 1 to 7 of 7

macro to consolidate - help!

Threaded View

jw01 macro to consolidate - help! 03-09-2012, 02:02 AM
arlu1201 Re: macro to consolidate -... 03-09-2012, 06:46 AM
jw01 Re: macro to consolidate -... 03-09-2012, 11:15 AM
jw01 Re: macro to consolidate -... 03-09-2012, 12:38 PM
jw01 Re: macro to consolidate -... 03-09-2012, 02:38 PM
jw01 Re: macro to consolidate -... 03-09-2012, 08:15 PM
arlu1201 Re: macro to consolidate -... 03-19-2012, 08:38 AM
  1. #1
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,228

    macro to consolidate - help!

    hello

    i have a macro that goes in a set path (folder) and extracts data from the "master" worksheet and dumps it into my together.

    the issue im having is the range on my macro is set as pretty large
    .Range("B10:P1009").Copy

    1) i was wondering if it would be possible so if column B contains "0" then it not copy over row?
    2) also, for each row that is pasted, so after the last column, if i can show how make the macro go into worksheet called "YTD OT Summary" sheet and paste over the name of the person in cell C10 i.e. each time a row is copied over in the B:P range, in column Q, it also assigns the name of the person?

    this is my code...can someone pls help....i have over 75 files so i would appreciate it!

    also, how can i make excel not calculation or set the workbook calculation to manual, each time the macro goes into a workbook?

    Option Explicit
    
    Dim Master As Workbook
    Dim sourceBook As Workbook
    Dim sourceData As Worksheet
    Dim CurrentFileName As String
    Dim myPath As String
    
    Sub cons_data()
    
    'The folder containing the files to be recap'd
    myPath = "C:\Documents and Settings\ht\Desktop\Colm\Test"
    
    'Finds the name of the first file of type .xls in the current directory
    CurrentFileName = Dir(myPath & "\*.xls")
    
    'Create a workbook for the recap report
    Set Master = ThisWorkbook
    
    Do
        Workbooks.Open (myPath & "\" & CurrentFileName)
        Set sourceBook = Workbooks(CurrentFileName)
        Set sourceData = sourceBook.Worksheets("Master")
        
    With sourceData
        .Range("B10:P1009").Copy
    End With
    With Master.Worksheets(1).Range("C" & Master.Worksheets(1).Rows.Count).End(xlUp).Offset(1, 0)
        .PasteSpecial Paste:=xlValues
    End With
    
    sourceBook.Close
      
    'Calling DIR w/o argument finds the next .xlsx file within the current directory.
    CurrentFileName = Dir()
    Loop While CurrentFileName <> ""
    
    End Sub
    pls help..thx u
    Last edited by jw01; 03-09-2012 at 08:24 PM.

Thread Information

Users Browsing this Thread

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

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