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
Bookmarks