Copy selection still not solved

Status
Not open for further replies.

keltic

New Member
Joined
Sep 3, 2002
Messages
20
I have a spreadsheet with data in columns A through to G.

The data starts on row 6 in column A, which is populated to the last line with a name. Columns B to G may or may not contain a value. The data always starts on row 6.

I am trying to copy formulae from a worksheet named control to I6:to the last line of data in column A (ie line 268 as in the following code).

Sheets("Control").Select
Range("I5:L5").Select
Selection.Copy
Sheets("Sheet4").Select
Range("I6:I268").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

The problem is the information in column A may vary on a monthly basis, so row I268 may be lower or higher. I assume I may be able to use some sort of offset command or lastrow command but can't quite get my head around which would be best.

Any help would be appreciated.

Try:

Range("I6").select
Range(selection, selection.end(xldown)).select

That copies down to row 65536. I am trying to find the last data entry in column A eg row 252 or row 235 or row 278 and copy the formula to this row.

As per my quote above, if there is data in column A from row 6 to 252 I need to copy a formula from I6:I252. Similarly, if the data changes next month in column A from row 6 to 235, I need to copy the formula from I6:I235.

Anyone else out there help ?

Thanks
Regards
Keltic
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Code:
Sub TEST()

Dim End_Row As Long

With Sheets("Control")
End_Row = .Range("A65536").End(xlUp).Row
.Range("I5:I" & End_Row).Copy Destination:=Sheets("Sheet4").Range("I6")
Application.CutCopyMode = False 
End With
End Sub

Note: Your explanation and the code you posted contradicted each other, so I may have misunderstood your request.

The above code will copy column I from row 5 to the end of the data. It then pastes it into I6 on Sheet 4.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Keltic

You really should continue in the original thread.:)

Anyways, try this.
Code:
LastRow = Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Row

Sheets("Control").Range("I5:L5").Copy Sheets("Sheet4").Range("I6:I"& LastRow)
 
Status
Not open for further replies.

Forum statistics

Threads
1,136,699
Messages
5,677,273
Members
419,683
Latest member
MrVBAConfused

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