JainAbraham
Board Regular
- Joined
- Feb 19, 2014
- Messages
- 92
Hi All,
I am trying to create a Macro for coping columns froms different sheets into one so for that i am assigning a variable to the worksheet where in i am getting an error of only one sheet the other sheets are assigned variables correctly.
i the below is my code i am not sure what i am missing because i have a worksheet named Rule Catalog yet it give me this error.
I get the same error for both line. Kinldly take a look and let me know what i am missing. i have checked the other posts but still cannot really decipher it.
I am trying to create a Macro for coping columns froms different sheets into one so for that i am assigning a variable to the worksheet where in i am getting an error of only one sheet the other sheets are assigned variables correctly.
i the below is my code i am not sure what i am missing because i have a worksheet named Rule Catalog yet it give me this error.
Code:
Option Explicit
Public Sub ResourceRelation()
Dim strptfile As String
Dim strASfile As String
Dim strASptfile As String
Dim strAssgin As String
Dim wrbMnsht As Workbook
Dim wrbASsht As Workbook
Dim shtMnsht As Worksheet
Dim shtASsht As Worksheet
Dim shtTemp As Worksheet
Dim rngAll As Range
Dim rngName As Range
Dim rngAssgin As Range
Application.StatusBar = "Opening the file to validate"
Sheet1.Activate
strptfile = ActiveSheet.Range("A2").Value
Set wrbMnsht = Workbooks.Open(Filename:=strptfile)
wrbMnsht.Activate
'Assigning variable to the Main sheet
Set shtMnsht = wrbMnsht.Worksheets(1)
Application.StatusBar = "Opening the common database sheet."
shtMnsht.Activate
Sheet1.Activate
'Selecting the Allstate sheet
strASptfile = ActiveSheet.Range("A4").Value
Set wrbASsht = Workbooks.Open(Filename:=strASptfile)
wrbASsht.Activate
'Assigning variable to the A doc
Set shtASsht = wrbASsht.Sheets("PRISM")
Application.StatusBar = "Opening the All Timesheet Submission Report"
shtASsht.Activate
'Create a range containing the column after thelast Column of the sheet
shtMnsht.Activate
Set rngAll = shtMnsht.UsedRange
rngAll.Select
With rngAll
Set rngName = .Offset(1, .Columns.Count).Resize(.Rows.Count - 1, 1)
End With
rngName.Select
strAssgin = "NT-ID"
Set rngAssgin = Utility.finder(shtMnsht, strAssgin)
ThisWorkbook.Activate
Dim shtContentPage As Worksheet
[COLOR=#ff0000]Set shtContentPage = ThisWorkbook.Sheets("ContentPage")
[/COLOR] [COLOR=#ff0000]Set shtTemp = ThisWorkbook.Sheets("Final")
[/COLOR] shtTemp.Cells.ClearContents
shtTemp.Cells.ClearFormats
End Sub