dynamically select sheet
 

A cell in each row of my "main" spreadsheet (DataEntry) has the name of other
spreadsheets (in the same workbook). For simplicity, assume there are 4
spreadsheets -- named A, B, C, and D.

As a macro loops through the DataEntry rows, it is to copy each row to the
appropriate spreadsheet (A, B, C, or D) based on the sheet name in, say,
column D of each row (of DataEntry).

Using Sheets(?????).Select (if that's what I should use), what is the
syntax for referring to Column D and the respective row? I've tried
Sheets("D" & x).Select . which results in a 'bug'.

dynamically select sheet
 

Assuming datarange is column A and starts at A2 with no blank rows

dim dest as range
dim datarg as range
dim rcell as range
dim rgend as range
dim cpyrg as range

set rgend = Worksheets("DataEntry).range("a2").end(xldown)

set datarg = Worksheets("DataEntry).range("a2:a" & rgend.row).

for each rcell in datarg
set cpryrg = range(rcell,rcell.offset(0,3))
cpyrg.copy
' this copies the first three columns of the current row
set dest =
worksheets(cell.offset(0,4).value).range("a65000") .end(xlup).offset(1,0)
' this denotes the first blank row on the worksheet named in column
D
dest.pastespecial
next

HTH


swtrader wrote:
A cell in each row of my "main" spreadsheet (DataEntry) has the name of other
spreadsheets (in the same workbook). For simplicity, assume there are 4
spreadsheets -- named A, B, C, and D.

As a macro loops through the DataEntry rows, it is to copy each row to the
appropriate spreadsheet (A, B, C, or D) based on the sheet name in, say,
column D of each row (of DataEntry).

Using Sheets(?????).Select (if that's what I should use), what is the
syntax for referring to Column D and the respective row? I've tried
Sheets("D" & x).Select . which results in a 'bug'.

dynamically select sheet
 

I noticed a typo in my code
this line

worksheets(cell.offset(0,4).value).range("a65000") .end(xlup).offset(1,0)
should be

worksheets(rcell.offset(0,4).value).range("a65000" ).end(xlup).offset(1,0)





bobbo wrote:
Assuming datarange is column A and starts at A2 with no blank rows

dim dest as range
dim datarg as range
dim rcell as range
dim rgend as range
dim cpyrg as range

set rgend = Worksheets("DataEntry).range("a2").end(xldown)

set datarg = Worksheets("DataEntry).range("a2:a" & rgend.row).

for each rcell in datarg
set cpryrg = range(rcell,rcell.offset(0,3))
cpyrg.copy
' this copies the first three columns of the current row
set dest =
worksheets(cell.offset(0,4).value).range("a65000") .end(xlup).offset(1,0)
' this denotes the first blank row on the worksheet named in column
D
dest.pastespecial
next

HTH


swtrader wrote:
A cell in each row of my "main" spreadsheet (DataEntry) has the name of other
spreadsheets (in the same workbook). For simplicity, assume there are 4
spreadsheets -- named A, B, C, and D.

As a macro loops through the DataEntry rows, it is to copy each row to the
appropriate spreadsheet (A, B, C, or D) based on the sheet name in, say,
column D of each row (of DataEntry).

Using Sheets(?????).Select (if that's what I should use), what is the
syntax for referring to Column D and the respective row? I've tried
Sheets("D" & x).Select . which results in a 'bug'.