Excel Question 2

[haeddy]

I'm trying to do total compensation report for our employees. I've created a database in Excel and I created the letter in excel. How can I merge the two files to create the letters? I have Excel 2013.

 

[haeddy]

Ok - got through that one. Now it says "Run time error 91: object variable or with block variable not set" on With wsdata.cells (1,1).current region.

 

[SkipVought]

Please post your code.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[haeddy]

Function RangeName(sName As String) As String
RangeName = Application.Substitute(sName, " ", "_")
End Function
Sub MergePrint()
Dim wsForm As Worksheet, wsData As Worksheet
Dim sRngName As String, r As Long, c As Integer
Set wsData = Workbooks("Total Compensation Summary_dteztz.xlsx").Worksheets("EE Upload")
With wsData.Cells(1, 1).CurrentRegion
For r = 2 To .Rows.Count
If Not wsData.Cells(r, 1).EntireRow.Hidden Then
For c = 2 To .Columns.Count
sRngName = wsData.Cells(1, c).Value
Range(RangeName(sRngName)).Value = wsData.Cells(r, c).Value
Next
wsForm.PrintOut
End If
Next
End With
End Sub

 

[SkipVought]

Where have you SET wsForm?

Com'on, please check your code before you run. Use the Debug Window to determine where the code ends. It did not error on the statement you stated.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[haeddy]

I swear the error was on that statement and I do Debug. I don't know what happened because it's on another one now and I didn't change anything. When I put the SET ws Form in front of the Set wsdata it says subscript out of range.

 

[SkipVought]

Subscript out of range is probably a spelling error in the workbook name or the worksheet name.

Do you have any trailing SPACE, for instance?

Look VERY carefully!

Due diligence is required by you.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[haeddy]

Thank you for all your patience and assistance. One last question - is it possible to have it sent to a file instead of the printer?

 

[Andrzejek]

Short answer - yes.
Longer answer - start your macro recorder, "sent to a file", stop recorder, look at the code generated.

BTW, don't you think Skip deserves a star for all his help?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

wsForm.Parent.Save

The parent object of the sheet is a workbook. You might want to .SaveAs to some other workbook name or as some other file format.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[haeddy]

I think the reason it wasn't working is because they were separate workbooks. When I figured out I had to add the data as a sheet to the form workbook it worked.

And yes - he deserves a big star!

 

[SkipVought]

Thx! Enjoyed the challenges.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}