I was able to get my macro to work with the code below and then I was asked to tweak a little and now I'm having problems again. Assistance on the tweaks to my code is greatly appreciated.
The tweaks I am to add:
1) Add a keyboard shortcut. However, when I do this, the macro stops running at the point when I open my other workbook. Want to use Ctrl + Shift +Q
2) Skip blanks rows in my iteration series as some are merged cells acting as header dividers to breakup the sheet. When I do this, it leaves the blank row still on my new worksheet (shtLoad).
3) Copy the newly created worksheet (shtLoad) as its own workbook (as well as leave a page in the active workbook) and title it using part of the name of the sheet (shtReq) it is pulling data from. I haven't tried this because my brain is fried after how long I've spent trying to get everything else to work.
The tweaks I am to add:
1) Add a keyboard shortcut. However, when I do this, the macro stops running at the point when I open my other workbook. Want to use Ctrl + Shift +Q
2) Skip blanks rows in my iteration series as some are merged cells acting as header dividers to breakup the sheet. When I do this, it leaves the blank row still on my new worksheet (shtLoad).
3) Copy the newly created worksheet (shtLoad) as its own workbook (as well as leave a page in the active workbook) and title it using part of the name of the sheet (shtReq) it is pulling data from. I haven't tried this because my brain is fried after how long I've spent trying to get everything else to work.
Code:
Sub QC()
Dim wbPPM As Workbook
Dim wbQC As Workbook
Dim shtTitle As Worksheet
Dim shtQC As Worksheet
Dim shtLoad As Variant
Dim shtReq As Variant
Dim ReqNo As String
Dim ReqName As String
Dim FinalRow As Long
Dim i As Integer, j As Integer
Set wbPPM = ThisWorkbook
Set shtTitle = wbPPM.Worksheets(1)
Set shtReq = ActiveSheet
FinalRow = Cells(Rows.Count, 3).End(xlUp).Row
Set wbQC = Workbooks.Open("[Location]")
Set shtQC = wbQC.Worksheets(1)
shtQC.Copy Before:=wbPPM.Sheets(10)
wbQC.Close
Set shtLoad = ActiveSheet
For i = 6 To FinalRow
ReqNo = shtReq.Cells(i, 1).Value
ReqName = shtReq.Cells(i, 3).Value
ActiveSheet.Cells(i, 3).Value = ReqNo & "--" & ReqName
Desc = shtReq.Cells(i, 4).Value
ActiveSheet.Cells(i, 4).Value = Desc
PPMNo = shtTitle.Range("G9").Value
ActiveSheet.Cells(i, 8).Value = PPMNo
Next i
End Sub
Last edited: