VBA Sheet Name

[sswcharlie]

I have 2 columns, reference data and column with which sheet it is to go in. Sheet 1, 2 etc

column two is populated by a vlookup. as this number changes frequently.

I want to do a copy and paste.

Want to copy Sheet 1 A1, and put it into the sheet listed in Sheet 1 A2,(in this case Sheet3) position D1

Which is best vba to try ?

Thks
Charlie

[domfootwear]

Pls send to us the excel sample.

[sswcharlie]

I have attached a workbook with comments of mine in sheet called 'Master'

This is a project for my model railroad, tracking of rolling stock around the layout.

Thanks

Charlie

[mbarron]

[VBA]
Sub MoveToSheet()
Dim i As Integer, shtTo As Worksheet
i = 9
Do Until Sheets("master").Cells(i, 5) = ""
Set shtTo = Sheets(Sheets("master").Cells(i, 5).Value)
Sheets("master").Cells(i, 4).Copy _
Destination:=shtTo.Cells(Rows.Count, 4).End(xlUp).Offset(1)
i = i + 1
Set shtTo = Nothing
Loop
Range(Cells(9, 4), Cells(i, 5)).ClearContents 'clears out table starting in D9



End Sub[/VBA]

[sswcharlie]

(Third time trying to post to the group, some problem somewhere)

Hi mbarron

Wow. The code works like magic. Very pleased.

I got ahead of myself with the code. There needs to be some code before to take the number in Master!D9 and delete it from one of the location sheets, then your code above comes next.

That is the reading comes in, macro checks and deletes reference from one of the location sheets. Then macro puts the reading in its new location.

I think that the code above for part 2 is suitable with some mods to use as the macro to delete previous reference in one of the sheets.

Find masterD9 in one of the sheets
Delete
return to masterD9
run code to insert new location

Would love your comments.

Thanks
Charlie Harris
New Zealand

[mikerickson]

Rather than having a macro change cell references, a Named range could be used.
Only one value needs to be changed when you want it to point to a different range.