Copy and paste to corporate sheet

[Guest]

I am working with 6 files. I need to copy the data from a sheet (within each
workbook/file) named 'substandard' and paste the information into the
'substandard' sheet in the master file. To do this I have been opening the
master file, placing the cursor where I want the information, opening the
file I wish to copy from, selecting the substandard sheet, copying the
information from the sheet and choosing paste special to the master file.
This takes forever. The files are all located in K:\fab\fab.xls,
K:\csb\csb.xls, K:\bsf\bsf.xls, K:\bag\bag.xls K:\bnp\bnp.xls to be
pasted into K:\corp\corp.xls. The sheets to select from within the files is
named 'substandard' - all with the same headings. I need to copy starting
from cell A6.
The sheets all have different amounts of data so I can't set a static range
to copy. And I don't know where it will need to be pasted in the master
sheet, or in other words the next empty row.

I know how to record a macro with the macro writer, but that is about it and
since the amount of data varies I am stumped.

Any suggestions??
Thanks for any help.
Marie

 

[Ron de Bruin]

You can start here Marie
http://www.rondebruin.nl/copy3.htm

 

[Guest]

Thanks! The site is very helpful!

I think the code I need is 'Copy all cells from the sheet or all cells
without the header row'

As I said I know some basic VB. I have a couple of questions about the code.

MyPath = "C:\Data"

I assume this is where I place the name of the folder I want to pull the
data from. Do all the files have to be in the same folder? In the example
above are all the files being copied in 'Data'. As I said mine are in
K:\csb\csb.xls and K:\bag\bag.xls, etc. Six different folders. Do I need to
create a folder to save them all in for this to work? They all have a sheet
that I want to copy with the same name but the files are all in different
folders.

And this line 'Copy from A2:IV? (till the last row with data on your sheet)

Is the IV like a wildcard? Does it copy everything on the sheet?

Thanks for your patience. I really appreciate it!

Marie

 

[Ron de Bruin]

Hi Marie

above are all the files being copied in 'Data'. As I said mine are in
K:\csb\csb.xls and K:\bag\bag.xls, etc. Six different folders. Do I need to
create a folder to save them all in for this to work?

Yes

I think I will create the C:\Data folder (empty folder) and run this macro first

Sub Copy_Files()
FileCopy "K:\csb\csb.xls", "C:\Data\csb.xls"
FileCopy "K:\bag\bag.xls", "C:\Data\bag.xls"
'Add four more lines
End Sub

Then try the sub from my site (copy the lastrow function also in the module)
You can change C:\Data to any folder

After the macro is ready you can use Kill to delete the files in C:\Data

If you need more help post back

 

[Guest]

I set up a couple of dummy files on my C drive to test it. In the files I
named a sheet substandard and input some data. I copied the code and
anywhere it referred to sheet1 I changed to substandard.

In the function I also changed it to A7 instead of A1 - is this right since
I want to copy from A7 - because of my headers?
What does this function do?


I am getting a Runtime error '9': subscript out of range on this line

basebook.Worksheets(Substandard).Cells.Clear

This is my code:

Sub Copy_Files()
FileCopy "C:\csb\csb.xls", "C:\Data\csb.xls"
FileCopy "C:\fab\fab.xls", "C:\Data\fab.xls"
'Add four more lines
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A7"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Sub Example7()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim lrow As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
basebook.Worksheets(Substandard).Cells.Clear
' clear all cells on the first sheet
rnum = 1

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
lrow = LastRow(mybook.Sheets(Substandard))
Set sourceRange = mybook.Worksheets(Substandard).Range("A2:IV" & lrow)
'Copy from A2:IV? (till the last row with data on your sheet)
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(Substandard).Cells(rnum, "A")

' sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only the
values

With sourceRange
Set destrange =
basebook.Worksheets(Substandard).Cells(rnum, "A"). _
Resize(.Rows.Count,
..Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Thanks for helping.

Marie

 

[Ron de Bruin]

Hi Marie

Do not change the cell address in the function

And is the sheet where you want to copy to also named Substandard ?

Change all

(Substandard)

To

("Substandard")

 

[Guest]

Yes, the file I want it to pull the data into has a sheet named Substandard.

I changed the cell address in the function back to A1 and changed the code
to include the quotes around "substandard". Now it is stopping at

Set sourceRange = mybook.Worksheets("Substandard").Range("A2:IV" & lrow)

BUT, it copied cell A1 in my CSB file - I have input in other cells it is
not picking up. It didn't pick up anything in by FAB file.

Am I still doing something wrong?

 

[Ron de Bruin]

Hi Marie

Send me your test file private then I look at it for you then

 

[Guest]

Just sent the files. Thanks
Marie

Hi Marie

Send me your test file private then I look at it for you then

 

[Ron de Bruin]

I have send you the example file
If you need more help let me know