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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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)
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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]))
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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
Back
Top