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