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]

Hello,
You can test following

Sub Data_Transfer()
' Working ithe the TWO workbooks Opened ''''''''
Dim wsh1 As Worksheet
Dim wsh2 As Worksheet

Set whs1 = Workbooks("Pokemon Collection Tracker Ver 1.2.xlsm").Sheets("Base Set")
Set whs2 = Workbooks("Pokemon Collection Tracker Ver 1.3.xlsm").Sheets("Base Set")

wsh2.Range("H2:K103").Value = wsh1.Range("H2:K103").Value
    
End Sub

 

[pure vito]

Thank you James that's a great start and if I wanted to add 200+ sheets would that be possible? it would be the same range for every sheet.

 

[pure vito]

Tested it, doesn't seem to work James?

 

[pure vito]

This seems to work but I'm still unsure on how to ad 200+ sheets to this

Change to

Sub Data_Transfer1()
' Working ithe the TWO workbooks Opened ''''''''
Dim wsCopy As Worksheet
Dim wsDest As Worksheet

Set wsCopy = Workbooks("Pokemon Collection Tracker Ver 1.2.xlsm").Sheets("Base Set")
Set wsDest = Workbooks("Pokemon Collection Tracker Ver 1.3.xlsm").Sheets("Base Set")

wsDest.Range("H2:K103").Value = wsCopy.Range("H2:K103").Value
    
End Sub

 

[pure vito]

Should I continue this way for 200+ sheets?

Sub Data_Transfer1()
' Working ithe the TWO workbooks Opened ''''''''
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim wsCopy1 As Worksheet
Dim wsDest1 As Worksheet
Dim wsCopy2 As Worksheet
Dim wsDest2 As Worksheet

Set wsCopy = Workbooks("Pokemon Collection Tracker Ver 1.2.xlsm").Sheets("Base Set")
Set wsDest = Workbooks("Pokemon Collection Tracker Ver 1.3.xlsm").Sheets("Base Set")
Set wsCopy1 = Workbooks("Pokemon Collection Tracker Ver 1.2.xlsm").Sheets("Expansion Pack-JP")
Set wsDest1 = Workbooks("Pokemon Collection Tracker Ver 1.3.xlsm").Sheets("Expansion Pack-JP")
Set wsCopy2 = Workbooks("Pokemon Collection Tracker Ver 1.2.xlsm").Sheets("Jungle")
Set wsDest2 = Workbooks("Pokemon Collection Tracker Ver 1.3.xlsm").Sheets("Jungle")


wsDest.Range("H2:K500").Value = wsCopy.Range("H2:K500").Value
wsDest1.Range("H2:K500").Value = wsCopy1.Range("H2:K500").Value
wsDest2.Range("H2:K500").Value = wsCopy2.Range("H2:K500").Value
    
    
    
End Sub

 

[James006]

A loop would simplify your process

Sub Data_Transfer200()
' 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 = 1 To 200
        Set wsCopy = Workbooks("Pokemon Collection Tracker Ver 1.2.xlsm").Sheets(i)
        Set wsDest = Workbooks("Pokemon Collection Tracker Ver 1.3.xlsm").Sheets(i)
        wsDest.Range("H2:K103").Value = wsCopy.Range("H2:K103").Value
    Next i
End Sub

 

[pure vito]

Thanks James that looks so much more convenient to what I've started

However the Workbook has over 400 sheets so I guess I need to be careful as to what sheets the vba works with

This code here, does it mean the first sheet up to sheet 200

For i = 1 To 200

so If the sheets I want to transfer are sheets 15 to 215 then, change

For i = 15 To 215

Is that correct and thanks again for your time James

 

[James006]

You are right !!!

Make sure both workbooks are identical .... i.e. the sequence of the worksheets is similar ... so that you can safely use the Index ...

 

[pure vito]

Awwww That's a dream! works perfectly thank you so much James you've been a real help today