last row question

[CTKC]

Hey all,

I am having trouble with a small piece of code right now. Basically I want the last row, go 2 rows up and delete the the whats below. There are three total rows at the end of the worksheet I want to get rid of. The code I have is below:

Dim lROW As Integer

Range("A5").Select
lROW = Selection.End(xlDown).Offset(-2, 0).Row
MsgBox lROW

[b]Rows("lrow:lrow+4").Delete shift:=xlUp[/b]

I get a mismatch error at the Rows line of code. I thought sense it lrow is an integer I could just use that as a designation of the row I wanted to delete...


Thanks for the help.

 

[combo]

You need object:
Set lROW = Selection.End(xlDown).Offset(-2, 0).Row


combo

 

[SkipVought]

Hi,

SET is for an OBJECT, not a value like row number.

Dim lROW As Integer

lROW = Range("A5").End(xlDown).Row
MsgBox lROW

Range(cells(LROW-2, 1), cells(LROW, 1)).entirerow.Delete shift:=xlUp

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

You are right Skip. To correct original code:

Rows(lRow & ":" & lRow + 4).Delete shift:=xlUp

combo

 

[CTKC]

Thank you! I don't know why I didn't think of that.

I am running into another obstacle thought. I will try to explain as thorough as possible.

I have two workbooks with multiple sheets (around 80-100 per WB). Each worksheet contains previous months explanation (colunmns A:C) and current month explanation (columns E:G). At my firm we have a monthly process that we have to discuss issues with certain accounts that trip set thresholds. So we take the previous months file and the newly created current month and copy the explaination for current in the each corresponding worksheet that is named the Account.

I created a macro already to take an output from our financial tool and create the current month template with the financial data. Now I want to take this output, the previous months out, and copy what was the last months explanation to the left side and include the current month financials/explanation on the right. So basically what I want to do is loop through the current month output and find the corresponding tab that has the account name on it and copy it in there and if the account was not in the previous month output, create a new tab with it...

The code i have right now is slim and I have been trying various options to no avail..

Dim wbEV, wbVAR As Workbook
Dim cSheets As Integer
dim sSheets as string

' Set to wbEV
   newpath = Application.GetOpenFilename("Excel, *xls")
   
   Set wbEV = Workbooks.Open(newpath)
   
   cSheets = wb.Sheet.Count <----I also get an Error here.
   MsgBox cSheet
   


   newpath2 = Application.GetOpenFilename("Excel, *xls")
   
   'Redim ssheets(1 to cSheets)

   'For i = 1 To sSheet
   ' wbev.sheet(i).name=wbVAR.Sheets(....
   ' Set to wbVAR
   ' Set to wbVAR

Set wbVAR = Workbooks.Open(newpath2)

For Each Worksheet In ActiveWorkbook
    If Sheet.Name = wbVAR.Sheet.Name Then
    MsgBox "YAY"
    End If
Next

 

[Bong]

cSheets = wb.Sheet[red]s[/red].Count

You've also got a lot of "xSheets" becoming "xsheet". Are these just typos?

For Each [red]wsWS2[/red] In ActiveWorkbook[red].sheets[/red]
If [red]wsWS2[/red].Name = wbVAR.Sheet.Name Then

That is, Worksheet is an object, Sheet is not. On the other hand Sheet[reds[/red] is a collection and you can get at the worksheets by indexing: sheets.

wbVAR.Sheet.Name doesn't make any sense. wbVar is a workbook. Which worksheet of that workbook are you interrogating to find it's name? I think maybe you want a 2nd loop over all the sheets of wbVAR: for each wsWS3 in wbVAR.sheets

You should avoid using keywords (like "Worksheet") as variable names.

_________________
Bob Rashkin

 

[SkipVought]

Also, you assign Workbook Objects to wbEB and wbVAR, yet you reference the ActiveWorkbook rather than your objects. at that point in the code, it appears that the ActiveWorkbook is wbVAR. ???



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

Bong,

The code I posted previously was a mess because I was trying various things to get on the right path but to no avail. Also, what did wsWS2 stand for? Worksheet?

Basically I want to take wbEV and loop through all the sheets. Each tab is named the Account, say XXX1 - thats why I am looking for the sheets name. In addition, I want to loop through wbVAR (tabs are named the Account too) to find the corresponding tab/name and paste the new financial data for the month in their and if a new account opened that we have to report on, create a new tab with that account and financial information. So I agree with you, I probably do need a second loop of some sort. I believe the For/Next inital loop is the one I want because it goes through each and every sheet in the current month data -> maybe I am wrong here though.


Skip,

I initially had wbEV instead of Activeworkbook but I was getting errors elsewhere and started to try to manipulate anything to get on the right path but after over an hour of brainstorming and no progress, I came here for help. It seems I do not fully understand how to set up the looping structure between the two workbooks correctly.

If you could guide me in the right direction, it would be apppreciated.

 

[SkipVought]

"Each tab is named the Account, say XXX1 ..."

This is a fundamental design flaw. By placing each account in a separate sheet, you have multiplied the effort to manipulate, analyze & report your valuable data, defeating the plethora of features available in Excel for manipulating, analyzing & reporting.

Your efforts would be better served by consolidating your data into a single sheet, adding a column for Account. From a properly designed table in Excel, you can then EASILY, in mere SECONDS, report & summarize an Account for any time period, like Week, Fortnight, Month & Year, Quarter & Year -- SECONDS!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

I understand it makes it more complex and complicated. But this is how we have to report our accounts. It's mandated by the Government and our Customer that we report to.
We have to report on serveral criteria thats why we have multiple blocks of explanations.

The current month output (that I have already created a macro for creates the Current month data/template. So i was hoping to loop through and copy April where March is and copy the current month data where April is.

Basically each tab in the previous month's file looks like this:
Account Name
Descrip


March April
Financials

Explanation Explanation
Explanation ""
Explanation
Explanation

 

[SkipVought]

And as I stated, it is orders of mangitude easier to go from ONE TABLE to an Account report, than what you are currently struggling with. Excel functionality is set up that way.

Unfortunately, the ease of using Excel also make it easy to make these kinds of design errors.

But on your previous question...

dim wsEV as worksheet, wsVAR as worksheet
for each wsEV in wbEV.worksheets
  for each wsVAR in wbVAR.worksheets
     if wsEV.name = wsVAR.name then
      'houston, we have a worksheet MATCH!!!

     end if
  next
next

or even this...

dim wsEV as worksheet, wsVAR as worksheet
for each wsEV in wbEV.worksheets
  with wbVAR.sheets(wsEV.name)
    'this is the corresponding worksheet in the other workbook

  end with
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

Skip,

Thank you for your posts, it is much appreciated. You have helped me throughout each thread I have started.

I have started to teach myself VBA for the past two or so months, how long did it take you to become proficient at it?



Thanks,

Tom

 

[SkipVought]

I had two fortunate helpful quantum pushes.

In 1994, I discovered a guy at our helpdesk, that knew the Excel Object Model and VBA. I picked his brain and got lots of help.

In 2001, I discovered Tek-Tips, and I continue to learn from my colleagues here in these forums.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}