Cut and paste range into first available row

[ParanoidAndroid]

Hi Guys

I would be most appreciative if someone could advise what is wrong with my code.

I want to cut from the 2nd row to the last used row in sheet1 and paste it into the first unused row in "master" sheet.

Then repeat the process with a different sheet (sheet2) into the master sheet.

Obviously im doing something wrong

Dim LastRowMaster As Integer
Dim LastRow1 As Integer
Dim LastRow2 As Integer

' below - find the last used row for sheet 1 and 2, and first available row 'for Master sheet
 
LastRowMaster = Sheets("Master").Range("A65536").End(xlUp).Row + 1 
LastRow1 = Sheets("1").Range("A65536").End(xlUp).Row  
LastRow2 = Sheets("2").Range("A65536").End(xlUp).Row 
 
'below - cut from row 2 until last used row in Sheet 2 and paste in first 'available row in Master sheet
 
Sheets("1").Range(2:LastRow1).Copy Destination:=Worksheets("Master").Range("LastRowMaster") 
 
' below Re-determine first available row in Master sheet
 
LastRowMaster = Sheets("Master").Range("A65536").End(xlUp) + 1  
 
'Copy row 2 until last used row in Sheet 2 and paste into first available 'row in master sheet
 
Sheets("2").Range("A2:LastRow2).Copy Destination:=Worksheets("Master").Range("LastRowMaster")

 

[AlphaFrog]

Dim LastRowMaster As Long
Dim LastRow1 As Long
Dim LastRow2 As Long

' below - find the last used row for sheet 1 and 2, and first available row 'for Master sheet
 
LastRowMaster = Sheets("Master").Range("A65536").End(xlUp).Row + 1
LastRow1 = Sheets("1").Range("A65536").End(xlUp).Row
LastRow2 = Sheets("2").Range("A65536").End(xlUp).Row
 
'below - cut from row 2 until last used row in Sheet 2 and paste in first 'available row in Master sheet
 
Sheets("1").Range("2:" & LastRow1).Copy Destination:=Worksheets("Master").Range("A" & LastRowMaster)
 
' below Re-determine first available row in Master sheet
 
LastRowMaster = Sheets("Master").Range("A65536").End(xlUp).Row + 1
 
'Copy row 2 until last used row in Sheet 2 and paste into first available 'row in master sheet
 
Sheets("2").Range("2:" & LastRow2).Copy Destination:=Worksheets("Master").Range("A" & LastRowMaster)

 

[AlphaFrog]

Another method that will loop through multiple sheets...

Dim LastRowMaster As Range
Dim rng As Range, ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Sheets(Array("1", "2"))
    
    Set LastRowMaster = Sheets("Master").Range("A65536").End(xlUp).Offset(1) ' Next empty row on Master
    
    With ws
        Set rng = .Range("A2", .Range("A65536").End(xlUp)).EntireRow
        rng.Copy Destination:=LastRowMaster
    End With
    
Next ws

Application.ScreenUpdating = True

 

[ParanoidAndroid]

Thanks Mate..

I will try the second suggestion with the loop...How do i put an exeption so it doesnt touch another sheet - lets call it sheet 3 and sheet 4?

 

[ParanoidAndroid]

Thanks Alpha...your the Frog!!

I tried the second one and it works a treat.

Very much appreciate your help...

 

[AlphaFrog]

The loop only will copy from the sheets in Red. You could add or exclude whatever sheets you want.

For Each ws In Sheets(Array([COLOR="Red"]"1", "2"[/COLOR]))

 

[ParanoidAndroid]

Hi Alpha

Is this loop system you created in the second exmaple restricted to checking the first 300 rows?

It does everything perfectly until row 300 (ie..it transfers those entried that meet the condition and ignores those entries that dont meet the condition" until row 300 or 299 then executes the error message followed by a "400" message.

Any idea?

 

[AlphaFrog]

Is this loop system you created in the second exmaple restricted to checking the first 300 rows?

No it's not limited to 300 rows.

It does everything perfectly until row 300 (ie..it transfers those entried that meet the condition and ignores those entries that dont meet the condition" until row 300 or 299 then executes the error message followed by a "400" message.

What condition?