Fix loop for cell reference totally stuck

I am trying to look through worksheets copying one area to the sheet and then onto the next sheet.
My problem is that the copying cell references are all on a table on sheet1.

Tried to put this together, my excel habits of nesting may be getting in the way. Can I receive assistance?

For x = 3 To Sheets.Count 
        Worksheets("sheet" & x).Activate 
        Range(Sheet1.Range(.Cells((42 + x - 3), 6).Value, .Cells((42 + x - 3), 7).Value)).Copy 
        Sheets(CStr(rSheets(x, 1))).Cells(3, CStr(rSheets(x, 2))).PasteSpecial xlValues 
        Sheets(CStr(rSheets(x, 1))).Cells(3, CStr(rSheets(x, 2))).PasteSpecial xlPasteColumnWidths 
    Next x

Re: Fix loop for cell reference totally stuck

Awesome.. here is the workbook. there are more sheets that I removed to keep cleaner if possible
Ken

Re: Fix loop for cell reference totally stuck

I uploaded the file. I have names on all of the sheets, so decided to just call them by Sheet1, 2, etc. You are allowed to do that, right?

Re: Fix loop for cell reference totally stuck

Quote from tjrt;773927

Working with Tables sometimes require different methods. Can you supply a working sample of your Workbook?

I have, thank you.

Re: Fix loop for cell reference totally stuck

Sheet1 is actually named USD.
It has a table on it (each column is a calculated reference to a Cell on another Sheet)
for sheets 3 to 15, I have to copy four columns from sheets 3 to 15, and paste to other columns on sheets 3 to 15.
The references of what columns to copy and where to paste are on this table on Sheet1.
Doing this is the crux of the problem.
Ken

Table on Sheet1.

[TABLE="width: 422"]

Sheet #

Sheet

[TD="colspan: 2"]Dependent of this sheet[/TD]

NAME

Copy to

[TD="colspan: 2"]Copy ranges on sheets[/TD]

on sheets

3

ARS

PX

PT1

PW18

4

AUD

PX

PT1

PW18

5

BRL

QA

PW1

PZ18

6

CAD

PZ

PV1

PY18

7

CNY

PU

PQ1

PT18

8

EUR

PU

PQ1

PT18

9

JPY

PU

PQ1

PT18

10

MXN

PU

PQ1

PT18

11

NZD

PU

PQ1

PT18

12

RUB

PU

PQ1

PT18

13

SEK

PU

PQ1

PT18

14

ZAR

QA

PW1

PZ18

15

INR

PU

PQ1

PT18

[/TABLE]

Re: Fix loop for cell reference totally stuck

norie,
I tried to post my answer to the thread.
Thank you.

Re: Fix loop for cell reference totally stuck

Can someone pick up the challenge? The format for the line in question is my hurdle.
Ken

Re: Fix loop for cell reference totally stuck

Can you take another look?

latest effort for this..

Private Sub CommandButton3_Click()
'2.
      Dim r As Long, rSheets As Range, rData As Range
'4.
      With Sheets("USD")
          Set rSheets = .Range("D40").CurrentRegion
          Set rData = .Range(.Range("E28").Value, .Range("E29").Value)
          For r = 1 To rSheets.Rows.Count
              rData.Copy
             Sheets(CStr(rSheets(r, 4))).Cells(3, CStr(rSheets(r, 6))).PasteSpecial xlValues
             Sheets(CStr(rSheets(r, 4))).Cells(3, CStr(rSheets(r, 6))).PasteSpecial xlPasteColumnWidths
         Next r
     End With
'14.
End Sub

What it does is copy from current sheet onto the other sheets.
That is not what I want.
I want to copy from sheet 3 (an area per the chart on sheet 1) to an adjacent area on sheet 3,
then do it again, from sheet 4, 5, 6 etc. through 15.
Cannot figure, and already uploaded worksheet.
Ken