Copy range of cells defined in one sheet to other worksheets in a loop

Ken Santopietro · Jul 12th 2016

I had to go back here again on this problem.
I wrote something that worked but when tested I was posting the same data to multiple sheets. Ugh.

I have a sheet with a table of ranges that is dependent on information on that sheet. It defines the areas that I need copied in other sheets.
On the other sheets, starting with sheet3, I will copy a selection based upon the table on sheet1. Then copy that information to a location on sheet3 based upon a table on sheet1.
Then I look through sheet3 to the end, doing the same.

If necessary I will post the entire worksheet, but its kind of sloppy.
Here is my code - stops right after the loop begins. I tried to define the selection to copy from sheet3 based on information from sheet1... I tried to nest it. Unsuccessful.

Code

Private Sub CommandButton3_Click()
' Used help of Ozgrid to do this!
      Dim r As Long, rSheets As Range, rData As Range
' loop through data sheets copying an area of known data
      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(r, 1))).Cells(3, CStr(rSheets(r, 2))).PasteSpecial xlValues
             Sheets(CStr(rSheets(r, 1))).Cells(3, CStr(rSheets(r, 2))).PasteSpecial xlPasteColumnWidths
      Next x
      '  Bring back to main screen.
Sheets("USD").Activate
Range("D29").Select
Application.CutCopyMode = False
End Sub

Display More

Thank you!

Ken Santopietro · Jul 12th 2016

Re: Copy range of cells defined in one sheet to other worksheets in a loop

I am posting the worksheet if it helps.

Ken Santopietro · Jul 12th 2016

Re: Copy range of cells defined in one sheet to other worksheets in a loop

I re-read and noticed that my reference to r should now be x in two lines. Still won't work though. "unqualified reference"
Ken

Ken Santopietro · Jul 13th 2016

Re: Copy range of cells defined in one sheet to other worksheets in a loop

I made some changes in the code. No better results. Dead stuck.

Code

Private Sub CommandButton3_Click()
' Used help of Ozgrid to do this!
      Dim r As Long, rSheets As Range, rData As Range
' loop through data sheets copying an area of known data
      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
      '  Bring back to main screen.
Sheets("USD").Activate
Range("D29").Select
Application.CutCopyMode = False
End Sub

Display More

Copy range of cells defined in one sheet to other worksheets in a loop

Participate now!

Share