PDA

View Full Version : How do i select a table range in VBA



keithaul
07-21-2016, 03:01 PM
Below is some code that is suppose to select certain worksheets in my workbook and then make a backup copy of the data on each of those sheets to a text file. Right now every is working. However, since my worksheets have navigation shapes on the left side of the sheet and each data range actually starts in cell F3, the ws.copy code actually copies the empty cells where the shapes are.

I don't want this to happen. So in each sheet I have a table and I've named those tables in each sheet. There is only one table on each sheet.

The end result I want is to copy just the table data, not the empty cells to the left of the data where the shapes are located.

I've tried using ListObjects but I can't figure out how to do it with the ListObjects collection

Since I have an array set up in the code with the actual sheet names, How do I reference the data range for each table on each sheet named in the array and then select that range and copy it using VBA?


Sub BackUpData()
Dim wbkCur As Workbook
Dim wbkNew As Workbook
Dim ws As Worksheet
Dim arrayone As Variant
Dim ThisFN As String

Set wbkCur = ActiveWorkbook
arrayone = Array("Sold Items", "Amazon")

'Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.
' Remember to set the ScreenUpdating property back to True when your macro ends.
Application.ScreenUpdating = False

'suppress prompts and alert messages while a macro is running; when a message requires a response, Microsoft Excel chooses the default response.
Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets(arrayone)


ws.Copy
Set wbkNew = ActiveWorkbook

'create a File name for the current workbook(the current path of the workbook) and path and append the current date and time of day to the file name
ThisFN = wbkCur.Path & "\" & ws.Name & "_" & Format(CStr(Now), "mm_dd_yyyy_hh_mm_AM/PM") & ".txt"

'save the new workbook with a file type of Text and dont create a backup file
wbkNew.SaveAs Filename:=ThisFN, FileFormat:=xlText, CreateBackup:=False

'close the new workbook and don't save the changes
wbkNew.Close SaveChanges:=False
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

p45cal
07-21-2016, 03:51 PM
Try:
Sub BackUpData()
Dim wbkCur As Workbook
Dim wbkNew As Workbook
Dim ws As Worksheet
Dim arrayone As Variant
Dim ThisFN As String

Set wbkCur = ActiveWorkbook
arrayone = Array("Sold Items", "Amazon")

'Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.
' Remember to set the ScreenUpdating property back to True when your macro ends.
Application.ScreenUpdating = False

'suppress prompts and alert messages while a macro is running; when a message requires a response, Microsoft Excel chooses the default response.
Application.DisplayAlerts = False

For Each ws In wbkCur.Worksheets(arrayone)
Set wbkNew = Workbooks.Add(xlWBATWorksheet)
ws.ListObjects(1).Range.Copy wbkNew.Sheets(1).Cells(1)

'create a File name for the current workbook(the current path of the workbook) and path and append the current date and time of day to the file name
ThisFN = wbkCur.Path & "\" & ws.Name & "_" & Format(CStr(Now), "mm_dd_yyyy_hh_mm_AM/PM") & ".txt"

'save the new workbook with a file type of Text and dont create a backup file
wbkNew.SaveAs Filename:=ThisFN, FileFormat:=xlText, CreateBackup:=False

'close the new workbook and don't save the changes
wbkNew.Close SaveChanges:=False
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
but by:
The end result I want is to copy just the table data,I've assumed that you want to keep the table headers. Easy to tweak if not.

keithaul
07-21-2016, 10:10 PM
Thanks for the solution this worked.

Can you help me understand something about the code you added?

The ws.ListObjects(1).Range.Copy:

Why don't I have to Dim a variable as Dim lstObj As ListObject and then do something like:

lstObj = ws.ListObjects

Does ws.ListObjects(1) refer to the 1st, and only table in the sheet?

How do I copy the data and not the header?

Thank You,
keith

p45cal
07-22-2016, 03:27 AM
Why don't I have to Dim a variable as Dim lstObj As ListObject and then do something like:
lstObj = ws.ListObjectsI don't know! You don't need to assign objects to variables to work on/with them (it does help when coding if the variable is used often and may be faster running) .





Does ws.ListObjects(1) refer to the 1st, and only table in the sheet?Yes.





How do I copy the data and not the header?change:
ws.ListObjects(1).Range.Copy wbkNew.Sheets(1).Cells(1)to:
ws.ListObjects(1).DataBodyRange.Copy wbkNew.Sheets(1).Cells(1)