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