VBA Code for dynamic range of data importing

[witsonjoyet]

Dear All,

Plz suggest me to solve my problem, i have a work sheet which i have to daily update with Turnover data. To update my main work sheet i download data from my soft ware as excel sheet and pick especially some columns data . Daily i copy these columns and paste it in my main work sheet. Now i need a macro to do this automatically.

Every day i have to upload only the following columns those are ..
A B C F G H I J K

and in rows i will take from second row only as the first row consists the heading those are not necessary in my main worksheet. and the last row consists Totals . this is also not necessary in my main work sheet.

After copy of these desired lines i have to paste it in my main work book (DATA.XLSM) in Sheet2 (BOTH sheet) here the problem is daily the data is increases so i have to paste it after the already filled rows. Kindly give me the VBA for this problem .

 

[SirJB7]

Hi, witsonjoyet!
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!

 

[witsonjoyet]

Sorry for inconvenience sir . Kindly fine the attachments

https://www.dropbox.com/s/0glm62p8er6poeo/Book1.xlsx

https://www.dropbox.com/s/kh7su7m9laixwgz/Data.xlsm

 

[droopy]

From file extension I guess your XL version >= 2007.
If yes, how about making your "both" data sheet a table (through insert > table).
The code below assumes your range selection macro works (I did not give it a try), and both source (Book1.xlsx) and destination (data.xlsm) workbooks are opened and have the worksheets we intend to use. It also assumes the table in "both" worksheets is named "Table1".
In any case, for safety, you should add some sanity check to prevent error events to be triggered.

Sub Moveit()

   Dim y As Worksheet, x As Worksheet, lr As Long, URng As Range
   Dim vDestLR As ListRow     ' This denotes a table object row

   Set x = Workbooks("Book1.xlsm").Sheets("Sheet1")
   Set y = Workbooks("DATA.xlsm").Sheets("Sheet2")

   lr = x.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
   x.Range(x.Cells(1, "A"), x.Cells(lr - 2, "C")).Offset(1).Name = "xi"
   x.Range(x.Cells(1, "F"), x.Cells(lr - 2, "K")).Offset(1).Name = "xj"

   Set URng = Union(x.Range("xi"), x.Range("xj"))

   URng.Copy 

   Set vDestLR = y.ListObjects("Table1").ListRows.Add         ' We add a row to the existing table - by default will be added at the end. If you want to insert it at a specific row number, see "Add" method details

   vDestLR.Range(1, 1).PasteSpecial Paste:=xlPasteValues   ' We paste the values (but you can choose to paste formulas as well) starting from the first cell of the newly created row.

End Sub

 

[droopy]

From file extension I guess your XL version >= 2007.
If yes, how about making your "both" data sheet a table (through insert > table).
The code below assumes your range selection macro works (I did not give it a try), and both source (Book1.xlsx) and destination (data.xlsm) workbooks are opened and have the worksheets we intend to use. It also assumes the table in "both" worksheets is named "Table1".
In any case, for safety, you should add some sanity check to prevent error events to be triggered.

Sub Moveit()

   Dim y As Worksheet, x As Worksheet, lr As Long, URng As Range
   Dim vDestLR As ListRow     ' This denotes a table object row

   Set x = Workbooks("Book1.xlsm").Sheets("Sheet1")
   Set y = Workbooks("DATA.xlsm").Sheets("Sheet2")

   lr = x.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
   x.Range(x.Cells(1, "A"), x.Cells(lr - 2, "C")).Offset(1).Name = "xi"
   x.Range(x.Cells(1, "F"), x.Cells(lr - 2, "K")).Offset(1).Name = "xj"

   Set URng = Union(x.Range("xi"), x.Range("xj"))

   URng.Copy

   ' We add a row to the existing table - by default will be added at the end.
   ' If you want to insert it at a specific row number, see "Add" method details
   Set vDestLR = y.ListObjects("Table1").ListRows.Add        

   ' We paste the values (but you can choose to paste formulas as well) 
   ' starting from the first cell of the newly created row.
   vDestLR.Range(1, 1).PasteSpecial Paste:=xlPasteValues  

End Sub

I noted in your destination worksheet, that you have some formulas in column L.

Please find below some changes to cope with that too.

First of all, I recommend that you delete the extra rows on worksheet "both".
We'll take care of them in the listrow object in vba code.
Although in this example columns numbers are hardcoded, I recommend to make them more flexible
and robust to changes (if for example you add a column before column L etc.)

Changed code:

Sub Moveit()

   Dim y As Worksheet, x As Worksheet, lr As Long, URng As Range
   
' all rows between and including the line above and below remain unchanged

   vDestLR.Range(1, 1).PasteSpecial Paste:=xlPasteValues   ' We paste the values (but you can choose to paste formulas as well) starting from the first cell of the newly created row.

  ' Populate formula in column L
 
  vDestLR.Range(1, 12).Formula = "=IFERROR(IF(" & _
  vDestLR.Range(1, 1).Address(RowAbsolute:=False) & _
  "<>"""",VLOOKUP(" & _
  vDestLR.Range(1, 1).Address(RowAbsolute:=False) & ",Backend!A$4:C$50,3,0),""""),"""")"

End Sub

 

[PeterB]

Hi Witsonjoyet,

You didn't mention how to deal with columns J:L on your Data sheet so they are left blank. The following process requires both workbooks to be open.

Sub Moveit()

  Dim wsData As Worksheet, wsBook1 As Worksheet
  Dim iLastRow As Long
  Dim vaData As Variant

  Set wsBook1 = Workbooks("Book1.xlsx").Sheets("Sheet1")
  Set wsData = Workbooks("DATA.xlsm").Sheets("Both")

  With wsBook1
    'Temporarily delete 2 columns so we can have a single range for the data
    Range(.Cells(1, 4), .Cells(1, 5)).EntireColumn.Delete
  
    'Find the last complteted data row in Column A
    iLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
  
    'We collect the data in a variant array to minimise call to the worksheet
    'and therefore speed up code execution
    vaData = Range(.Cells(2, 1), .Cells(iLastRow, 9)).Value
  
    'Close without saving (as we temporarily deleted 2 columns
    .Close False
  End With

  With wsData
    'Find the last complteted data row in Column A
    iLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
  
    'Write the Variant array to the Data workbook
    'This removes the need for the inefficient copy / paste
    .Cells(iLastRow, 1).Resize(UBound(vaData, 1), UBound(vaData, 2)).Value = vaData
  End With

'Clean up
Set wsBook1 = Nothing
Set wsData = Nothing

End Sub

Regards,

Peter

 

[witsonjoyet]

Thanks for your valuable suggestions .