CODEWITHSUNDEEP
vbaexcelloops
benvas
benvas

Reputation: 1

Copy/Paste VBA in increments

I was thinking of creating a loop for the following part of the code:

InvestorMaterialDataCrypto001.Range(InvestorMaterialDataCrypto001.Cells(TableFirstRow, TableFirstColumn), InvestorMaterialDataCrypto001.Cells(TableLastRow_Crypto001, TableLastColumn)).Copy
    HistoricalSheet.Cells(InputReferenceRow_002, InputReferenceColumn + (CountColumns * 0)).PasteSpecial Paste:=xlPasteValues
InvestorMaterialDataCrypto002.Range(InvestorMaterialDataCrypto002.Cells(TableFirstRow, TableFirstColumn), InvestorMaterialDataCrypto002.Cells(TableLastRow_Crypto002, TableLastColumn)).Copy
    HistoricalSheet.Cells(InputReferenceRow_002, InputReferenceColumn + (CountColumns * 1)).PasteSpecial Paste:=xlPasteValues
InvestorMaterialDataCrypto003.Range(InvestorMaterialDataCrypto003.Cells(TableFirstRow, TableFirstColumn), InvestorMaterialDataCrypto003.Cells(TableLastRow_Crypto003, TableLastColumn)).Copy
    HistoricalSheet.Cells(InputReferenceRow_002, InputReferenceColumn + (CountColumns * 2)).PasteSpecial Paste:=xlPasteValues
InvestorMaterialDataCrypto004.Range(InvestorMaterialDataCrypto004.Cells(TableFirstRow, TableFirstColumn), InvestorMaterialDataCrypto004.Cells(TableLastRow_Crypto004, TableLastColumn)).Copy
    HistoricalSheet.Cells(InputReferenceRow_002, InputReferenceColumn + (CountColumns * 3)).PasteSpecial Paste:=xlPasteValues
InvestorMaterialDataCrypto005.Range(InvestorMaterialDataCrypto005.Cells(TableFirstRow, TableFirstColumn), InvestorMaterialDataCrypto005.Cells(TableLastRow_Crypto005, TableLastColumn)).Copy
    HistoricalSheet.Cells(InputReferenceRow_002, InputReferenceColumn + (CountColumns * 4)).PasteSpecial Paste:=xlPasteValues
InvestorMaterialDataCrypto006.Range(InvestorMaterialDataCrypto006.Cells(TableFirstRow, TableFirstColumn), InvestorMaterialDataCrypto006.Cells(TableLastRow_Crypto006, TableLastColumn)).Copy
    HistoricalSheet.Cells(InputReferenceRow_002, InputReferenceColumn + (CountColumns * 5)).PasteSpecial Paste:=xlPasteValues

The issue is that I cannot find a solution to increment the 'worksheet' names/variables & the (CountColumns * 1) portion.

Would be helpful to get this code more optimized...

The total code is:

Dim FilePath As String: FilePath = InvestorMaterialDataFilePathParsed
Dim InvestorMaterialData As Workbook
Set InvestorMaterialData = Workbooks.Open(Filename:=FilePath)
Set InvestorMaterialDataDashboardSheet = InvestorMaterialData.Worksheets("Dashboard")
Set InvestorMaterialDataTop30Sheet = InvestorMaterialData.Worksheets("Top30")
Set InvestorMaterialDataCrypto001 = InvestorMaterialData.Worksheets("CryptoAsset001")
Set InvestorMaterialDataCrypto002 = InvestorMaterialData.Worksheets("CryptoAsset002")
Set InvestorMaterialDataCrypto003 = InvestorMaterialData.Worksheets("CryptoAsset003")
Set InvestorMaterialDataCrypto004 = InvestorMaterialData.Worksheets("CryptoAsset004")
...
TableFirstColumn = InvestorMaterialDataCrypto001.Range("A1").Column
TableLastColumn = InvestorMaterialDataCrypto001.Range("A1").End(xlToRight).Column
TableFirstRow = InvestorMaterialDataCrypto001.Range("A1").Row
TableLastRow_Crypto001 = InvestorMaterialDataCrypto001.Range("A1").End(xlDown).Row
TableLastRow_Crypto002 = InvestorMaterialDataCrypto002.Range("A1").End(xlDown).Row
TableLastRow_Crypto003 = InvestorMaterialDataCrypto003.Range("A1").End(xlDown).Row
TableLastRow_Crypto004 = InvestorMaterialDataCrypto004.Range("A1").End(xlDown).Row


and the top part of the loop...

Upvotes: 0

Views: 70

Answers (1)

CMArg
CMArg

Reputation: 1567

Did not read your question/code in full. But since you are not getting any response after three hours, I dare to post a hint that might be useful (or completely wrong if I misunderstood your post). Copy to a new workbook with at least three sheets ("Sheet1", "Sheet2" and "Sheet3") and give it a try.

Sub Trial()
    Dim i As Integer
    Dim TempName1 As String
    Dim TempName2 As String

    For i = 1 To 3
        'TempName1 = "InvestorMaterialDataCrypto00" & i
        TempName1 = "Sheet" & i
        TempName2 = "TableLastRow_Crypto00" & i

        'here do your stuff. Next line just as an example
        Worksheets(TempName1).Cells(i, 1) = TempName2
    Next

End Sub

Upvotes: 1

Related Questions