Solved mergeing 2 workbooks ad keeping all info

July 9, 2012 at 07:16:26
Specs: Windows 7
How can I merge 2 workbooks and leave a1 alone on both workbooks - tehy are both same set up but one will have new information.

See More: mergeing 2 workbooks ad keeping all info

Report •


✔ Best Answer
July 9, 2012 at 12:10:17
While I'm not quite sure how a "customer's name" changes when you save the workbook to a different location, if you say it's happening, I have to believe you.

If each sheet contains something different in its own A1, then you'll need a line for each sheet, just like I posted in my example. It's one macro (stored in the ThisWorkbook module) with multiple instructions, one for each sheet.

Note how 3 different sheets are referenced.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Sheets(1).Range("A1") = "Whatever I want to put in Sheet1!A1"
 Sheets(2).Range("A1") = "Whatever I want to put in Sheet2!A1"
 Sheets(3).Range("A1") = "Whatever I want to put in Sheet3!A1"
End Sub

Another option is to loop thorugh a list of names stored in a worksheet, populating each A1 with a name from the list.

With a list of 3 names in Sheet1!B1:B3, this will populate A1 of Sheets 1 through 3 with those names:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  For shtNum = 1 to 3
     Sheets(shtNum).Range("A1") = Sheets(1).Range("B" & shtNum)
  Next 
End Sub

The list could also reside in the code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'List of names assigned to variables
    nxtName1 = "Bob"
    nxtName2 = "Steve"
    nxtName3 = "Mary"
'Loop to populate cells using the variables
  For shtNum = 1 to 3
      Sheets(ShtNum).Range("A1") = nxtName & shtNum
  Next 
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 9, 2012 at 09:29:05
It's not clear to me what you are trying to do.

Please provide a little more detail. The steps that you are following would help a lot.

Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 9, 2012 at 10:07:44
ok hopefully I can do this so you can understand. I have a workbook on my main pc then I transfer it to a flash drive stick so I can give it to my boss. Each time I try to update the flash stick I only have the option of replacing it. A1 is use as hyperlink to the invoice folder on Main pc I have it in c drive, on flash stick I have it there, but if I copy paste of course each time I lose links in flash drive.. So I want to know if there is a way to update flash stick -adding any new info I have added to main pc and not copy or override A1 on each page.

Report •

#3
July 9, 2012 at 10:42:44
You've used the words "transfer it", "update the flash stick" and "replacing it."

None of these tell me exactly what you are doing.

Are you doing a Save As to the flash stick? Are you doing a Windows Copy/Paste? A Drag and Drop?

How exactly are you updating the flash stick?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 9, 2012 at 10:54:55
eaither way I do it It only gives me the options copy and replace or copy and keep both files


Report •

#5
July 9, 2012 at 11:04:37
If you do a Save or Save As you can use the Before_Save event macro to force a specific entry into a specific cell, such as A1.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Sheets(1).Range("A1") = "Whatever I want to put in Sheet1!A1"
 Sheets(2).Range("A1") = "Whatever I want to put in Sheet2!A1"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
July 9, 2012 at 11:11:41
Will I have to do one of these for each worksheet just courious beacuse each one is a customers name

Sheets(1).Range("A1") = "Whatever I want to put in Sheet1!A1"


Report •

#7
July 9, 2012 at 12:10:17
✔ Best Answer
While I'm not quite sure how a "customer's name" changes when you save the workbook to a different location, if you say it's happening, I have to believe you.

If each sheet contains something different in its own A1, then you'll need a line for each sheet, just like I posted in my example. It's one macro (stored in the ThisWorkbook module) with multiple instructions, one for each sheet.

Note how 3 different sheets are referenced.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Sheets(1).Range("A1") = "Whatever I want to put in Sheet1!A1"
 Sheets(2).Range("A1") = "Whatever I want to put in Sheet2!A1"
 Sheets(3).Range("A1") = "Whatever I want to put in Sheet3!A1"
End Sub

Another option is to loop thorugh a list of names stored in a worksheet, populating each A1 with a name from the list.

With a list of 3 names in Sheet1!B1:B3, this will populate A1 of Sheets 1 through 3 with those names:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  For shtNum = 1 to 3
     Sheets(shtNum).Range("A1") = Sheets(1).Range("B" & shtNum)
  Next 
End Sub

The list could also reside in the code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'List of names assigned to variables
    nxtName1 = "Bob"
    nxtName2 = "Steve"
    nxtName3 = "Mary"
'Loop to populate cells using the variables
  For shtNum = 1 to 3
      Sheets(ShtNum).Range("A1") = nxtName & shtNum
  Next 
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
July 9, 2012 at 12:42:12
ty so much - I'll try it & see how it works

Report •

#9
July 10, 2012 at 04:10:02
fablious it works you don't konw how much time this is gonna save me ty so much - I used the second one. thank you again

Report •

#10
Report •

#11
August 11, 2012 at 05:37:27
Have a question for you DerbyDad03 I want to copywrite my set up but I want to make sure it is ok with you becaue you did help me which I am greatful let me know thank-you

Report •


Ask Question