PDA

View Full Version : VBA Sheet Name



sswcharlie
02-08-2010, 06:40 PM
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
02-08-2010, 06:46 PM
Pls send to us the excel sample.

sswcharlie
02-08-2010, 11:37 PM
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
02-09-2010, 09:28 AM
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

sswcharlie
02-10-2010, 10:04 PM
(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
02-11-2010, 02:55 AM
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.