Copy information from each worksheet and paste it all in one worksheet.

[finaljustice]

Hello all,


I have 174 text files that I was able to import into a workbook using VBA. Now my workbook has 174 sheets with the information I need, but I need to compile all that information that is fragmented in many worksheets into one single worksheet. I thought this would be a straight forward code but I keep getting an error.

Sub load_db()

For n = 2 To Worksheets.Count

Sheets(n).Range(Cells(11, 1), Cells(1048576, 1).End(xlUp).Offset(-4, 0)).Copy
Sheets("ALL DB").Cells(1048576, 1).End(xlUp).Offset(1, 0).Paste
s
Next

End Sub

What I realized is that this line works if I select the sheet it is actively copying:

Sheets(n).Range(Cells(11, 1), Cells(1048576, 1).End(xlUp).Offset(-4, 0)).Copy

I am also having issues pasting the information in the "ALL DB" sheet, all this avoiding selecting sheets and activecells, trying to keep my coding as clean as possible. But... apparently I'm missing something very basic.


Thank you for your time and insight.
LF.

 

[Fluff]

For the 1st part

With Sheets(n)
   .Range(.Cells(11, 1), .Cells(Rows.Count, 1).End(xlUp).Offset(-4, 0)).Copy
End With

You need to qualify the ranges. (note the . in front of Cells)
And for part 2 try

With Sheets(n)
   .Range(.Cells(11, 1), .Cells(Rows.Count, 1).End(xlUp).Offset(-4, 0)).Copy _
      Sheets("ALL DB").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

 

[finaljustice]

Thank you for your response!

Can you please give me a little bit of explaining regarding the use of the .cells? What do you mean by "you need to qualify the ranges"?

Thank you once again for your time.

For the 1st part

With Sheets(n)
   .Range(.Cells(11, 1), .Cells(Rows.Count, 1).End(xlUp).Offset(-4, 0)).Copy
End With

You need to qualify the ranges. (note the . in front of Cells)
And for part 2 try

With Sheets(n)
   .Range(.Cells(11, 1), .Cells(Rows.Count, 1).End(xlUp).Offset(-4, 0)).Copy _
      Sheets("ALL DB").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

 

[Fluff]

In your code the 2 parts in red are looking at the active sheet, as you haven't qualified them(ie you have not specified which sheet to look at)

Sheets(n).Range([COLOR=#ff0000]Cells(11, 1)[/COLOR], [COLOR=#ff0000]Cells(1048576, 1).End(xlUp).Offset(-4, 0)[/COLOR]).Copy

One way of correcting that is to do this is

Sheets(n).Range(Sheets(n).Cells(11, 1), Sheets(n).Cells(1048576, 1).End(xlUp).Offset(-4, 0)).Copy

or you can use the "shortcut" I used.

 

[finaljustice]

In your code the 2 parts in red are looking at the active sheet, as you haven't qualified them(ie you have not specified which sheet to look at)

Sheets(n).Range([COLOR=#ff0000]Cells(11, 1)[/COLOR], [COLOR=#ff0000]Cells(1048576, 1).End(xlUp).Offset(-4, 0)[/COLOR]).Copy

One way of correcting that is to do this is

Sheets(n).Range(Sheets(n).Cells(11, 1), Sheets(n).Cells(1048576, 1).End(xlUp).Offset(-4, 0)).Copy

or you can use the "shortcut" I used.

Hi there,

I've been trying to implement this "shortcut" you presented above.

I am having an issues, I can't paste on the desired IIF_DB sheet and it seems that the "IF" isn't "looking" properly at the Nth sheet. It only seems to work if I have the Nth sheet active when debugging with F8 for both copying and the pasting says "Object doesn't support this property or method" error.

Sub load_iifdb()


For n = 6 To Worksheets.Count


    With Sheets(n)
    'detemrmine the Lbound for the sheet
        Lbnd = .Range(.Cells(1048576, 1), .Cells(Rows.Count, 1)).End(xlUp).Offset(1, 0).Row
    'upperbound it pre-established, row = 3
        Ubnd = 3
    'LOWER & UPPER BOUND ESTABLISHED, ALL DATA WILL COME FROM BETWEEN THESE BOUNDARIES
        
        For x = Ubnd To Lbnd
            With Sheets(n)
            If Cells(x, 1) = "TRNS" Then
                'Range(Cells(1048576, ActiveCell.Column).End(xlUp), Cells(ActiveCell.Row, 256).End(xlToLeft)).Select
                .Range(.Cells(x, 1), .Cells(x, 256).End(xlToLeft)).Copy
                Sheets("IIF_DB").Cells(1048576, 1).End(xlUp).Offset(1, 0).Paste
                
                
                
            End If
            End With
        
        Next
        
        
    End With
Next


End Sub

 

[Fluff]

Try

Sub load_iifdb()


For n = 6 To Worksheets.Count


    With Sheets(n)
    'detemrmine the Lbound for the sheet
        Lbnd = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    'upperbound it pre-established, row = 3
        Ubnd = 3
    'LOWER & UPPER BOUND ESTABLISHED, ALL DATA WILL COME FROM BETWEEN THESE BOUNDARIES
        
        For x = Ubnd To Lbnd
            If .Cells(x, 1) = "TRNS" Then
                .Range(.Cells(x, 1), .Cells(x, Columns.Count).End(xlToLeft)).Copy _
                Sheets("IIF_DB").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            End If
        Next
        
        
    End With
Next


End Sub

 

[finaljustice]

Try

Sub load_iifdb()


For n = 6 To Worksheets.Count


    With Sheets(n)
    'detemrmine the Lbound for the sheet
        Lbnd = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    'upperbound it pre-established, row = 3
        Ubnd = 3
    'LOWER & UPPER BOUND ESTABLISHED, ALL DATA WILL COME FROM BETWEEN THESE BOUNDARIES
        
        For x = Ubnd To Lbnd
            If .Cells(x, 1) = "TRNS" Then
                .Range(.Cells(x, 1), .Cells(x, Columns.Count).End(xlToLeft)).Copy _
                Sheets("IIF_DB").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            End If
        Next
        
        
    End With
Next


End Sub

Thank you for the response.

Why is it that it is not required the .paste at the end of the code below? The below "_ " is just a line break correct?

_
                Sheets("IIF_DB").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

 

[Fluff]

The below "_ " is just a line break correct?

That's correct.
When using copy you can either do

Range.copy
Range.pastespecial

or

Range.Copy Destination:= Range

Which is what I used (you don't need to include the Destination:= part)

 

[finaljustice]

Hi there,


I would like to understand why is it that I am having two different "behaviors" for the same line of code.

On the bellow code, the range I'm trying to copy an entire range but it is only copying the first column.

Sub load_db()


Application.ScreenUpdating = False


For n = 2 To Worksheets.Count




    With Sheets(n)
    'detemrmine the Lbound for the sheet
        Lbnd = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    'upperbound it pre-established, row = 3
        Ubnd = 2
    'LOWER & UPPER BOUND ESTABLISHED, ALL DATA WILL COME FROM BETWEEN THESE BOUNDARIES
        Range(.Cells(Ubnd, 1), .Cells(Lbnd, Columns.Count).End(xlToLeft)).Copy _
        Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
       'Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        
    End With
Next


Application.ScreenUpdating = True


End Sub

While if I run this simplified code just to check the syntax and the desired range is being selected and copied.

Sub test_2()


Lbnd = 84
Ubnd = 2


Sheets(2).Range(Cells(Ubnd, 1), Cells(Lbnd, Columns.Count).End(xlToLeft)).Select
Sheets(2).Range(Cells(Ubnd, 1), Cells(Lbnd, Columns.Count).End(xlToLeft)).Copy


End Sub

I'm trying to understand why I'm getting two different behaviors for what I understand seems to be the same line of code, the difference is the FOR / NEXT loop.

Thank you for assistance and sorry to keep going back at this, I thought I had understood but apparently I'm missing something.

Best regards,
LF.

 

[Fluff]

On this line

Lbnd = .Range("A" & Rows.Count).End(xlUp)[COLOR=#ff0000].Offset(1, 0)[/COLOR].Row

Remove the part in red & it should work.
Lbnd is the last row +1 so when you then do

Columns.Count).End(xlToLeft)

you're working on an empty row, so it will return column 1