The code below pulls data from multiple sheets and pastes it onto a single sheet called the Master sheet.
Snippet of sheet 1 copied to the master sheet. (Note that the copied sheet starts at the line Multiplier.)
Snippet of sheet 2 on the Master.
The images above are snippets of the output based on the code above. I want to navigate to the 4th column from the left and multiply it with the multiplier (wksht.range("F9")). Is there anyway to do that?
One thing to understand is that a range like this is created for every sheet one below another with an empty space between each ( Firstblank row in code), I need to multiply the multiplier only for that particular range (wksht.range("F9")), as the multiplier changes for every sheet. That's why I cant just select the whole column and multiply it with the multiplier.
I am trying this as shown below:
QtyMultiplier = wksht.Range("F9").value ' is located on every sheet, it is always a number
I Don't know how to traverse this new range.
Any help I would appreciate it.
PS: I know that Activesheet may make Excel crash but for now Im using it, I will change that statement in the end. Thanks!
Apologies for berating you with questions. But this the last one!
Thanks a lot!!
VBA Code:
On Error GoTo Erfix
Application.ScreenUpdating = False
Sheets("Master").Range("A13", "N10000").ClearContents
For Each wksht In Worksheets
If IsInArray(wksht.name, DefaultSheetNames) = False Then
LR = wksht.Cells(Rows.Count, 1).End(xlUp).Row
Set rng1 = wksht.Range("A11:J" & LR)
Set rng2 = wksht.Range("E9:G9")
'For i = 11 To LR
'Debug.Print LR
FirstBlankrow = Sheets("Master").Cells(Sheets("Master").Rows.Count, 1).End(xlUp).Row + 2 '
Sheets("Master").Cells(FirstBlankrow, 1).Resize(rng2.Rows.Count, rng2.Columns.Count).Cells.value = rng2.Cells.value
FirstBlankrow = Sheets("Master").Cells(Sheets("Master").Rows.Count, 1).End(xlUp).Row + 1 '
Sheets("Master").Cells(FirstBlankrow, 1).Resize(rng1.Rows.Count, rng1.Columns.Count).Cells.value = rng1.Cells.value
'Next i
End IfSnippet of sheet 1 copied to the master sheet. (Note that the copied sheet starts at the line Multiplier.)
Snippet of sheet 2 on the Master.
The images above are snippets of the output based on the code above. I want to navigate to the 4th column from the left and multiply it with the multiplier (wksht.range("F9")). Is there anyway to do that?
One thing to understand is that a range like this is created for every sheet one below another with an empty space between each ( Firstblank row in code), I need to multiply the multiplier only for that particular range (wksht.range("F9")), as the multiplier changes for every sheet. That's why I cant just select the whole column and multiply it with the multiplier.
I am trying this as shown below:
QtyMultiplier = wksht.Range("F9").value ' is located on every sheet, it is always a number
I Don't know how to traverse this new range.
Any help I would appreciate it.
PS: I know that Activesheet may make Excel crash but for now Im using it, I will change that statement in the end. Thanks!
Apologies for berating you with questions. But this the last one!
Thanks a lot!!
