How to copy row in one workbook to master workbook.

[adriel19pr]

Question: I have a workbook called "Datasheet1.xlsx" and would like to have the total row which is "B209-O209" of that sheet auto fill the Master workbook row by row starting with "B4" I would need to reuse the datasheet over and over on a daily basis. I would like to keep the records on the next available row in the master workbook without erasing the previous data.

Is there a way of updating the master workbook using a macro?

 

[jonhaus]

If I understand you correctly you want to copy Range B209:O209 and transpose this in the master beginning at range A3. Give this code a try.

Sub ImportandTranspose()
Workbooks("DataSheet1.xlsx").Range("B209:O209").Copy
If Len(ThisWorkbook.Range("A3")) = 0 Then
    ThisWorkbook.Range("A3").PasteSpecial Paste:=xlPasteValues, Transpose:=True
Else
    ThisWorkbook.Range("A" & Rows.Count).End(xlUp).PasteSpecial Paste:=xlPasteValues, Transpose:=True
End If
End Sub

-jonhaus
For More Help with Excel and VBA visit my Website

 

[adriel19pr]

Thanks Jonhaus,

For some reason I get a Compile error: "Method or data member not found." Here "If Len(ThisWorkbook.Range[/B][/B][/B]("A3")) = 0 Then" were the .Range gets highlighted.

Basically what I have is a Datasheet that I need to fill for each individual cashier. I would like to have a VBA so that when I click on the button the information in the "Datasheet1.xlsx" range B209:O209 go and start filling the "MASTER1.xlsx" range "A3:N3". Then for the next cashier it will fill the next line "A4:N4" and so on to fill the Master on a daily basis. Dont really know anything about VBA was just told that it was the only way to make it happen. will greatly appreciate all the help willing to give. Thanks

 

[jonhaus]

I forgot to specify the sheet name in your master workbook. You will have to change "Sheet1" to your sheet name. Also I think I misinterpreted you and you don't want to transpose the data, so i modified that as well.

Sub ImportandTranspose()
Workbooks("DataSheet1.xlsx").Range("B209:O209").Copy
If Len(ThisWorkbook.Sheets("Sheet1").Range("A3")) = 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A3").PasteSpecial Paste:=xlPasteValues
Else
ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).PasteSpecial Paste:=xlPasteValues
End If
End Sub

-jonhaus
If you are interested in learning visual basic visit my website

 

[adriel19pr]

Sorry again.

For some reason I get an Run-time error '438' "Object doesn't support this property or method. Please excuse the question is there anyway I may send you the workbooks so that you may see the problem?

Thank You very much.