Hi,
I need help on excel tables. I encountered this issue but instead of using my real data, i created a dummy data for your easy understanding.
I need to frequently download data, insert columns, formula lookup multiple times a day. First of all, whenever i clear content, the columns get shifted and my macro button keeps shifting to the right. is there any way i can fix clear table that will not shift the columns?
2nd issue is with the code below having error.
thanks for helping to check my issue.
Sub test()
Sheets("basefile").Select
Range("A1:C7").Select
Selection.Copy
Sheets("Data").Select
Range("A1").Select
ActiveSheet.Paste
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Value = "Products"
Range("C2").FormulaR1C1 = "=VLOOKUP(RC[-1],vlookup!R1C1:R4C2,2,0)"
Range("C2:C2").AutoFill Destination:=Range("C2:C" & LastRow), Type:=xlFillDefault
Range("C2:C" & LastRow) = Range("C2:C" & LastRow).Value
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Value = "lookup"
Range("B2").FormulaR1C1 = "=VLOOKUP([@Series],vlookup!R1C4:R7C5,2,0)"
Range("B2:B2").AutoFill Destination:=Range("B2:B" & LastRow), Type:=xlFillDefault
Range("B2:B" & LastRow) = Range("B2:B" & LastRow).Value
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:E" & LastRow), , xlYes).Name _
= "table1"
Sheets("Calculation").Select
Range("C2").FormulaR1C1 = "=COUNTIFS(Items,""Chocolate"",Products,""C"")"
Sheets("Data").Select
Range("A1").Select
End Sub
Sub clear()
Application.ScreenUpdating = False
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End Sub
I need help on excel tables. I encountered this issue but instead of using my real data, i created a dummy data for your easy understanding.
I need to frequently download data, insert columns, formula lookup multiple times a day. First of all, whenever i clear content, the columns get shifted and my macro button keeps shifting to the right. is there any way i can fix clear table that will not shift the columns?
2nd issue is with the code below having error.
thanks for helping to check my issue.
Sub test()
Sheets("basefile").Select
Range("A1:C7").Select
Selection.Copy
Sheets("Data").Select
Range("A1").Select
ActiveSheet.Paste
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Value = "Products"
Range("C2").FormulaR1C1 = "=VLOOKUP(RC[-1],vlookup!R1C1:R4C2,2,0)"
Range("C2:C2").AutoFill Destination:=Range("C2:C" & LastRow), Type:=xlFillDefault
Range("C2:C" & LastRow) = Range("C2:C" & LastRow).Value
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Value = "lookup"
Range("B2").FormulaR1C1 = "=VLOOKUP([@Series],vlookup!R1C4:R7C5,2,0)"
Range("B2:B2").AutoFill Destination:=Range("B2:B" & LastRow), Type:=xlFillDefault
Range("B2:B" & LastRow) = Range("B2:B" & LastRow).Value
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:E" & LastRow), , xlYes).Name _
= "table1"
Sheets("Calculation").Select
Range("C2").FormulaR1C1 = "=COUNTIFS(Items,""Chocolate"",Products,""C"")"
Sheets("Data").Select
Range("A1").Select
End Sub
Sub clear()
Application.ScreenUpdating = False
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End Sub