Automatically populating data when a certain column is filled

[anne.gomes]

Hi,

So I have this code so far.... I want rows FQ38840:FV38840 to be copy and pasted including formula and pasted when column C is filled with data to the length of column c's newly pasted data.

Please help




Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Range("FQ38840:FV38840").Copy
Range("FQ" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

End If
End Sub



Thank you in advance

[Bob Phillips]

Can you explain this bit, I don't understand it

... when column C is filled with data to the length of column c's newly pasted data.

[anne.gomes]

Hi xld,

Actually I want to these rows: "FQ38840:FV38840" to be filled down with their formulas until the last row of data in column C.

[anne.gomes]

Ive got this so far:



Application.ScreenUpdating = False
Application.Calculation = xlManual

Sheets("Invoice").Select
Selection.ClearFormats
Range("FQ38840:FV38840").Select
Selection.AutoFill Destination:=Range("FQ38840:FV38840" & Range("C" & Rows.Count).End(xlUp).Row)

[OG Loc]

So basically you want were formulae in FQ38840:FV38840 to be pasted into the rows for which where is something in row C?

If that is right, then depending on your situation, it might be easier to use a formula in the FQ:FV column range that is something like =IF(ISBLANK($C1),"",[InsertFormulaToUseHere])

[Bob Phillips]

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Sheets("Invoice")

lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
If lastrow > 38840 Then

.Range("FQ38840:FV38840").AutoFill .Range("FQ38840:FV38840").Resize(lastrow - 38840 + 1)
End If
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

[anne.gomes]

Thanks guys, I just ended up doing this:



LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Range("FQ38839").AutoFill Destination:=Range("FQ38839:FQ" & LastRow)
Range("FR38839").AutoFill Destination:=Range("FR38839:FR" & LastRow)
Range("FS38839").AutoFill Destination:=Range("FS38839:FS" & LastRow)
Range("FT38839").AutoFill Destination:=Range("FT38839:FT" & LastRow)
Range("FU38839").AutoFill Destination:=Range("FU38839:FU" & LastRow)
Range("FV38839").AutoFill Destination:=Range("FV38839:FV" & LastRow)

[anne.gomes]

I have a pretty challenging task to do now,

I can't really seem to find any info off on the net.

So I've got one excel workbook that has 4 different worksheets all with 1 different pivot graph each. These worksheets are called: "Sheet1", "Sheet2", "Sheet3", and "Sheet4". All these graphs have the same filter selections e.g. apples, bananas, pears, and oranges. So if in Sheet1 if I selected apples then I want apples to be selected in all the other 3 worksheets and have all four apple graphs to be copied and pasted automatically into a new worksheet.

Can this be done using vba? Can you guys please help me?

[Bob Phillips]

Add a slicer and connect it to all pivots. No VBA required.

[anne.gomes]

Hi xld,

Thanks for that, never knew that existed...amazing function! however when I went onto my graph, the 'Insert slicer' is disabled. I can't click onn it. Any ideas why and how to be able to use it?

Thanks a billion!

[anne.gomes]

I saved the files as a .xlsx and still no luck. I've read that it may have something to do with permissions but I don't know how to fix that?

[Bob Phillips]

You have to select the pivot to be able to insert a slicer anne, but that should also be the only place you see the option.

Any chance of posting the workbook?

[anne.gomes]

No sorry, the file is too large. The graphs gets the data from pivot tables. but I only want to connect the graphs and not the tables. I can insert slicer on the tables just not the graphs.

Is there another way I can achieve my outcome?

Thanks xld

[Bob Phillips]

Why not create pivot graphs? They share the same pivot cache, so the slicers control them too.

[anne.gomes]

Is there a way I can do it via VBA instead?

[Bob Phillips]

I am sure there is, but without seeing your model it is hard to be specific.

[anne.gomes]

Hi,

So I have this code...

What I want to do is after the procedure has run.... I want to open each excel document which is in the path below so "A.xlsx, B.xlsx, C.xlsx" and delete the extra sheets "Sheet1, Sheet2, Sheet3 and Sheet4" for each file and also select graph tabs "test1, test2, test3 and create one PDF for all three graphs. I'm guessing I need a for loop?




Sub copyOut(filename)
'
' copyout Macro
'
'
Workbooks.Add
ActiveWorkbook.SaveAs filename:="C:\Users\anneg\Documents\temp\" & filename & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

For Each graphTab In graphTabList
Windows("Basware Daily Report Data.xlsm").Activate
Sheets(graphTab).Copy After:=Workbooks(filename & ".xlsx").Sheets(1)
Next graphTab


ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub





Thanks for your help

[OG Loc]

The deleting of sheets is quite easy as i recall, you can probably just google search the line you need. The multiple charts in a PDF thing is a bit more tricky, but if it is the case as I am currently guessing that you have test1, test2 and test3 as chart sheets in your workbook you can use the following code to join all together in a single PDF.


ActiveWorkbook.Sheets(Array("test1", "test2", "test3")).Select
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="All Charts.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True