Cut and paste range into first available row

ParanoidAndroid

Board Regular
Joined
Jan 24, 2011
Messages
50
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

Code:
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")
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
Another method that will loop through multiple sheets...
Code:
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

Board Regular
Joined
Jan 24, 2011
Messages
50
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

Board Regular
Joined
Jan 24, 2011
Messages
50

ADVERTISEMENT

Thanks Alpha...your the Frog!! :)

I tried the second one and it works a treat.

Very much appreciate your help...
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
The loop only will copy from the sheets in Red. You could add or exclude whatever sheets you want.
Code:
For Each ws In Sheets(Array([COLOR="Red"]"1", "2"[/COLOR]))
 

ParanoidAndroid

Board Regular
Joined
Jan 24, 2011
Messages
50
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,820
Messages
5,598,301
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top