Hi
I think the problem is here:
With ActiveCell
'>>>>>>>>>>> Failing to paste NEXT LINE <<<<<<<<<<<<
ActiveSheet.PasteEnd With
Can you try replacing it with
Regards
Maqbool
Hi
I think the problem is here:
With ActiveCell
'>>>>>>>>>>> Failing to paste NEXT LINE <<<<<<<<<<<<
ActiveSheet.PasteEnd With
Can you try replacing it with
Regards
Maqbool
Hi
Can you please post a sample workbook with expected results?
Hi Bob:
Please read the forum rules. You need to tag the code when you post.
I am not sure i understand you fully.
Try the following:
Dim strDate As String
'paste below code after "IF sheets("Mandatory").Cells("i,1)...................................... Then"
With Sheets("Mandatory")
strDate = Me.Textbox4.Value
If IsNumber(strDate) Then 'check the value is in textbox
.Cells(i, 5).Value = strDate
Else
MsgBox "Invalid Date."
End If
End With
Display More
otherwise post a sample workbook with expected result.
Maqbool
Please note that: this is to plot chart. Otherwise I can use a conditional formatting to hide.
Hi Everyone:
Hope Everyone doing well.
Can someone help me on how to hide the running total of a field on a Pivot Table. See the attached spreadsheet. I need to hide the highlighted numbers until the months data available on table. Any help will be appreciated.
Regards
Maqbool
Hi
Welcome to the forum:
Do you want store all changes (I mean, history of the changes) every changes on the column (what changed to what and the time of both changes) etc..
Can you please provide a sample of this?
Regards,
Maqbool
Thank you Ali.
Finally, I managed to do this. Thanks a lot for the help.
Regards,
Maqbool
Thank you for the reply.
No - I need a flexibility to choose historic files also. I found a way to do this with latest file . That does not meet my requirement
Maqbool
Hi
Can someone guide me how to dynamically change the source of a Power Query; My source files are in a folder. I am trying to change the source of the query by typing the file name in a cell. I found few videos which takes all files from the folder. But I need to use a single file as my data source. This source files are created on a weekly basis with a date suffix.
Thank you very much
Any help will be highly appreciated.
Hi
Try the code below:
Sub SumSheets()
Sheets("Summary").Range("A2:A1000").ClearContents ' clear everything in summary before pasting
Dim sh As Worksheet
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Summary" And sh.Name <> "Sheet1" And sh.Name <> "Sheet2" Then
sh.Range("F17:F66").Copy
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlValues
End If
Next
End Sub
Display More
Regards
Maqbool
Hi
Code below will solve both issues:
Please leave blank row 1 on consolidation workbook.
Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String
Dim lstRw As Long
Dim strLkpArra As String
Set wbSls = ThisWorkbook
strPath = "'C:\Test\[" ' change accordingly
strSht = "Master Data'!R7C2:R"
With wbSls
Set wsSls = .Sheets("Sales")
End With
n = 11
With wsSls
Do Until n = lstRw
lstRwInClsd = .Range("D4").Value + 6
x = .Range("D8").End(xlToRight).Column
lstRw = .Rows.Count
lstRw = .Range("D" & lstRw).End(xlUp).Offset(0, -1).Row
For x = 4 To x
strfile = .Cells(8, x)
strLkpArray = strPath & strfile & "]" & strSht
'levae row 1 empty on consolidated workbook - this helper row to get the last row in closed workbook
.Cells(1, x).Value = "=COUNTA('" & strPath & strfile & "]Master Data'!$B$8:$B$10000)"
lstRwInClsd = .Cells(1, x).Value + 6 '(6 rows blank on the monthly workbook - may need to adjust accordingly)
.Cells(n, x).Value = "=VLOOKUP(RC2," & "'" & strLkpArray & lstRwInClsd & "C11,10,FALSE)"
.Cells(1, x).Value = ""
Next
n = n + 1
Loop
End With
End Sub
Display More
Regards
Maqbool
Hi
Change the code as below:
Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String
Dim lstRw As Long
Dim rngFmula As Range
Set wbSls = ThisWorkbook
strpath = "'C:\Test\[" ' change accordingly
strSht = "Master Data'!$B$7:$K$33,10,FALSE)"
With wbSls
Set wsSls = .Sheets("Sales")
End With
n = 11
With wsSls
Do Until n = lstRw
x = .Range("D8").End(xlToRight).Column
lstRw = .Rows.Count
lstRw = .Range("D" & lstRw).End(xlUp).Offset(0, -1).Row
For x = 4 To x
strfile = .Cells(8, x)
strlkparray = strpath & strfile & "]" & strSht
.Cells(n, x).Value = "=VLOOKUP($B11," & strlkparray
Next
n = n + 1
Loop
End With
End Sub
Display More
Regards
Maqbool
Hi
Try the following. I have noticed an issues with your file extension (why you do have January 2010.xlsm.xlsx) . This needs to be corrected. Or change the file names on row 8.
Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String
Set wbSls = ThisWorkbook
strpath = "'C:\Test\[" ' change accordingly
strSht = "Master Data'!$B$7:$K$33,10,FALSE)"
With wbSls
Set wsSls = .Sheets("Sales")
End With
With wsSls
x = .Range("D8").End(xlToRight).Column
For x = 4 To x
strfile = .Cells(8, x)
strlkparray = strpath & strfile & "]" & strSht
Debug.Print strlkparray
.Cells(11, x).Value = "=VLOOKUP($B11," & strlkparray
Next
End With
End Sub
Display More
Regards
Maqbool
Hi
I can't upload the workbook now. I will upload the workbook later today.
Follow the below steps:
Create three ranged names as below:
Use below formula against each name
[F]
tblData: Sheet1!$C$1:INDEX(Sheet1!$C$1:$G$1000,COUNTA(Sheet1!$C:$C),5)
rngCat : =INDEX(tblData,0,2)
rngPercent =OFFSET(Sheet1!$C$1,MATCH(Sheet1!$J$1,rngCat,0)-1,4,COUNTIF(rngCat,Sheet1!$J$1))
[/F]
Then create the following formula on your sample sheet
L21 - enter as an array formula (CTRL+SHIFT+Enter]
[F]
N21 = LARGE(rngPercent,$K21)
L21 = OFFSET(rngPercent,MATCH(N21,rngPercent,0)-1,-4)
M21= VLOOKUP(L21,tblData,MATCH($M$20,$C$1:$G$1,0),FALSE)
[/F]
Regards
Maqbool
Hi
try the below :
Sub Test()
Dim wsOne As Worksheet
Dim rngOne As Range
Dim testOne As String
Dim testTwo As String
Set wsOne = ThisWorkbook.Sheets("Sheet1")
With wsOne
x = .Rows.Count
Set rngOne = .Range("F2", .Range("F" & x).End(xlUp))
End With
With rngOne
n = rngOne.Count
For x = n To 1 Step -1
testOne = .Cells(x, 1).Value
testTwo = .Cells(x, -1).Value
Debug.Print testOne
If testOne = "" And testTwo = "AFC" Then
.Cells(x, -1).Font.Bold = True 'this will highlight the cell which is going to be deleted
'.Cells(x, -1).EntireRow.Delete 'to delete entire row - enable this part, if everything works as expected
End If
Next
End With
End Sub
Display More
Regards
Maqbool
Hi
Sorry - I am not very clear about your requirement.
Do you mean - Copy 16 rows ( ie 20% of 80 data points) to each sheet (say mar 01 to March 05). So each sheet will have 16 rows of data.
Otherwise post expected result in each sheet (Mar 01, Mar 02 etc..) - Also, if you can input some meaningful data on the spreadsheet that will be more helpful to those who willing to help you.
Regards
Maqbool
YouTube solution.
The spread has lot of other info and the user needs to select respective fields and load the corresponding data.
Regards
Maqbool