Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to sheet name in column 1
Trying to copy AREAs to the sheet of the employee whose name is the same as "Ar" in the - For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
If current Ar = Name 1 then the AREA should go to the sheet named Name 1. I'm using Name 1, Name 2 etc, as sheet names and employee names. "Main" sheet column 1 has Name n and data for Name n is in columns B to E down any number of rows. Say 5 to 45, varies. There is a one blank row between AREAs. The "Set ArCpy As..." works just fine, can't get the copy-to-sheet-name thing correct. Thanks, Howard Sub Staff_Info_To_Staff_Sheet() Dim Ar As Range Dim ArRow As Long, ArDwn As Long Dim ArCpy As Range Dim sNme As Worksheet For Each Ar In Columns("A").SpecialCells(xlConstants).Areas ArDwn = Ar.Offset(, 1).End(xlDown).Row 'Set sNme.Name = Ar.Offset(0, 0) Set ArCpy = Range(Cells(Ar.Row, 2), Cells(ArDwn, 5)) ArCpy.Copy Sheets(sNme).Range("A" & Rows.Count).End(xlUp)(2) Next End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy every Nth column to a new sheet | New Users to Excel | |||
Code to copy column in sheet 1 to column in sheet 2 based on matched criteria | Excel Programming | |||
Match value from column A to column A on 2nd sheet and then copy R | Excel Programming | |||
How to search column, copy row, and copy to another sheet in same | Excel Discussion (Misc queries) | |||
how to make one column copy from one sheet to anoth column w/o zer | Excel Discussion (Misc queries) |