Copy complete range with 1 line of code using currentRegion

[MarkCBB]

Hello VBA pros,

I want to be able to copy a range on sheet2 using .currentRegion but exclude the heading by using OFFSET and then .end(xlup) to un select the blank row at the bottom.

I have a feeling this is possible, below is the line of code that excludes the heading row, but includes the blank row due to the OFFSET.

Sheet2.Range("A1").CurrentRegion.Offset(1, 0).Copy

and here is the code that is not working, that is meant to exclude the blank row:

Sheet2.Range("A1").CurrentRegion.Offset(1, 0) , Sheet2.Range("A1").CurrentRegion.End(xlUp)).copy

Below is the Code that pastes the data to an existing database (no challenges with this code just an fyi):

Sheet1.Range("A" & WorksheetFunction.CountA(Sheet1.Columns("B:B")) + 1).PasteSpecial xlPasteValuesAndNumberFormats

 

[VoG]

Perhaps (untested)

Sheet2.Range("A1").CurrentRegion.Offset(1).Resize(Sheet2.Range("A1").CurrentRegion.Rows.Count - 1).Copy

 

[pgc01]

Hi

Try, similar to Peter's:

With Sheet1.Range("A1").CurrentRegion
    .Resize(.Rows.Count - 1).Offset(1, 0).Copy
End With

 

[MarkCBB]

Hi there VoG,

Yup that works really well, thanks.

Hi pcg01,

That works as well, thank you.



Just out of curiosity, is there away to make the 2 mines of code into 1 line?

Sub test()
Sheet2.Range("A1").CurrentRegion.Offset(1).Resize(Range("A1").CurrentRegion.Rows.Count - 1).Copy
Sheet1.Range("A" & WorksheetFunction.CountA(Sheet1.Columns("B:B")) + 1).PasteSpecial xlPasteValuesAndNumberFormats
End Sub

thanks again you 2 are always very helpfull.

 

[VoG]

With PasteSpecial you need two lines of code - one for the copy and one for the paste.

 

[pgc01]

Hi Mark

You have an error in the copy statement. You forgot the sheet reference:

Sheet2.Range("A1").CurrentRegion.Offset(1).Resize([COLOR=red]Sheet2.[/COLOR]Range("A1").CurrentRegion.Rows.Count - 1).Copy

 

[VoG]

Hi Mark

You have an error in the copy statement. You forgot the sheet reference:

Sheet2.Range("A1").CurrentRegion.Offset(1).Resize([COLOR=red]Sheet2.[/COLOR]Range("A1").CurrentRegion.Rows.Count - 1).Copy

That's my fault. I edited my post to correct that error.

 

[MarkCBB]

Hi there pgc01,

ahh yes, thank you.

 

[taurean]

Combining these two will mean VBA won't do Paste Special but Paste. In the second row of your code if you intend to find the last row where data is filled then following code is better equipped:

Sheet1.Range("A"[I][COLOR=blue] & Rows.Count).End(xlUp)(2).[/COLOR][/I]PasteSpecial xlPasteValuesAndNumberFormats

 

[MarkCBB]

thanks VoG,