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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.