Change Data of Range().Value Reference after Copy and Paste Hardcoded Data from Table-CodePudding

I wish to create a VBA command that will reference range values using variables and then copy and paste to those files mentioned in the ranges. The command will pull from a table and put it in the reference range for the copy and paste command. I am having issues getting it to work past the first reference. I tried adding save commands and wait time commands to see if that would do anything, but no progress has been made.

Images:

Data Reference for Copy and Paste

Table that Will Replace Data on First Picture

Debugging Showing that Reference Data Won't Change Even if Data in Range Does

This is what I have so far:


Public Sub Changing_Variable_Loop()
'--Variables
CopyFile = Range("A1").Value
CopyWksht = Range("B1").Value
CopyRange = Range("C1").Value

PasteFile = Range("D1").Value
PasteWksht = Range("E1").Value
PasteRange = Range("F1").Value

'--Change Range("").Value Reference Data in Excel Sheet (1)
Workbooks("VBA Command.xlsm").Activate
Range("A1").Select

Workbooks("VBA Command.xlsm").Worksheets("Pull").Range("A1:F1").Copy _
Workbooks("VBA Command.xlsm").Worksheets("Post").Range("A1:F1")

Workbooks("VBA Command.xlsm").Activate
ActiveWorkbook.Save

Application.Wait (Now   TimeValue("00:00:05"))

'--Copy and Paste for Varying Files based on Range("").Value Reference Data(1)

Workbooks(CopyFile).Worksheets(CopyWksht).Range(CopyRange).Copy _
Workbooks(PasteFile).Worksheets(PasteWksht).Range(PasteRange)


'--Change Range("").Value Reference Data in Excel Sheet (2)
Workbooks("VBA Command.xlsm").Worksheets("Pull").Range("A2:F2").Copy _
Workbooks("VBA Command.xlsm").Worksheets("Post").Range("A1:F1")

Workbooks("VBA Command.xlsm").Activate
ActiveWorkbook.Save

Application.Wait (Now   TimeValue("00:00:05"))

'--Copy and Paste for Varying Files based on Range("").Value Reference Data(2)

Workbooks(CopyFile).Worksheets(CopyWksht).Range(CopyRange).Copy _
Workbooks(PasteFile).Worksheets(PasteWksht).Range(PasteRange)


'--Change Range("").Value Reference Data in Excel Sheet (3)
Workbooks("VBA Command.xlsm").Worksheets("Pull").Range("A3:F3").Copy _
Workbooks("VBA Command.xlsm").Worksheets("Post").Range("A1:F1")

Workbooks("VBA Command.xlsm").Activate
ActiveWorkbook.Save

Application.Wait (Now   TimeValue("00:00:05"))

'--Copy and Paste for Varying Files based on Range("").Value Reference Data(3)

Workbooks(CopyFile).Worksheets(CopyWksht).Range(CopyRange).Copy _
Workbooks(PasteFile).Worksheets(PasteWksht).Range(PasteRange)

MsgBox ("Successful")
End Sub

CodePudding user response：

I think your code could be a lot simpler - more like this:

Public Sub RunCopyPasteSettings()
    
    Dim wsSettings As Worksheet, i As Long, rw As Range
    
    Set wsSettings = ThisWorkbook.Sheets("Settings") 'or whatever
    
    Set rw = wsSettings.Range("A1:F1") 'your first row of copy/paste settings
    
    For i = 1 To 3 'or however many rows of settings you have
        
        Workbooks(rw.Cells(1).Value).Worksheets(rw.Cells(2).Value). _
            Range(rw.Cells(3).Value).Copy _
            Workbooks(rw.Cells(4).Value).Worksheets(rw.Cells(5).Value). _
            Range(rw.Cells(6).Value)
    
        Set rw = rw.Offset(1) 'next row of copy/paste range settings
        
    Next i

End Sub