Set Copy rng to two cells instesd of a contiguous range

[L. Howard]

In a For Each c In Range("K2:K100") - Next loop.

How do I write <set i?)="" &="" i?:?C:?="" rngCopy='wrkSheet.wrkSheet.Range("A:"'>in the code below, Set rngCopy = wrkSheet.wrkSheet.Range("A:" & i":"C:" & i")
where Dim i As Long, and i is the row number of the cell in column K of the loop?

Set rngCopy to two (or more) individual cells instead of a contiguous range.

(My example above produces every error message known to Excel 2010)

Set rngCopy = wrkSheet.wrkSheet.Range("A125:Q144")

Regards,
Howard

<tbody>
</tbody>

</set>

 

[kevatarvind]

can you provide your full code what u want to need actual ?

 

[L. Howard]

Since I have the whole code posted, may I add that the line in red is the one I want a couple of cells instead of a whole range, and when I tested it as is i get an error message on the same line Set rngCopy = wrkSheet.wrkSheet.Range("A125:F144") ... "Method or Data Member not found".

There is no column K loop going here, I intended to adapt this code to a series of sheets in a workbook using a loop to look at each cell in column K of several sheets and return some stuff from that row of the loop to a master sheet.

Option Explicit
Sub AllToOne()
'Consolidates data from the range A125:Q144 for every tab except the one it's part of.
    Dim wrkSheet As Worksheet
    Dim rngCopy As Range
    Dim lngPasteRow As Long
    Dim strConsTab As String
    
    Dim i As Long
    
    strConsTab = ActiveSheet.Name 'Consolidation sheet tab name based on active tab.
        
    If Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row >= 2 Then
        If MsgBox("Do you want to clear the existing consolidated data in """ & strConsTab & """", _
          vbQuestion + vbYesNo, "Data Consolidation Editor") = vbYes Then
            Sheets(strConsTab).Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
        End If
    End If
         
    Application.ScreenUpdating = False
            
    For Each wrkSheet In ActiveWorkbook.Worksheets
        
        If wrkSheet.Name <> strConsTab Then
               
            Set rngCopy = wrkSheet.wrkSheet.Range("A125:F144")
            lngPasteRow = Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row + 1
            rngCopy.Copy Sheets(strConsTab).Range("A" & lngPasteRow)
            Application.CutCopyMode = False
            
        End If
    
    Next wrkSheet
    
    Application.ScreenUpdating = True
    
    MsgBox "The workbook data has now been consolidated.", vbInformation, "Data Consolidation Editor"
End Sub

Thanks for taking a look.

Regards,
Howard

 

[John_w]

Set rngCopy = wrkSheet.wrkSheet.Range("A125:F144") ... "Method or Data Member not found".

Because you've put wrkSheet twice. In answer to your OP, try:

        Set rngCopy = wrksheet.Range("A" & c.Row & ":C" & c.Row)

 

[L. Howard]

Hi John,

Set rngCopy = wrkSheet.wrkSheet.Range("A125:F144")

I changed that line to this and eliminated the error. Thanks for that tid bit.
Set rngCopy = wrkSheet.Range("A125:F144")

Now I am puzzled by what the code below is doing. I commented out some lines that are of no intrest to me now.
The code is in Sheet1 vb editor. I have three sheet in the workbook, Sheet 1, 2, & 3.

When I run the code it copies Range("A1:F10") of sheet 1 to sheet 1, one row below the last entry of column A AND to sheet 3 in the same manner AND skips sheet 2.
As I read the code I it should omit copying to itself (sheet 1) and copy to all the other sheets in the workbook, sheet 2 and sheet 3 in this case.

Do you read something else that makes it skip sheet 2 and fails to omit sheet 1?

Seems this line here should make the code NOT copy to the sheet that is running the code.
If wrkSheet.Name <> strConsTab Then

Option Explicit
Sub SheetOneToAll()
    Dim wrkSheet As Worksheet
    Dim rngCopy As Range
    Dim lngPasteRow As Long
    Dim strConsTab As String
    
    Dim i As Long
    
    strConsTab = ActiveSheet.Name 'Consolidation sheet tab name based on active tab.
        
    'If Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row >= 2 Then
    '    If MsgBox("Do you want to clear the existing consolidated data in """ & strConsTab & """", _
    '      vbQuestion + vbYesNo, "Data Consolidation Editor") = vbYes Then
    '        Sheets(strConsTab).Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
    '    End If
    'End If
         
    Application.ScreenUpdating = False
            
    For Each wrkSheet In ActiveWorkbook.Worksheets
        
        If wrkSheet.Name <> strConsTab Then
               
            Set rngCopy = wrkSheet.Range("A1:F10")
            lngPasteRow = Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row + 1
            rngCopy.Copy Sheets(strConsTab).Range("A" & lngPasteRow)
            Application.CutCopyMode = False
            
        End If
    
    Next wrkSheet
    
    Application.ScreenUpdating = True
    'MsgBox "The workbook data has now been consolidated.", vbInformation, "Data Consolidation Editor"
End Sub

Howard

 

[John_w]

Do you read something else that makes it skip sheet 2 and fails to omit sheet 1?

That would happen if Sheet2 is the active sheet. You should activate (select) Sheet1 before running the code if you want it to skip Sheet1.

Seems this line here should make the code NOT copy to the sheet that is running the code.
If wrkSheet.Name <> strConsTab Then

Debug the code and examine the strConsTab variable. Is its value what you expect?

 

[Norie]

To refer to 2 non contigous cells separate them with a comma.

For example.

Range("A1, B10")

 

[L. Howard]

Hi John,

Thanks for taking a look.

I have never learned to do the Debug thing. Perhaps now is a good time to start.

Is it something I will have to read a lot about or can you give me give me two minute lesson on it?

Howard

 

[John_w]

Press F8 key to step through code line by line. F9 to add a breakpoint. F5 to run. Use Debug.Print statements in code to output variables to the Immediate Window (Ctrl+G to show this window). Type ?variableName or ?objectName.property to examine variables, or look in the Locals Window. That's the basics.

Have a look at this tutorial - Debugging Macros

 

[L. Howard]

Thanks John.

I will give it a good look-see.

Regards,
Howaed