Take rows from one sheet, put into other based on other sht

[macdian]

Hi,

I've got a very interesting problem.

I've got 2 workbooks:

#1)Is a massive listing of accounts with unique account numbers in column A, and various other data pertaining to each account in about the next 7 columns

#2)Is a workbook with about 8 different sheets, all with specific names of "groupings" that the accounts from workbook 1 should be placed into, and each sheet has a list of the account numbers which make up that grouping.

What I am trying to do is get a macro that will separate workbook #1 into the groupings as per workbook 2. I want a separate workbook (or worksheet, whichever is easiest for me and the code)

Also, if the macro finds an account number in workbook 1 that is not in workbook 2, is it possible to generate a list of these?

I really appreciate anyone who can help me. I know this is possible, I just really don't know where to start.

Thanks,

Macdian

 

[SydneyGeek]

Thinking out loud --

Open Book2 and Book1.
For each sheet in Book2, do this:
1. Copy the list of relevant accounts
2. Switch to Book1, paste these accounts into cell Z2 (Z1 has Account Number for example -- make it EXACTLY the same as the heading in the big table.
3. Use Advanced Filter to filter for those accounts, using Z1:Z whatever as the Criteria.
4. Select the whole table, Edit | Go To | Special | Visible Cells only (ALT + E G S Y)
5. Copy and paste back to Book2

Repeat.

This can be automated with a bit more information.
Where is the account list in Book2 -- always the same location in the sheet?
Where in the sheet do you want to paste?
How many columns of data are there in Book1?

Denis

 

[macdian]

Hi Denis,

Thanks for the reply. That might actually work. However, I'd like to automate it for sure.

Right now, I don't have access to the sheets, so I can't remember exactly how many columns book 1 has, so I'm going to say it has 5 columns of data for now, which I'm sure can easily be changed if that's wrong.

To make things simple, I was thinking that for each sheet in book 2, (the account numbers), I would change the sheet so that they are all in column A, beginning in row one. The only identifier would be the sheet name (which is currently two letters, for example, SG). However, if need be, the sheet name could also be in the first row.

For pasting, I was also thinking the simpler the better, paste in row 1, column A.

You think it's easy to automate this?

Thanks again,

Macdian

 

[SydneyGeek]

Hi macdian, try this for a first shot. I created a Source book with this layout:

MoveAccountsSource.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Account	Identifier	Heading 1	Heading 2	Heading 3	Heading 4	Heading 5	Heading 6			Identifier		Destination:	MoveAccountsDest.xls
2	0001	SG	3253	4755	5965	4181	5213	2823			SG
3	0002	SA	2891	1710	3728	4407	1714	5325
4	0003	SC	2016	2437	2162	1849	5684	5120
5	0004	SD	5267	4603	4123	1942	4863	2802
Sheet1

Assumptions --
Destination and Source books are in the same folder, Destination book is closed before running routine
Destination book's name (with .xls extension) is in cell M1 of main data sheet in Source book
Main data table has at least 1 blank column and 1 blank row separating it from the Criteria info on the sheet
Main sheet in Source file is Sheet1. You'll have to adjust code to suit.
With those assumptions in mind, copy this code to a new module in the Source workbook (Alt + F11, Insert | Module) and return to Excel.
Press Alt + F8 to show list of macros, double-click the macro name.

Sub TransferData()
  Dim SourceBook As Workbook, DestBook As Workbook
  Dim Sht As Worksheet
  Dim sFilter As String
  
  Set SourceBook = ActiveWorkbook
  Workbooks.Open ActiveWorkbook.Path & "\" & Sheets("Sheet1").Range("N1").Value
  Set DestBook = ActiveWorkbook
  
  For Each Sht In DestBook.Sheets
    sFilter = Sht.Name
    With SourceBook.Sheets("Sheet1")
      .Activate
      .Range("K2").Value = sFilter
      On Error Resume Next
      .ShowAllData
      On Error GoTo 0
      .Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("K1:K2"), Unique:=False
      .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=DestBook.Sheets(sFilter).Range("A1")
    End With
  Next Sht
  SourceBook.Sheets("Sheet1").Activate
  ActiveSheet.ShowAllData
End Sub

Denis

 

[macdian]

Hi Denis,

I will try that. Sounds good though. Thanks!

Macdian