I have an Excel file with a significant amount of data in it. Every so often (about every other month) one of the guys in the group develops a newer version of the Excel file with more options in it, and more data. When this happens, currently all of the users of his Excel file go and (by hand) pull the data from the old version and copy/move/retype it into the newer version. I want to automate this. I've made a bit of headway but when I got to cells that are picked via Data Validation Drop Downs, the copy throws and error something like: "The name "Race" already exists. Click Yes to use that version of the name, or click No to rename the version "Race" you're moving or copying."
So I went back and tried to get the data from those cells individually (I was copying via Range.copy of cells). I expected to be able to use something like Range("K15").Value to get the value, but I get an error message that isn't very helpful.
Here's the code I'm working with currently:
Sub PullToSheet()
'We need to get the original sheet open
'We need to define the file for the original sheet in steps
'The original sheet needs to be in the same location as the new format sheet
'OR if the person is knowledgable enough to put the name with full path in the cell, that might work
Dim FileName As String
Dim OrgFile As Workbook
Dim DD As DropDown
ThisWorkbook.Activate 'This assures we're working with the right workbook (file)
Worksheets("There are Hidden Tabs").Select 'This assures we're pulling our filename from the correct worksheet
FileName = Range("C9").Value 'This should be the location of the field with the original character sheet filename
Set OrgFile = Workbooks.Open(FileName)
'We should now have the original character sheet open and flipped to the "Build Sheet"
'Now we need to pull the data from this sheet to the new sheet
'Start by grabbing data from top table
Worksheets("Build Sheet - Start Here").Range("K5:K10").Copy _
Destination:=ThisWorkbook.Worksheets("Build Sheet - Start Here").Range("K5") 'Base Points
Worksheets("Build Sheet - Start Here").Range("O5:O10").Copy _
Destination:=ThisWorkbook.Worksheets("Build Sheet - Start Here").Range("O5") 'Level 5 Boosts
Worksheets("Build Sheet - Start Here").Range("S5:S10").Copy _
Destination:=ThisWorkbook.Worksheets("Build Sheet - Start Here").Range("S5") 'Level 10 Boosts
Worksheets("Build Sheet - Start Here").Range("W5:W10").Copy _
Destination:=ThisWorkbook.Worksheets("Build Sheet - Start Here").Range("W5") 'Level 15 Boosts
Worksheets("Build Sheet - Start Here").Range("Z5:Z10").Copy _
Destination:=ThisWorkbook.Worksheets("Build Sheet - Start Here").Range("Z5") 'Level 20 Boosts
'Now lets grab Baseline Info
Dim Race, AAbility, Theme, Themeless, Class1, Class2, FGroup1, FName1, Archetype, FGroup2, FName2 As String
OrgFile.Activate
Worksheets("Build Sheet - Start Here").Select
Set Race = [E12].Value
Set AAbility = [E13].Value
ThisWorkbook.Activate
Worksheets("Build Sheet - Start Here").Select
Range("E12").Select
ActiveCell.Value = Race
Range("E13").Select
ActiveCell.Value = AAbility
Worksheets("Build Sheet - Start Here").Range("G16:G17").Copy _
Destination:=ThisWorkbook.Worksheets("Build Sheet - Start Here").Range("G16") 'Baseline Info 2nd Class Column
Worksheets("Build Sheet - Start Here").Range("N14:O17").Copy _
Destination:=ThisWorkbook.Worksheets("Build Sheet - Start Here").Range("N14") 'Baseline Info Birth Data
'Worksheets("Build Sheet - Start Here").Range("P15:P17").Copy _
Destination:=ThisWorkbook.Worksheets("Build Sheet - Start Here").Range("P15") 'Player and Character Stats (Pull downs causing issues)
Worksheets("Build Sheet - Start Here").Range("R13:T14").Copy _
Destination:=ThisWorkbook.Worksheets("Build Sheet - Start Here").Range("R13") 'Player and Character Stats
Worksheets("Build Sheet - Start Here").Range("T15:T19").Copy _
Destination:=ThisWorkbook.Worksheets("Build Sheet - Start Here").Range("T15") 'Player and Character Stats
End Sub
I'm also open to suggestions to simplify or compact my code.
Thanks!!
Bookmarks