PDA

View Full Version : Automatically populating data when a certain column is filled



anne.gomes
05-27-2014, 04:21 PM
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

xld
05-27-2014, 04:25 PM
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
05-27-2014, 04:40 PM
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
05-27-2014, 04:49 PM
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
05-28-2014, 01:48 AM
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])

xld
05-28-2014, 04:45 AM
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
05-28-2014, 01:24 PM
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
05-28-2014, 04:03 PM
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?

xld
05-28-2014, 11:42 PM
Add a slicer and connect it to all pivots. No VBA required.

anne.gomes
05-29-2014, 02:17 PM
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
05-29-2014, 02:37 PM
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?

xld
05-29-2014, 04:03 PM
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
05-29-2014, 04:30 PM
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

xld
05-29-2014, 04:36 PM
Why not create pivot graphs? They share the same pivot cache, so the slicers control them too.

anne.gomes
05-29-2014, 04:50 PM
Is there a way I can do it via VBA instead?

xld
05-30-2014, 12:36 AM
I am sure there is, but without seeing your model it is hard to be specific.

anne.gomes
06-02-2014, 03:13 PM
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
06-04-2014, 06:28 AM
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