VBA clear contents messed up name range and formula

EggMuffin

New Member
Joined
Aug 22, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
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
 

Attachments

  • data.jpg
    data.jpg
    93.2 KB · Views: 13
  • code.jpg
    code.jpg
    173 KB · Views: 14

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
On the sheet is already a table.
Clear the old table.
 
Upvote 0
If you're importing data frequently then I'd definitely use PowerQuery - its far more powerful.

To stop your button moving, right click on it, select 'Format Control', on the 'Properties' tab select 'Don't move or size with cells'.

HTH
 
Upvote 0
Are you want to Second Table Paste on first Table or Add it after last row of previous table?
this code solve first problem:
VBA Code:
Sub test()
Dim Sh1 As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet, LastRow As Long
Dim xlSrcRange As Range
Set Sh3 = Sheets("basefile")
Set Sh1 = Sheets("Data")
Set Sh2 = Sheets("Calculation")
Sh3.Range("A1:C7").Copy Sh1.Range("A1")

LastRow = Sh1.Range("A" & Rows.Count).End(xlUp).Row

Sh1.Columns("C:C").Insert Shift:=xlToRight
Sh1.Range("C1").Value = "Products"
Sh1.Range("C2").FormulaR1C1 = "=VLOOKUP(RC[-1],vlookup!R1C1:R4C2,2,0)"
Sh1.Range("C2:C2").AutoFill Destination:=Range("C2:C" & LastRow), Type:=xlFillDefault
Sh1.Range("C2:C" & LastRow) = Sh1.Range("C2:C" & LastRow).Value

Sh1.Columns("B:B").Insert Shift:=xlToRight
Sh1.Range("B1").Value = "lookup"
Sh1.Range("B2").FormulaR1C1 = "=VLOOKUP([@Series],vlookup!R1C4:R7C5,2,0)"
Sh1.Range("B2:B2").AutoFill Destination:=Range("B2:B" & LastRow), Type:=xlFillDefault
Sh1.Range("B2:B" & LastRow) = Sh1.Range("B2:B" & LastRow).Value


ActiveSheet.ListObjects.Add(xlSrcRange, Sh1.Range("A1:E" & LastRow), , xlYes).Name _
= "table1"

Sh2.Range("C2").FormulaR1C1 = "=COUNTIFS(Items,""Chocolate"",Products,""C"")"

Sh1.Activate

End Sub

Sub clear()

Application.ScreenUpdating = False
Columns("A:B").Delete
Range("A1:C" & Range("C" & Rows.Count).End(xlUp)).ClearContents

End Sub
 
Upvote 0
Thanks for all your reply.
I have issues with name range keeps moving when the columns was cleared and reload. and new columns keep adding.
E.g my name range was setup in column C, D. But after data cleared and reload, the name range formula appears in column F, G. so my formula does not pickup correctly. Any idea?
 

Attachments

  • data.jpg
    data.jpg
    99.5 KB · Views: 10
Upvote 0
You forgot to answer my previous question?
Also Are you test my Previous Macro?
Also Test this:
VBA Code:
Sub test()
Dim Sh1 As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet, LastRow As Long
Dim xlSrcRange As Range
Set Sh3 = Sheets("basefile")
Set Sh1 = Sheets("Data")
Set Sh2 = Sheets("Calculation")
For i=1 to 3
Range(Sh3.Cells(1, i), Sh3.Cells(7, i)).Copy  Sh1.Cells(1, 2*i-1)
Next i
LastRow = Sh1.Range("A" & Rows.Count).End(xlUp).Row

Sh1.Range("D1").Value = "Products"
Sh1.Range("D2").FormulaR1C1 = "=VLOOKUP(RC[-1],vlookup!R1C1:R4C2,2,0)"
Sh1.Range("D2:D2").AutoFill Destination:=Range("D2:D" & LastRow), Type:=xlFillDefault
Sh1.Range("D2:D" & LastRow) = Sh1.Range("D2:D" & LastRow).Value

Sh1.Range("B1").Value = "lookup"
Sh1.Range("B2").FormulaR1C1 = "=VLOOKUP([@Series],vlookup!R1C4:R7C5,2,0)"
Sh1.Range("B2:B2").AutoFill Destination:=Range("B2:B" & LastRow), Type:=xlFillDefault
Sh1.Range("B2:B" & LastRow) = Sh1.Range("B2:B" & LastRow).Value


ActiveSheet.ListObjects.Add(xlSrcRange, Sh1.Range("A1:E" & LastRow), , xlYes).Name _
= "table1"

Sh2.Range("C2").FormulaR1C1 = "=COUNTIFS(Items,""Chocolate"",Products,""C"")"

Sh1.Activate

End Sub

Sub clear()

Application.ScreenUpdating = False
Columns("A:B").Delete
Range("A1:C" & Range("C" & Rows.Count).End(xlUp)).ClearContents

End Sub
 
Upvote 0
You're welcome & Thanks for feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top