Copy Data From ranges without headers from multiple worksheets

[brian sullivan]

Hello Chandoo Community,

I'm curious in knowing what the best practice is for copying data ranges (identical in multiple worksheets) without headers from multiple worksheets (into a master spreadsheet). I'm essentially trying to have a solid starting point in consolidating various, but identical worksheets into a master one. Please let me know, what the best approach is here with either formulas, or a marco.

Thanks.

 

[chirayu]

You don't need headers to copy as long as you know which range / column you're copying from e.g.

Range("A1:F10").Copy

 

[Deepak]

Hi,

There are several ways to do the same depending upon the data layout!!

here's some...

Range("A1").CurrentRegion.Offset(1).Copy

Range("A1:D10").Offset(1).Copy

 

[brian sullivan]

Thanks for the response here. I think that formula would work without a doubt.

 

[brian sullivan]

Hi,

There are several ways to do the same depending upon the data layout!!

here's some...

Range("A1").CurrentRegion.Offset(1).Copy

Range("A1:D10").Offset(1).Copy

What is the best code approach in VB when copying data ranges from sheet into another?

 

[Deepak]

What is the best code approach in VB when copying data ranges from sheet into another?

As per me.
Best approach would be as per data layout.
Can we have the sample file pls.

 

[chirayu]

Generally I prefer using Paste Special. Instead of direct paste & the format the data afterwards. But it depends on your preference.

The Code would be something like the below:

Sub Test()
Worksheets("Sheet1").Select
Range("A1:F10").Copy
Worksheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial xlPasteValues

Please note that lets say you're copying from somewhere & you're pasting under existing data in a different sheet. You'll need to use a code snippet that finds the last non blank cell in the column so you don't overwrite your existing data.

Code Example below:

Sub Test()
Worksheets("Sheet1").Select
Range("A1:F10").Copy
Worksheets("Sheet2").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Activecell.Offset(1,0).Select
Selection.PasteSpecial xlPasteValues

 

[chirayu]

Forgot to write End Sub at the bottom of each sample code

 

[Marc L]

Hi !

An efficient code must not use awful Activate & Select ‼

chirayu samples in an efficient way :​

Sub Test1()
Range("Sheet1!A1:F10").Copy
Range("Sheet2!A1").PasteSpecial xlPasteValues
End Sub


Sub Test2()
Range("Sheet1!A1:F10").Copy
Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End Sub

♪ My way ♫ :​

Sub Demo1()
Range("Sheet2!A1:F10").Value = Range("Sheet1!A1:F10").Value
End Sub


Sub Demo2()
VA = Sheet1.[A1:F10].Value
Sheet2.Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(VA), UBound(VA, 2)).Value = VA
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

With same columns order between worksheets,
updating sheet 2 from sheet 1 with continuous data
(no entire blank column or row) starting from A1 cell :​

Sub Demo3()
Sheet1.Cells(1).CurrentRegion.Offset(1).Copy Sheet2.Cells(1).End(xlDown).Offset(1)
End Sub

You like ? So thanks to …​