Copying and pasting named range, edit formulas in range and paste back

[MrJimmy]

Hi there!

I'm quite new to VBA and I don't know that much programming or coding otherwise, besides some MATLAB-coding.

The problem/question;

I had a workbook on 5000 rows with around 120 smaller "tables"(not formatted as tables, just formatted as named ranges). I have now split up these named ranges/tables to 8-9 different sheets, to make it a bit easier to navigate. Then I also wanted to add an easy way to look at any table/named range in the workbook, so I've created a sheet called "table-comparison" or similar. The idea is to be able to copy any named range from any of the sheets by choosing them from a list and then be able to make Changes to the Formulas in the range and then paste them back.

I have the foundation built up, but I know it can be much more refined.. At the moment I have a separate sheet with settings, where I write my named ranges exact names for the list to pick up. The copying goes very fine, but I'm now trying to figure out a good way to paste back the range to it's correct position. (Worth mentioning is that right now I have it made so I can choose 2 tables (chosen in 2 separate lists) so I can compare tables from any sheets. There is a separate code for the other table which is copied to "Z3".)

Global table As Integer
Global list
Global rows as integer
Global columns as integer
Public adress As String
Sub Tablecomparer()
'There is a separate list for choosing which sheet to access tables, so the list won't be 100 entries long


table = Sheets("Settings").Range("L5").Value 'Chosen range to copy
list = Sheets("Settings").Range("L6:L30").Value 'All available ranges to choose from

    Range("A3:Y181").Select    
    Selection.Delete Shift:=xlUp   
    Application.Range(list(table, 1)).Copy
    Sheets("Tablecomparer").Select
    Range("A3").Select
    ActiveSheet.Paste
    adress = Application.ActiveWorkbook.Names(list(table, 1)).RefersTo ' Gives the adress of the copied range e.g "Fuel!A1:A5"
        Rows = Application.Range(list(table, 1)).Rows.Count 
    Columns = Application.Range(list(table, 1)).Columns.Count 
    
End Sub

Sub pasteingback()
    Dim bladet2 As String
    adress = Application.ActiveWorkbook.Names(list(table, 1)).RefersTo
    place = InStr(adress, "!")
    Sheet = Left(adress, place - 1)
    Sheet2 = Replace(Sheet, "'", "")
    Cell = Right(adress, Len(adress) - Len(Sheet) - 1)
    Range("A3").Activate
    ActiveCell.Resize(rows, columns).Copy
    
     'Here I'm not sure how to go back to the location and paste back..I also would like some error-message if there is nothing to paste back, so a table won't be replaced by empty cells. Or possible a yes or no box that comes up, as an extra check for overwriting the existing table/named range.

End Sub

Happy for any help! Thanks.

 

[MrJimmy]

Noticied that I'm unable to edit post.. Note that "dim bladet2 as string" should be "dim sheet2 as string".. I translated my Swedish terms to English, but missed that one.