Worksheet Data Transfer VBA

[pure vito]

Good Morning All,

I have a document that I share with a collectors community, I have created a database of collectibles but will update the workbook from time to time with new items, meaning I will have to release a new version of the workbook for people to download, so I wanted to work on a data transfer method.

I am not an expert in VBA so would appreciate any advise here, I've recoded a simple copy and paste macro to the new workbook, my question is, I would have to replicate this code over 100 times under 1 macro using different sheet names and ranges, before I commit to doing that does it seem like the right approach?

Thanks in advance,

Sub Data_Transfer()
'
' Data_Transfer Macro
'

'
    Windows("Pokemon Collection Tracker Ver 1.2.xlsm").Activate
    Sheets("Base Set").Select
    Range("H2:K103").Select
    Selection.Copy
    Windows("Pokemon Collection Tracker Ver 1.3.xlsm").Activate
    Sheets("Base Set").Select
    Range("H2").Select
    ActiveSheet.Paste
    Windows("Pokemon Collection Tracker Ver 1.3.xlsm").Activate
    Application.CutCopyMode = False
End Sub

 

[James006]

Glad to hear you have managed to fix your problem

 

[pure vito]

Hey @James006 I've just realised this code doesn't allow me to transfer notes as it only transfers the values,

Is it possible to copy and paste the cells as they are with the values, formatting and notes?

 

[James006]

You can test following modification

Sub Data_Transfer215()
' Working with the TWO Workbooks Opened ''''''''
' Identical Worksheet Index in Both Files ''''''
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim i As Long
    For i = 15 To 215
        Set wsCopy = Workbooks("Pokemon Collection Tracker Ver 1.2.xlsm").Sheets(i)
        Set wsDest = Workbooks("Pokemon Collection Tracker Ver 1.3.xlsm").Sheets(i)
        ' Copy All
        wsCopy.Range("H2:K103").Copy Destination:=wsDest.Range("H2")
    Next i
End Sub

 

[pure vito]

That's wonderful thank you James, I was messing with that line but again failed to execute

 

[James006]

You should be proud of all the progress you have made lately ....

Albert Einstein used to say : " All stumbling blocks should be perceived as Stepping Stones towards Progress " !!!

 

[pure vito]

Now that's a line we can all appreciate, Thanks for your kind words of encouragement