Copying many named ranges using for loop

Question

I am going to import some values from one workbook to another.

I want to do this by looping through the named ranges in the target workbook, and copy those named ranges in source workbook with exact similar name. The relevant names to be copied are listed in a worksheet called "Names" in the target workbook.

The actual named ranges are located in a worksheet called TargetSheet (I have given many single cells one name).

I know how to copy one named range from a workbook to another, when typing this, it works:

ThisWorkbook.Sheets("TargetSheet").Range("TagName") = _ Workbooks("Source").Sheets("SourceSheet").Range("TagName").

However, I don't manage to make a loop that copies values from all relevant named ranges, even if I include quotation marks in the string.

For i = 2 To 8
  For j = 1 To 6
    CurrentName = """" & ThisWorkbook.Sheets("Names").Cells(i, j) & """"
    ThisWorkbook.Sheets("Target").Range(CurrentName) = _
      Workbooks("Source").Sheets("SourceSheet").Range(CurrentName)
  Next j
Next i

The code above gives error message 'Application defined or object defined error', while I hoped that some 42 cells would get updated values.

Answer 1

you should have a variable that brings the target table you want to update and then use for example: ra.CurrentDb.OpenRecordset("root_table") re.CurrentDb.OpenRecordset("target_table")

then handling all the code, update the target table doing:

re.AddNew re.Update

Answer 2

Method 2 Copy the data Sheets("Sheet1").Range("A1:B10").Copy Activate the destination worksheet Sheets("Sheet2").Activate Select the target range Range("E1").Select Paste in the target destination ActiveSheet.Paste

this could help you.. like i said im not xpert on excel.. but always there is a begining