MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying to another workbook via finding sheets


Posted by Emily on August 17, 2000 9:04 AM

I'm looking for a simple way to copy some data onto another workbook - It sounds simple so far but it will get complicated.

The idea is that I have a workbook (MasterData.xls) that contains data, in which each of these sheets are named (as accounts). Also in the workbook is a Sheet (New Accounts) which contains a list of some of the accounts.

I need a code that will move down the column (A) of "New Accounts" searching for the accounts that are the same name as the sheet tabs relevant to that account. In doing so, once found copies the data on that sheet and pastes it onto another Workbook (TempData.xls). It finds the 1st sheet and pastes the data onto it. It then returns to Masterdata.xls to "New Accounts" finds the next account in the column, finds the relevant page and copies and pastes onto the TempData.xls workbook on the next sheet. This is to be done until all the accounts in "New Accounts" column have been done.

Is this too tricky as I've come into a little difficulty as a lot of this is out of my realm.

Hope this is understandable.

Any help, will be worshipped.

Emily.

Ps In the TempData.xls Workbook I have already setup 10 sheets. However it might be easier if one sheet existed and everytime new data is to be pasted it will create a new sheet. (The sheets in the workbook contain a format that needs to be kept, so any new shhet must have the same format i.e. copied).


Posted by Ryan on August 17, 0100 10:48 AM

Emily,

Try this, it was working well for me! Hope it does the same for you.

Ryan

Sub PullAccounts()
Dim LastRow As Long

Application.ScreenUpdating = False

LastRow = Sheets("New Accounts").Range("A65000").End(xlUp).Row

For x = 1 To LastRow

If SheetExists(Sheets("New Accounts").Cells(x, 1).Text) = True Then _
Sheets(Sheets("New Accounts").Cells(x, 1).Text).Copy _
After:=Workbooks("TempData.xls").Sheets(Workbooks("TempData.xls").Sheets.Count)
Workbooks("MasterData.xls").Activate
Next x

Application.ScreenUpdating = True
End Sub
Function SheetExists(sname)
' Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function