Help with ActiveCell.Offset.Activate (1 Viewer)

[tdamme]

Here is what I am trying to do


Sub mroCopyShrinkageData()
On Error GoTo TheEnd

' Macro written on
'----------------
'N O T E S
'
'Must Start this macro on the source data workbook
'----------------

Application.ScreenUpdating = False
Dim currCell As String
Dim strData As String
Dim strDestination As String



temp = DatePart("m", Date) - 1
currMonth = MonthName(temp, 1) & Format(Date, "yy")
'currMonth = MonthName(temp, 1)
strData = currMonth & "MonthlyDATA.xls" '* * * * SOURCE DATA
strDestination = "ISA_" & currMonth & "_Network" & ".xls" '* * DESTINATION
'==========================================

Windows(strData).Activate 'DATA SHEET

Range(ActiveCell, ActiveCell.Offset(0, 13)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select

Windows(strDestination).Activate ' DESTINATION SHEET


Range("A60").Select

currCell = ActiveCell.Address

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("c60").Select
Selection.Copy


++++++++++++++


Here I want to activate the cell on the destination sheet that is equal to A60 (which is date) I want it to find the same date in column and then offset 1 (column B) in that row.

like
19-Jun

**********************************************************

ActiveCell.Offset(????????).Activate

'* * * Planned Hours

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
ActiveCell.HorizontalAlignment = xlCenter

'* * * Leave of Absence
Range("d60").Select
'ActiveCell.Value = ActiveCell.Value '/ 100
Selection.Copy


Hope that make sense.....

 

[tdamme]

Clarify

All I am trying to do ....
If I have a date in a column and I want it to look for the same date in same column to activate it....

 

[Sergeant]

Hi,
In your first post, it is very difficult to figure out what you are having a problem with.

I know you were trying to elaborate here...

All I am trying to do ....
If I have a date in a column and I want it to look for the same date in same column to activate it....

...but it didn't help much.

If you explain what you are really trying to do in a nutshell, perhaps we can streamline your code and get it to work at the same time.

BTW, When you manipulate data in XL, you don't need to .select and .activate everything you're working with.

 

[tdamme]

Ok let me see....
I have a database that I run by day..... so I may gather data by day like today I would run it for July 5, July 6, July 7 etc.....
So I run the data and it returns my query that I export to excel.

So then I copy the data elements I need and copy it to the customers spreadsheet..... then the process is to find the date on the spreadsheet and copy the data into that row.....

So then I run my data for July 5 and paste it in to a row that I am not using on the customers spreadsheet let's say ROW 60.... so my first piece of data in Row 60 column A is the date 5- Jul
Now where I am stuck ...... I want it to Activate the row that has 5- Jul -- and it is Row 20 in column A.....

that is all I am trying to do.....

I know how to do the Activate.offset.active but I want the offset to find the date......not offset....

Hope that explains it alittle better.....

Thank you

 

[Sergeant]

Sorry for the delay...
Here is the basic usage of the .find method of a range object:

Dim rng As Range
With Sheet1.Range("a2:a34")
     Set rng = .Find(Sheet1.Range("f1"))
End With
rng.Select
Set rng = Nothing

Maybe that will help.

I'm still not clear on your overall process, so I just focused on what you are saying that you need.

If the data remains in the db, couldn't you just produce the whole page each time?

 

[tdamme]

I have attached a sample of what I am trying to do.....

 

[Sergeant]

So you have this...(If the picture works)

You have just pasted data onto row 60 and you want to activate the row or cell that coincides with the date that you pasted?

Are you going to leave the data on row 60? If not, then why put it there in the first place?

You could use the .find method to find the target cell in the first place.

In any case, to complete your method...
Do as advised:
dim rng as Range
With ActiveSheet.Range("a4:a59")
Set rng = .Find(ActiveSheet.Range("A60"))
End With
rng.Offset(0, 2) = Range("C60")
rng.Offset(0, 3) = Range("D60")
Set rng = Nothing