VBA clear contents messed up name range and formula

EggMuffin

New Member
Joined
Aug 22, 2020
Messages
6
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: 6
  • code.jpg
    code.jpg
    173 KB · Views: 6

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,070
Office Version
  1. 2016
Platform
  1. Windows
On the sheet is already a table.
Clear the old table.
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

EggMuffin

New Member
Joined
Aug 22, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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: 2

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

EggMuffin

New Member
Joined
Aug 22, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
sorry for my late reply. Thank you. it works!
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're welcome & Thanks for feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,808
Messages
5,627,010
Members
416,214
Latest member
boston814

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
Top