Thanks for your help - I think it has got me a step in the right direction -
however, it still does not appear to be working. Can anyone suggest a
modification to the code to make it work. The code as it stands at the
moment is as follows:

Dim strWSName As String
Dim ws As Worksheet

done = False

Windows("Cancer monitoring (Commissioner).xls").Activate

For Each ws In ActiveWorkbook.Worksheets

'match the left four characters of the filename and sheet name

If Left(ws.Name, 4) = Left(wbdatafile.Name, 4) Then

wbdatafile.Open
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

ThisWorkbook.Activate

strWSName = wbdatafile.Name
If SheetExists = True Then
Worksheets(strWSName).Activate

Range("B65536").End(xlUp).Offset(1, -1).Select
ActiveSheet.Paste
Range("B65536").End(xlUp).Offset(1, -1).Select

wbdatafile.Activate
ActiveWorkbook.Close

done = True

Exit For
End If
End If

Next


Thanks for your anticipated help!





"sali" wrote:

"BoRed79" je napisao u poruci interesnoj
...
Hi All.

Can anyone please offer some advice on where I might start to create a
macro
to solve the following problem:

I have a series of xls files containing data each with a prefix of 1.1,
1.2,
1.3 etc. I want to take the data from each of these files in turn and
paste
it into a master spreadsheet. In the spreadsheet there are sheets with
names
that match the prefix of the downloads e.g. 1.1, 1.2, 1.3 etc.

I need to macro to match up the xls file name to the sheet name to
determine
where it pastes the data.

I have code to enable it to copy and paste the data into the sheets - but
not the functionality to match the data to the correct sheets.

here is the main loop, just insert you copy/paste sheet code in the match
place

----
done = false
for each ws in master.sheets
if ws.name = wbdatafile.name then
'sheet match - copy from wb data file to master sheet
done = true
exit for
end if
next
if not done then
msgbox "not matched, not pasted!"
end if
----


.