I Need Advice with Automating Data Transfer Between Excel Sheets Using VBA

[matthew22194]

Hello there,

I am new to VBA and have been learning by tackling small automation tasks in Excel. Although; I have hit a bit of a roadblock with a project; and I am hoping someone here can offer guidance.

Contains multiple sheets, and each sheet has data in different ranges. Needs to receive specific data from the Source Workbook based on certain criteria.
The goal is to automate the transfer of data based on the following conditions.

Copy data from specific ranges in the Source Workbooks various sheets. Paste the data into corresponding sheets in the Destination Workbook.
Ensure that only rows with non-blank values in column A are copied. Ideally; clear the existing data in the Destination Workbook before pasting the new data.
I have managed to write a simple macro to copy and paste between two sheets.


Sub TransferData()
Dim srcWB As Workbook
Dim destWB As Workbook
Set srcWB = Workbooks("Source.xlsx")
Set destWB = Workbooks("Destination.xlsx")
' Example of copying data - needs improvement
srcWB.Sheets(1).Range("A2:D100").Copy
destWB.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
End Sub


Loop through multiple sheets in the Source Workbook. Copy data conditionally based on column A.

Clear the previous data in the Destination Workbook without affecting the structure.

Thanks in advance for your help and assistance.

[Aussiebear]

This is a little long winded but it will give you an idea on how to copy from one workbook to another. Simply remove those sections you don't want anymore.



Sub CopyDataToAnotherWorkbook()
Dim SourceWorkbook As Workbook
Dim DestinationWorkbook As Workbook
Dim SourceSheet As Worksheet
Dim DestinationSheet As Worksheet
Dim SourceRange As Range
Dim DestinationRange As Range
' Specify the paths to the source and destination workbooks
Dim SourceWorkbookPath As String = "C:\Path\To\Source\Workbook.xlsx"
Dim DestinationWorkbookPath As String = "C:\Path\To\Destination\Workbook.xlsx"
' Specify the sheet names in each workbook
Dim SourceSheetName As String = "SourceSheet"
Dim DestinationSheetName As String = "DestinationSheet"
' Specify the range to copy
Dim SourceRangeAddress As String = "A1:D10"
' Open the source and destination workbooks
Set SourceWorkbook = Workbooks.Open(SourceWorkbookPath)
Set DestinationWorkbook = Workbooks.Open(DestinationWorkbookPath)
' Set references to the source and destination sheets and ranges
Set SourceSheet = SourceWorkbook.Sheets(SourceSheetName)
Set DestinationSheet = DestinationWorkbook.Sheets(DestinationSheetName)
Set SourceRange = SourceSheet.Range(SourceRangeAddress)
' Determine the first empty row in the destination sheet
Dim LastRow As Long
LastRow = DestinationSheet.Cells(DestinationSheet.Rows.Count, 1).End(xlUp).Row + 1
' Set the destination range
Set DestinationRange = DestinationSheet.Range("A" & LastRow)
' Copy the data from the source range to the destination range
SourceRange.Copy DestinationRange
' Close the source workbook if necessary
SourceWorkbook.Close SaveChanges:=False
' Save the destination workbook
DestinationWorkbook.Save
' Close the destination workbook
DestinationWorkbook.Close SaveChanges:=True
MsgBox "Data copied successfully!"
End Sub

BTW: Please don't post spam here. You are lucky I didn't come across it, other wise you'd be watching from the sidelines.