Reconfiguring variable assignments


New Member
Apr 15, 2013

6 months ago someone here helped me build a macro to reformat a report. I own a property management company and I use this report to list my current vacancies. The way it is generated through my management software is very difficult to read and is poorly designed. After installing an update to our management software it changed the look and cell placement of the report and therefore my macro will not work
. I have tried changing the cell assignments for the variables but some of the coding was a little over my head. I have included the code for the macro here. Any help or advice would be greatly appreciated! Thank you!


P.S. I've done some playing around with it. The new report has cells that are merged, how do I copy the text in those cells into my output sheet? It says #Value the way Ive tried!

Sub aaa()
Dim OutSH As Worksheet
cntr = 0
Set OutSH = Sheets.Add(after:=Sheets(1))
ActiveSheet.Name = "Output"
Range("G1").Formula = "=today()"


For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Left(Cells(i, 2), 5) = "Unit:" Then unit = Right(Cells(i, 4), Len(Cells(i, 1)) - 6)
If Left(Cells(i, 1), 10) = "Unit Type:" Then unittype = Right(Cells(i, 1), Len(Cells(i, 1)) - 11)
If Left(Cells(i, 1), 12) = "Market Rent:" Then mktrent = Right(Cells(i, 1), Len(Cells(i, 1)) - 13)
If Left(Cells(i, 1), 12) = "Unit Status:" Then unitstatus = Right(Cells(i, 1), Len(Cells(i, 1)) - 13)
If Left(Cells(i, 1), 11) = "Date Ready:" Then dateready = Right(Cells(i, 1), Len(Cells(i, 1)) - 12)
If Cells(i, 1) = "Lease Term" Then
outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row
OutSH.Cells(outrow, 1).Value = "Unit Type:"
OutSH.Cells(outrow, 1).Font.Bold = True
OutSH.Cells(outrow, 2).Value = unittype
OutSH.Cells(outrow, 2).Font.Bold = True
OutSH.Cells(outrow + 1, 1).Value = unit
OutSH.Cells(outrow + 1, 3).Value = "Market Rent:"
OutSH.Cells(outrow + 1, 3).Font.Underline = True
OutSH.Cells(outrow + 1, 4).Value = mktrent
OutSH.Cells(outrow + 1, 5).Value = "Unit Status:"
OutSH.Cells(outrow + 1, 5).Font.Underline = True
OutSH.Cells(outrow + 1, 6).Value = unitstatus
OutSH.Cells(outrow + 1, 7).Value = "Date Ready:"
OutSH.Cells(outrow + 1, 7).Font.Underline = True
OutSH.Cells(outrow + 1, 8).Value = dateready
Cells(i, 1).Resize(15, 5).Copy Destination:=OutSH.Cells(outrow + 2, 1)
cntr = cntr + 1
If cntr = 2 Then
OutSH.HPageBreaks.Add Before:=OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
cntr = 0
End If

With OutSH.Cells(outrow + 1, 4).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With OutSH.Cells(outrow + 1, 6).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With OutSH.Cells(outrow + 1, 8).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End If

Next i
OutSH.Columns("F:F").ColumnWidth = 14.4
OutSH.Columns("A:A").ColumnWidth = 14.25
OutSH.Rows("1:10000").RowHeight = 13.75
OutSH.PageSetup.Orientation = xlLandscape
OutSH.Columns("A:H").HorizontalAlignment = xlCenter
OutSH.Range("A1").HorizontalAlignment = xlLeft

End Sub

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Welcome to the MrExcel forum.

When posting VBA code, please use Code Tags - like this:


'Paste your code here.


What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Even better for your request:

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.
Upvote 0

Thanks for the workbook.

I do not see the correlation from Raw Data to worksheet Visual Representation?

I can not find worksheet Visual Representation Unit Type: 12705-20 information on worksheet Raw Data??

I would think that you should use the information from Raw Data, and, manually complete Visual Representation in order for someone to understand what information is going where.

And, worksheet Instructions contains pictures/graphics that are difficult to see and understand.

Please post another workbook.
Upvote 0

My apologies, I've sent you the wrong version. I have included the link to the correct version. The 'instructions' tab was for the old VBA module which should still be in there. This was merely for my employees as a reference on how to operate the workbook. Refer to that module though for a benchmark. It will have to be set in a loop because the report is always a different length based on how many apartments are vacant at a given time. To give you a background on my problem, the 'raw data' report prints datasets half on one pg half on the other making it difficult to read and contains information that is quite frankly unneccessary for our day-to-day reference and creates confusion. That being said, if you could help me with the loop and getting the data to copy in the way I have demonstrated, I can take care of the bold font, auto-fit columns, underlining, etc.

One other there a function I can apply to the 'raw data' to require the user to only be able to paste 'values and number formatting' only? This was the only way I could keep merged cells out so that I could properly reference the values I needed.

Thank you VERY much for your help & patience.



Upvote 0

Forum statistics

Latest member

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
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 "".
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