Copy and paste date from one column to other except last row

[Zain_inout]

Hi,

I am working on a macro that will have different file to work with every time but all the files will have the same format as follows:

1) Row 1 will be merged from column A to Z
2) Row 2 will be merged from column A to Z
3) Row 3 will be merged from column A to Z
4) Row 4 will be blank
5) Row 5 will have headers
6) Data will start row 6 onwards
7) Last row will be merged from column A to Z and it will have the totals

Now, I would like to copy data from column A to column B except the last row because that is merged anyway. Below is my code:

Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
Range("A6").Select
ActiveSheet.Range("A6:A" & LR).Select
ActiveSheet.Range("A6:A" & LR).Copy Activesheet.Range("B6:B" & LR)
Application.CutCopyMode = False

Now, when I run this code, it is not doing anything because I think the last row is merged and that is causing the issue. Is there anyway I can still copy keeping the bottom most row merged and I need to write a code without giving any specific number of rows as the number of rows will be different every time in each file that the code will work on.

 

[SQUIDD]

HI

So if im understanding correct, just -1 from LR

as below, also think you had some code in there you did not need?

Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count - 1
ActiveSheet.Range("A6:A" & LR).Copy Activesheet.Range("B6:B" & LR)
Application.CutCopyMode = False

 

[Zain_inout]

HI

So if im understanding correct, just -1 from LR

as below, also think you had some code in there you did not need?

Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count - 1
ActiveSheet.Range("A6:A" & LR).Copy Activesheet.Range("B6:B" & LR)
Application.CutCopyMode = False

Hi, I tried this code but somehow it doesnt work may be because my last row is merged?

 

[Zain_inout]

Hi, I tried this code but somehow it doesnt work may be because my last row is merged?

Hi, it worked when defined LR-2 instead of LR-1. Thanks

 

[SQUIDD]

Ok. Good.

strange why it needed -2, but if its working great. Just make sure your last cell is copying as expected, the one before the merged cell at the bottom

my preferred method fo Lastrow is below

lr = Range("A" & Rows.Count).End(xlUp).Row